Chris Guiblin's Multimedia Blog: 13/07/2008

Friday, July 18, 2008

PHP (4) - Sorting By Date

PHP & MYSQL TUTORIAL/PROGRESS BLOG

Comments/Guest Book

PART 4 "Sorting By Date"

The next thing I wanted to do was to be able to sort the information backwards, so the latest items of data appear at the top. Although I realized this would be possible to do by simply doing a descending sorting of the ID, I thought it would be more useful to sort descending by date as this is another piece of information that would be useful to provide on a comments page/guest book, etc.

Adding Date field into database table

This is done by going back into the phpMyAdmin front end and clicking the structure tab. Underneath the table is a bit that says Add (number) field(s) At End of Table/At Beginning of Table/After (field name). Since it is more organized to keep the ID as the first field, choose After, and the field name you want it to be after (ID in this case). Name the field 'Date' and select type as 'Date' too. Save this then go back to the php file.

Writing the date into the database

All you need to do in the php is add 'Date' into the list of fields you are adding to and 'CURDATE()' as its associated value. The line should look like this:

$query = "INSERT INTO example1 (Date, Name, Subject, Comment, URL) VALUES (CURDATE(), '$Name', '$Subject', '$Comment', '$URL')";

Save the file and open it from within Firefox. Press submit and then if no errors appear, go back into phpMyAdmin and click the Browse tab, where you should see the date stored in the most recent Id's date field (click ID to make the ID list descending so the newest is at the top).

Information/help from:

page resource
electric toolbox
Tiz Taz

©Chris Guiblin 2008
URL: www.guiblin.com/chris/
EMAIL: chris@guiblin.com

Creating and Retrieving MySQL Data (2)

PHP & MYSQL TUTORIAL/PROGRESS BLOG

Comments/Guest Book

PAGE 4 "Creating and Retrieving MySQL Data (2)"

Creating HTML form

Below is the html I used for creating the table which is used to enter data to be added to the form.

<table width="200" border="0" cellspacing="0" cellpadding="1" align = "center">

<tr>
<td width="100">Username:</td>
<td><input name="username" type="text" id="username"></td>
</tr>

<tr>
<td width="100">Subject:</td>
<td border = "0"><input name="subject" type="text" id="subject"></td>
</tr>

<tr>
<td width="100">Comment:</td>
<td><input name="comment" type="text" id="comment"></td>
</tr>

<tr>
<td width="100">URL:</td>
<td><input name="url" type="text" id="url"></td>
</tr>

<tr>
<td width="100"> </td>
<td align = "right"><input name="add" type="submit" id="add" value="Submit"></td>
</tr>

</table>

The 'id' is what the php code looks at and gets the data from. The finished php file for the file looks like this:

<html>
<head>
<title>Add New Comment</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
if(isset($_POST['add']))
{

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'test1';
mysql_select_db($dbname);

$Name = $_POST['username'];
$Subject = $_POST['subject'];
$Comment = $_POST['comment'];
$URL = $_POST['url'];

$query = "INSERT INTO example1 (Name, Subject, Comment, URL) VALUES ('$Name', '$Subject', '$Comment', '$URL')";
mysql_query($query) or die('Error, insert query failed');

$query = "FLUSH PRIVILEGES";
mysql_query($query) or die('Error, insert query failed');

echo "Comment Added <br><br>";

$query = "SELECT Name, Subject, Comment, URL FROM example1";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "----------------------------------- <br>" .
"Name: {$row['Name']} <br>" .
"Subject: {$row['Subject']} <br>" .
"Comment: {$row['Comment']} <br>" .
"URL: {$row['URL']} <br>";
}
mysql_close($conn);

}
else
{
?>
<form method="post">

<font size="1" face="Arial">

<table width="200" border="0" cellspacing="0" cellpadding="1" align = "center">

<tr>
<td width="100">Username:</td>
<td><input name="username" type="text" id="username"></td>
</tr>

<tr>
<td width="100">Subject:</td>
<td border = "0"><input name="subject" type="text" id="subject"></td>
</tr>

<tr>
<td width="100">Comment:</td>
<td><input name="comment" type="text" id="comment"></td>
</tr>

<tr>
<td width="100">URL:</td>
<td><input name="url" type="text" id="url"></td>
</tr>

<tr>
<td width="100"> </td>
<td align = "right"><input name="add" type="submit" id="add" value="Submit"></td>
</tr>

</table>
</font>

</form>
<?php
}
?>
</body>
</html>

Note that the entire php section is placed within the html tags. However, the file must still be saved as a php file for it to work. Ensure that the id's in the html section towards the end are the same as the values for the defined variables such as $Name, $Subject etc. I have emphasized these in bold.

The easiest way to understand all this is to look at it a few times and then try making some changes. Try changing the look of the form as well as how the information is displayed once it is submitted.

