Tutorial
Click on thumbnailed images to enlarge
Description
A common use of PHP is to connect to a MySQL database. A database can store data for your website, and a script on your webpage can then retrieve that data. This is often used on blog-style webpages; a database holds all the entries of a blog, and then they are output to a dyanamic page. This allows you as the web designer to show a certain number of entries on your front page; it also helps with maintenance, since when you redesign a layout, you only have to update a stylesheet, and not individual blog entries.
This tutorial will show you how to connect to a MySQL database to retrieve the newest five records, and display them on a page. Please note that the reader should have a fair understanding of PHP syntax; I will describe some syntactic elements but will not go into great detail.
The Script
Here is a simple script for retrieving the newest five records from a database and displaying them on a page. I will go over the script pretty much line-by-line below.
Okay, what did you do?
Let's take a look at each line of code, and examine what I did:
This line establishes the connection the MySQL database server. It uses the function [font=courier]mysql_connect()[/font] to do so. [font=courier]mysql_connect()[/font] takes three arguments (actually, it takes several more, but for our purposes, we will use just three):[list=1]
[*]'server': a string representing the name of the server on which the database resides;
[*]'user': a string representing the username of the database;
[*]'password': a string representing the username's password;
[/list]Your webhost should let you know the proper values to fill in.
Note that the line ends with or die(mysql_error());. The [font=courier]die()[/font] function tells the PHP interpreter (the software the processes your script) to halt termination if [font=courier]mysql_connect()[/font] is unable to connect to the database; we pass this function the value from [font=courier]mysql_error()[/font]. [font=courier]die()[/font] will then print that error on the page if a connection to the database cannot be established. In laymen's terms, this says that if we cannot connect to the database for some reason, there's no reason to do anything else (since the rest of the script depends on the database connection) so just stop everything and print an error to the screen.
Note that we also prepend @ to [font=courier]mysql_connect()[/font]; this supresses error output from [font=courier]mysql_connect()[/font] and instead allows [font=courier]die()[/font] to handle all errors. (Much thanks to hlall for pointing this out to me in the first place!)
[font=courier]mysql_select_db()[/font] is a function that selects the proper database to connect to. It takes one argument:[list=1]
[*]'database': a string representing the name of the database to which you wish to connect
[/list]Again, this information should be provided to you by your webhost.
Note also that the same points about [font=courier]die()[/font] take effect on this line, too.
[font=courier]mysql_query()[/font] allows us to retrieve data from the database using a SQL (Structured Query Language) statement. Allow me to explain the statement used in this example:[list]
[*]SELECT is a command that tells retrieves information from the database. Other commands include "INSERT INTO", "DELETE", and "UPDAT", but these are best saved for another tutorial.
[*]TOP 5 tells MySQL to return the first five records that meet our criteria (explained in a second); 5 can be replaced with any number.
[*]FROM blog tells MySQL to select data from the table "blog"; if your table was named, say, "puppies", you'd use "FROM puppies", and so forth.
[*]ORDER BY post_date ASC tells MySQL how to order the data; it should always be used with TOP queries, or you risk getting data in a random order. The explanation of the syntax is:[list]
[*]ORDER BY is the command that tells MySQL to order the data;
[*]post_date is the field to order the data by;
[*]ASC tells MySQL to order the data ascending; that is, from 0 on up. "DESC" can also be used to order down from, say, 10-0. Note that this works on any type of data--numbers, strings, dates, times, and so forth. MySQL knows whether to order numerically, alphabetically, and so forth. (Note that MySQL treats times and dates as numbers, so it can easily order them, too.)
[/list]
[/list]Note that there is much, much more you can do with SQL that is beyond the scope of this tutorial (but could appear in another one, perhaps).
Note that we set the variable [font=courier]$result[/font] equal to the data being retrieved. This is so we can work with it in the next line.
This sets up a while loop, which is a loop that continues looping as long as the condition in the parentheses is true. (PHP treats any non-zero value as true.)
So what about the while [font=courier]$row = mysql_fetch_assoc($result)[/font] part? Well, the [font=courier]mysql_fetch_assoc()[/font] takes a MySQL result (in this case, the variable [font=courier]$result[/font]) and returns an array whose indexes are named according to the column names in the database. So what does that mean? Well, say you had columns named "title", "post_date", and "post". [font=courier]mysql_fetch_assoc()[/font] would return an array with the following values:[list]
[*]$row['title'] = 'the title of your entry'
[*]$row['post_date'] = your entry's post date
[*]$row['post'] = 'the text of your entry'
[/list]The great thing about [font=courier]mysql_fetch_assoc()[/font] is that it can easily be used in a loop, for two reasons:[list]
[*]Each subsquent call to [font=courier]mysql_fetch_assoc()[/font] gets the next row of data. The first time the loop iterates, it gets the first record--in this case, your newest entry. The next time the loop iterates, it automatically fetches the next row--your second post--and so on.
[*][font=courier]mysql_fetch_assoc()[/font] returns false when it has fetched all rows, so the loop automatically ends because its condition statement is now false.
[/list]
These lines simply output the data using [font=courier]echo()[/font]. Note that array values, like normal variables, can be processed automatically by PHP; however, it is best to surround them in curly braces {} so PHP knows they are array variables and not literal text.
And, of course, we signify the end of the loop with a closing brace }.
[font=courier]mysql_free_result()[/font] frees any memory associated with the query we just ran on the database; it is not strictly necessary (memory is freed upon termination of the script) but can make your code more efficient. We prepend @ because we don't care about any errors with that function, since it's not necessary anyway.
[font=courier]mysql_close()[/font] closes the connection to the database. It takes one argument, which is the pointer returned from [font=courier]mysql_connect()[/font] that points to the database you wish to close. Again, it is not strictly necessary (PHP closes connections upon termination of the script) but can be more efficient. Like [font=courier]mysql_free_result()[/font], we prepend @ because we're not concerned about errors with this function.
That, in a nutshell, is a simple way to connect to a MySQL database and retrieve data. Of course, most scripts will be slightly more complicated in how they handle data, and will probably update or add new data to the database; but this shows a simple way to connect to a database. (Perhaps we can save the more complicated stuff for a sequel--or should I say SQL?! Heh heh.)
A common use of PHP is to connect to a MySQL database. A database can store data for your website, and a script on your webpage can then retrieve that data. This is often used on blog-style webpages; a database holds all the entries of a blog, and then they are output to a dyanamic page. This allows you as the web designer to show a certain number of entries on your front page; it also helps with maintenance, since when you redesign a layout, you only have to update a stylesheet, and not individual blog entries.
This tutorial will show you how to connect to a MySQL database to retrieve the newest five records, and display them on a page. Please note that the reader should have a fair understanding of PHP syntax; I will describe some syntactic elements but will not go into great detail.
The Script
Here is a simple script for retrieving the newest five records from a database and displaying them on a page. I will go over the script pretty much line-by-line below.
<?php
//CONNECT TO DATABASE
$link = @mysql_connect('server', 'user', 'password') or die(mysql_error());
$select = @mysql_select_db('database') or die(mysql_error());
//RETRIEVE FIRST FIVE ENTRIES
$result = @mysql_query('SELECT TOP 5 * FROM blog ORDER BY post_date DESC') or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
echo "<h1>{$row['title']}</h1>\n";
echo "<h2>{$row['post_date']}</h2>\n";
echo $row['post'];
}
@mysql_free_result($result);
//CLOSE DATABASE
@mysql_close($link);
?>
//CONNECT TO DATABASE
$link = @mysql_connect('server', 'user', 'password') or die(mysql_error());
$select = @mysql_select_db('database') or die(mysql_error());
//RETRIEVE FIRST FIVE ENTRIES
$result = @mysql_query('SELECT TOP 5 * FROM blog ORDER BY post_date DESC') or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
echo "<h1>{$row['title']}</h1>\n";
echo "<h2>{$row['post_date']}</h2>\n";
echo $row['post'];
}
@mysql_free_result($result);
//CLOSE DATABASE
@mysql_close($link);
?>
Okay, what did you do?
Let's take a look at each line of code, and examine what I did:
$link = @mysql_connect('server', 'user', 'password') or die(mysql_error());
This line establishes the connection the MySQL database server. It uses the function [font=courier]mysql_connect()[/font] to do so. [font=courier]mysql_connect()[/font] takes three arguments (actually, it takes several more, but for our purposes, we will use just three):[list=1]
[*]'server': a string representing the name of the server on which the database resides;
[*]'user': a string representing the username of the database;
[*]'password': a string representing the username's password;
[/list]Your webhost should let you know the proper values to fill in.
Note that the line ends with or die(mysql_error());. The [font=courier]die()[/font] function tells the PHP interpreter (the software the processes your script) to halt termination if [font=courier]mysql_connect()[/font] is unable to connect to the database; we pass this function the value from [font=courier]mysql_error()[/font]. [font=courier]die()[/font] will then print that error on the page if a connection to the database cannot be established. In laymen's terms, this says that if we cannot connect to the database for some reason, there's no reason to do anything else (since the rest of the script depends on the database connection) so just stop everything and print an error to the screen.
Note that we also prepend @ to [font=courier]mysql_connect()[/font]; this supresses error output from [font=courier]mysql_connect()[/font] and instead allows [font=courier]die()[/font] to handle all errors. (Much thanks to hlall for pointing this out to me in the first place!)
$select = @mysql_select_db('database') or die(mysql_error());
[font=courier]mysql_select_db()[/font] is a function that selects the proper database to connect to. It takes one argument:[list=1]
[*]'database': a string representing the name of the database to which you wish to connect
[/list]Again, this information should be provided to you by your webhost.
Note also that the same points about [font=courier]die()[/font] take effect on this line, too.
$result = @mysql_query('SELECT TOP 5 * FROM blog ORDER BY post_date DESC') or die(mysql_error());
[font=courier]mysql_query()[/font] allows us to retrieve data from the database using a SQL (Structured Query Language) statement. Allow me to explain the statement used in this example:[list]
[*]SELECT is a command that tells retrieves information from the database. Other commands include "INSERT INTO", "DELETE", and "UPDAT", but these are best saved for another tutorial.
[*]TOP 5 tells MySQL to return the first five records that meet our criteria (explained in a second); 5 can be replaced with any number.
[*]FROM blog tells MySQL to select data from the table "blog"; if your table was named, say, "puppies", you'd use "FROM puppies", and so forth.
[*]ORDER BY post_date ASC tells MySQL how to order the data; it should always be used with TOP queries, or you risk getting data in a random order. The explanation of the syntax is:[list]
[*]ORDER BY is the command that tells MySQL to order the data;
[*]post_date is the field to order the data by;
[*]ASC tells MySQL to order the data ascending; that is, from 0 on up. "DESC" can also be used to order down from, say, 10-0. Note that this works on any type of data--numbers, strings, dates, times, and so forth. MySQL knows whether to order numerically, alphabetically, and so forth. (Note that MySQL treats times and dates as numbers, so it can easily order them, too.)
[/list]
[/list]Note that there is much, much more you can do with SQL that is beyond the scope of this tutorial (but could appear in another one, perhaps).
Note that we set the variable [font=courier]$result[/font] equal to the data being retrieved. This is so we can work with it in the next line.
while ($row = mysql_fetch_assoc($result)) {
This sets up a while loop, which is a loop that continues looping as long as the condition in the parentheses is true. (PHP treats any non-zero value as true.)
So what about the while [font=courier]$row = mysql_fetch_assoc($result)[/font] part? Well, the [font=courier]mysql_fetch_assoc()[/font] takes a MySQL result (in this case, the variable [font=courier]$result[/font]) and returns an array whose indexes are named according to the column names in the database. So what does that mean? Well, say you had columns named "title", "post_date", and "post". [font=courier]mysql_fetch_assoc()[/font] would return an array with the following values:[list]
[*]$row['title'] = 'the title of your entry'
[*]$row['post_date'] = your entry's post date
[*]$row['post'] = 'the text of your entry'
[/list]The great thing about [font=courier]mysql_fetch_assoc()[/font] is that it can easily be used in a loop, for two reasons:[list]
[*]Each subsquent call to [font=courier]mysql_fetch_assoc()[/font] gets the next row of data. The first time the loop iterates, it gets the first record--in this case, your newest entry. The next time the loop iterates, it automatically fetches the next row--your second post--and so on.
[*][font=courier]mysql_fetch_assoc()[/font] returns false when it has fetched all rows, so the loop automatically ends because its condition statement is now false.
[/list]
echo "<h1>{$row['title']}</h1>\n";
echo "<h2>{$row['post_date']}</h2>\n";
echo $row['post'];
}
echo "<h2>{$row['post_date']}</h2>\n";
echo $row['post'];
}
These lines simply output the data using [font=courier]echo()[/font]. Note that array values, like normal variables, can be processed automatically by PHP; however, it is best to surround them in curly braces {} so PHP knows they are array variables and not literal text.
And, of course, we signify the end of the loop with a closing brace }.
@mysql_free_result($result);
//CLOSE DATABASE
@mysql_close($link);
?>
//CLOSE DATABASE
@mysql_close($link);
?>
[font=courier]mysql_free_result()[/font] frees any memory associated with the query we just ran on the database; it is not strictly necessary (memory is freed upon termination of the script) but can make your code more efficient. We prepend @ because we don't care about any errors with that function, since it's not necessary anyway.
[font=courier]mysql_close()[/font] closes the connection to the database. It takes one argument, which is the pointer returned from [font=courier]mysql_connect()[/font] that points to the database you wish to close. Again, it is not strictly necessary (PHP closes connections upon termination of the script) but can be more efficient. Like [font=courier]mysql_free_result()[/font], we prepend @ because we're not concerned about errors with this function.
That, in a nutshell, is a simple way to connect to a MySQL database and retrieve data. Of course, most scripts will be slightly more complicated in how they handle data, and will probably update or add new data to the database; but this shows a simple way to connect to a database. (Perhaps we can save the more complicated stuff for a sequel--or should I say SQL?! Heh heh.)
Tutorial Comments
Showing latest 1 of 1 comments
