Part 1 of my Beginning PHP and MySQL for Dynamic Web Pages showed you how to access a MySQL server, access a database and even retrieve and write data to that table. In this article we will go a little deeper and discuss retrieving entries from the table and how to use them in a PHP document or documents. We will discuss how to create one page that can actually support an unlimited number of pages, if I have confused you with that last statement then just read on because it should all come very clear soon.
As I stated in the first part of this 2 part article, these statements are very powerful and you can create an entire web site which is database driven very quickly and very easily. The greatest thing about creating a database driven website is that you can set it up so that it is very easy to update. Instead of having to update the HTML (or in this case PHP) page every time you want to add more information all you have to do it update the table in the database, which is almost always much easier. It also leaves you room to expand by allowing you to create an incredibly large website without all of the hassle.
First lets look at what we have in our database the database is called ‘mysite’ and we are going to look at the table ‘mypages’:
ID | Title | Page |
1 | Home Page | Welcome to my home page|
2 | About Page | This is the about me page |
3 | Links Page | This is the links page |
We have three entries in our table so lets create a links page that is dynamic, meaning that if we want to create a new link all we have to do is update the ‘mypages’ table with a new entry and we will add a link.
$conn = mysql_connect(’localhost’, ‘mike’, ’mikenetpc’);
mysql_select_db(’mysite’);
$result = mysql_query(“SELECT * FROM mypages”);
while ($row=mysql_fetch_array($result))
{
echo “<a href=’viewpage.php?id=”.$row['ID'].” ‘>”.$row['Title'].”</a>”;
}
The above snippet of code will access the database and get everything from the ‘mypages’ table which is three rows. It will then put those results into an array which is stored in the $row variable. We will then use the While loop to go through each row and create a link pointing to viewpage.php but also notice the each link will be unique because we have added the $row['ID'] to the end of it. You can add variables after the actual page name once you place the ? there. These variables can be accessed on the page they are referring to, so the variable id can be used on the viewpage.php page.
When the web server spits out the HTML code for these links it will look like the following:
<a href=’viewpage.php?id=1′>Home Page</a>
<a href=’viewpage.php?id=2′>About Page</a>
<a href=’viewpage.php?id=3′>Links Page</a>
If we had twenty entries in our table we would have generated twenty links. Next you are obviously thinking OK, well what does the viewpage.php code look like and it is actually very simple to do. Lets same someone clicked on our about page link and it took them to viewpage.php?id=2
viewpage.php
<html>
<head>
<?php
$conn = mysql_connect(’localhost’, ‘mike’, ’mikenetpc’);
mysql_select_db(’mysite’);
$result = mysql_query(“SELECT * FROM mypages WHERE id=”.$_GET['id']);
$row = mysql_fetch_array($result);
echo “<title>”.$row['Title'].”</title>”;
?>
</head>
<body>
<?php
echo “<center><h1>”.$row['Title'].”</h1></center>”;
echo $row['Page'];
?>
</body>
</html>
The top part of this code you should already know because we are just accessing the server and database. I have placed this code in the <head> tags because the <head> tags are loaded before the rest of the page so I know that my PHP code has been executed and is ready plus I had to access the Title field of the table for the <title> tag. Notice that we used the WHERE command in out MySQL query, this command specifies a specific record or records based on criteria. The $_GET['id'] commands gets the variable that we placed in the URL after the ?. Since we know that only one entry in the table as the id we don’t need to do a loop because we know there is only one entry in the array. Next we use the same commands we did before to create the links but we organize them and create out web page.
Hopefully the beginning makes a little more since when I said how we can have one page acutally be an unlimited amount of pages.
I hope this has been enough information for you for now and you should have enough information to proceed on with creating your own dynamic web site using PHP or MySQL. Just make sure you get some good inexpensive hosting, like Cheap As Dirt Hosting or any of the other ones.
If you have any trouble with the code I have posted here feel free to post a message here or contact me.