The images below show how it should look (yes I spelled my own surname wrong)

Thursday, July 17, 2008

PHP (3) - Creating and Retrieving MySQL Data

PHP & MYSQL TUTORIAL/PROGRESS BLOG

Comments/Guest Book

PAGE 3 "Creating and Retrieving MySQL Data"

Adding data to the database

To add the data you must use a query. Add the following code to your php file, after "mysql_select_db($dbname);" and before "?>"

$query = "INSERT INTO example1 (Name, Subject, Comment, URL) VALUES ('Bob', 'Hey', 'Once upon a time, the end', 'http://chrisgibbo.blogspot.com')";
mysql_query($query) or die('Error, insert query failed');

This adds the values to their relative fields, so ordering the fields and values correctly is essential. Do not add the ID as one of the fields, as the ID field is handled automatically.

Save the file (as a .php file) and open it in your browser. There will still be nothing there (if there is an error go back through and make sure you didn't miss anything. Now go into the phpMyAdmin front end. Go into the table under the database you created and choose browse. You should see the information there. Try refreshing the php file you created several times to ensure that it keeps adding the data on to the end.

Retrieving data from the database
The following will only display data which already exists in the database. It will not add it and then display it, so ensure you have followed the above section first before trying this bit.

The final code to both enter the data and retrieve it:

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error

connecting to mysql');

$dbname = 'test1';
mysql_select_db($dbname);

$query = "INSERT INTO example1 (Name, Subject, Comment, URL) VALUES ('a', 'b', 'c', 'd')";

mysql_query($query) or die('Error, insert query failed');

$query = "SELECT Name, Subject, Comment, URL FROM example1";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo " Name :{$row['Name']} <br>" .
"Subject : {$row['Subject']} <br>" .
"Comment : {$row['Comment']} <br>" .
"URL: {$row['URL']} <br>.<br>";
}

?>

Save and refresh to show the same page with the added content (a, b, c and d in my example). Keep refreshing and the page should add the same data to the bottom of the page over and over again.

Information/help from:

Collin Jensen
php.net
php mysql tutorial

©Chris Guiblin 2008
URL: www.guiblin.com/chris/
EMAIL: chris@guiblin.com

PHP (2) - MySQL Database

PHP/MYSQL TUTORIAL/PROGRESS BLOG

Comments/Guest Book

Xampp phpMyAdmin

One thing I like about XAmpp is the built version of phpMyAdmin, which is an easy to use front end for MySQL. This can be accessed by going to "http://localhost/xampp" and following the link to phpMyAdmin under the tools section of the left hand navigation, on the XAmpp front page (http://localhost/phpMyAdmin).

CREATING A NEW DATABASE
A new database can be created easily using the Create new database field:

CHOOSING TABLE NAME AND NUMBER OF FIELDS
I have named my database "test1", on the next page choosing "example1" as the new table on the database. I chose five for the Number of fields (different bits of information that will be entered into the table). When creating a database it is best to plan what information is going to be entered into the table and entering the Number of fields accordingly. Both the database and table name should be relevant to what it is going to be used for so you can remember what is what when you have multiple databases and tables.

FIELD NAMES, TYPES AND LENGTHS

For this Database table I chosen field names which would be used on a comments page or guest book; Name, Subject, Comment and URL, as well as ID which will be the comments number (1 for the first comment, 2 for the second, etc). I have set the field type for ID to INT, with the others as VARCHAR. For the Length/Values, I have chosen numbers which I think will be about right for these field inputs.

I have also chosen for the ID field to be set to 'Primary' and 'auto-increment', which makes it the primary field as well as automatically increasing the number in the field as required for the ID field. Choose the Collation you want (chosing none defaulted to latin1_swedish_ci for me, so I chose to use ucs2_bin).

CONNECT TO DATABASE WITH PHP
Now to the coding. Bring up your text editor and enter the following:
$dbhost = 'localhost';

$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'test1';
mysql_select_db($dbname);
?>

Things you may have changed which may be causing you problems: If you have set up a username and password for your database, change these accordingly under "$dbuser = " and "$dbpass = ". Also, if you didn't call your database "test1" then type whatever you did call it under "$dbname = ". Also ensure there are ";"'s at the end of every line where it is needed.

Save this file as a php file (I named mine connectphp.php) in C:\xampp\apache\htdocs (or the relative location if you put it somewhere else). Access the page in your web browser (http:\\localhost\connectphp.php in my case) and it should be blank, which is a good sign that it hasn't caused an error.

Information/help from:

Collin Jensen
php.net
php mysql tutorial

©Chris Guiblin 2008
URL: www.guiblin.com/chris/
EMAIL: chris@guiblin.com

PHP (1) - Hello World

PHP & MYSQL TUTORIAL/PROGRESS BLOG

Comments/Guest Book

PAGE 1 "HELLO WORLD"

Introduction

For this tutorial bog I am using Xampp running all the available services included with it on a home computer not running as a web server. Most of the information is going to be the same whether or not you are using Xampp. One thing to note is that not all webspace providers support php and mysql. If you are using a service like this, check whether php and mysql are supported with them before attempting any sort of php /mySQL project.

This tutorial is as much a documentation of what I am doing than teaching how to do it. Although I have some previous experience with web servers, programming and a very small amount with PHP and mySQL, this is a learning experience for me, I just thought it might be useful to write how I am doing it, for anyone else who might be attempting this and has come across this page. If you spot anything wrong then please send a comment or email me and I will check it out.

DOWNLOAD AND INSTALLATION (windows)

Firstly, install XAMPP from
http://www.apachefriends.org/en/xampp.html. I first tried the ZIP file
and tried installing the windows version on my computer. However this
failed, I think because I am using vista and the version wasn't made
for that. So probably best using the installer. Follow the installation
(don't install to program files if in vista, as it will tell you),
enabling the services you want. You must enable php for the above to
work. I have installed everything as I will be dabbling with mysql and
possibly other services later on.

VIEWING/EDITING THE INDEX PAGE

In your web browser go to "http://localhost". This brings up
the yellow and orange xampp page with information and demos, etc. This
will take you to "http://localhost/ampp". Change it to
"http://localhost/index.html to view the index page, which will display
the text "It works!"

To get to where this page is actually stored in your computer,
go to the directory where you installed Xampp ("C:\Xampp" in my case).
You should see a folder named "htdocs". This is where the pages are
stored which will actually be accessible through the web server. In
here you will find a file "index.html", which is the page displayed
when you go to "http://localhost/index.html".

To edit the content of the page, open the file with notepad,
dreamweaver, or any other text editor/web design software. If you don't
understand any of what has come up, you should probably go searching
Google for help or buy a book on basic html. All you need to do to
change the text on the page is to change the text between the tag

Once You have done this, save the text file and reload the page in your browser to display the change.

PHP

Since PHP works differently to html, we will need to change
the file in a few ways to make it work using php. Php in itself is not
built into browsers like html is, therefor it is up to the server to
deal with the php code, and that is why php needed to be installed as a
service earlier.

To create a php document, you must start with "<h>" to show that this is the end of the php code.

To create the words "Hello World" or "It works!" you use
the command "echo" which just means to display the value of something.
In this case you just want to display a string value, so you just need
to type the following:

<?php
echo "Hello";
?>

notes: you must put ";" at the end of echo "Hello" to show
that this is the end of the line of code, and to allow the action to be
performed.

Information/help from:

Collin Jensen
php.net
php mysql tutorial

©Chris Guiblin 2008
URL: www.guiblin.com/chris/
EMAIL: chris@guiblin.com

PHP, Programming etc

I have tried several half attempts at installing php and mysql onto my server and gave up, I havn't got anywhere web design or php wise. I keep setting myself projects to get this done, and to create a website, but then I keep giving up.Anyway my friend from about five years back (Jensen) who used to make php websites for my comic management pointed me in the direction of an application called XAMPP which does the whole php, mysql thingy really easily. For now I just have it on my desktop computer to learn some php. Once I have some stuff to put up I will probably install it on the server.

Anyway, I think it is working:Not actually tried it out yet, but I thought I would post something since it has been a while. I have been away for three weeks visiting family (instead of the originally planned one or two weeks). Although I got a lot of designs and stuff done I didn't have access to a computer and so didn't get anything done in any projects I was hoping to achieve. Anyway, my newly revised aims for the rest of the summer are to:1/ learn php and get some basic websites made using the language2/ Design a simple, but polished room in Maya3/ Create an interactive comic/gameI have also added a plug in for Firefox called ScribeFire which allows you to post directly to your blog from your web browser. I haven't tried posting yet, but if this blog posts successfully then I would definitely recommend it.

Anyway my friend from about five years back (Jensen) who used to make php websites for my comic management pointed me in the direction of an application called XAMPP which does the whole php, mysql thingy really easily. For now I just have it on my desktop computer to learn some php. Once I have some stuff to put up I will probably install it on the server.

Anyway, I think it is working:

Not actually tried it out yet, but I thought I would post something since it has been a while. I have been away for three weeks visiting family (instead of the originally planned one or two weeks). Although I got a lot of designs and stuff done I didn't have access to a computer and so didn't get anything done in any projects I was hoping to achieve. Anyway, my newly revised aims for the rest of the summer are to:

1/ learn php and get some basic websites made using the language
2/ Design a simple, but polished room in Maya
3/ Create an interactive comic/game

I have also added a plug in for Firefox called ScribeFire which allows you to post directly to your blog from your web browser. I haven't tried posting yet, but if this blog posts successfully then I would definitely recommend it.

(update: it works great)

Chris Guiblin's Multimedia Blog