There are five steps to putting MySQL data onto a Web page. Here are five chunks of code and an explanation for each one. This is a long process but once you understand what is happening all you need to do is copy and paste the code onto each new PHP/MySQL page you create.
To try this code you will need a database with a table in it called firsttable (containing data about CDs). This was created when you learned about database theory and PHPMyAdmin use. If you do not have firsttable import it using this link.
Connect
MySQL is a server database and so a connection needs to be created to the server. Create a blank PHP page (with just the start and end PHP tags and then insert this:
$mysqlserver="localhost";
$mysqlusername="test";
$mysqlpassword="test";
$link=mysql_connect($mysqlserver, $mysqlusername, $mysqlpassword) or die ("Error connecting to mysql server: ".mysql_error());
The first three lines store needed data into variables. You will need to change these to match your server location and user. The first variable holds the name of the server (here localhost which will work for any situation where the PHP Web server and MySQL server both live on the same system.
The final line uses a PHP function to start the connection. The "or die" part says that if the connection attempt fails then PHP should give up and send you a message. The message should tell you where the problem happened to help in troubleshooting. A mysql_error will be shown as well. The $link is like a pointer - a way of referring to the connection to the database.
This connection will stay live until the end of the page where you need to close it.
Paste that code into a blank PHP page called firstmysqlpage.php. Upload and test it. It should be blank unless there is a problem with your connection. You could ECHO $link to prove it worked although that won't tell you anything especially useful.
Choose database
The server may hold many databases so you need to pick one by changing th value assigned to the variable. The database must be one which the user named above has access to.
$dbname = 'test'; // change this to the name of your database
mysql_select_db($dbname, $link) or die ("Error selecting specified database on mysql server: ".mysql_error());
$link is the connection to the server. nThe PHP function mysql_select_db() chooses the database you named. If there is any problem the die(0 function will tell you.
Remember that you need to change the database name from "test" to the name of your database which holds the firsttable and secondtable from the background page which explains how to create MySQL tables in PHPMyAdmin. You must have those tables to finish this page.
Try it. The "or die" will give clues to you if the choice of database fails for any reason (the database may not exist because the name is mis-typed).
Grab the data
Now that there is a connection to the required database the data is available. MySQL uses structured query language where communication with the database is done by text queries. These are not all queries in the sense of questions as some are used to create databases, delete tables, add users and so on. In this case the query says "Select all the data from firsttable:
$cdquery="SELECT * FROM firsttable";
$cdresult=mysql_query($cdquery) or die ("Query to get data from firsttable failed: ".mysql_error());
The query is stored in a variable ($cdquery here but it could be anything). It is then used by the PHP function mysql_query() which sends it to the server as an SQL query. The result is then available but more on that soon.
The query is fairly easy to understand. A SELECT query gets data from a table and the asterisk (*) says get all fields. The FROM says which table to get the data from.
Try the extra two lines but you still should have a blank page unless there are errors
Use the data
Currently you might think that $result contains the results. It doesn't. It holds a pointer to the results, a shortcut. Try:
echo "<p>$cdresult</p>";
You can make use of the pointer by communicating with the server again:
$cdnumrows=mysql_num_rows($cdresult);
echo "<p>The number of CDs in your database is: $cdnumrows</p>";
The first line uses the pointer to obtain the number of rows which were returned by the query (it literally says to the MySQL server "Oi, how many results are there?"). The result will be the number of records in firsttable on your server. Then the third line echoes that number to the Web page.
Try it and you should see the correct number of CDs (check it in PHPMyAdmin) which could be none! This is still fairly pointless but with all this done properly you will soon be using the actual data in Web pages.
Close the connection
The connection to the server can be closed now. Once closed all results will be lost so this goes at the very end of the page.
mysql_close($link);
The page will still work if the connection is not closed at the end but it may slow down the server.
Error handling
The above xamples are fine for development and practise but when you deploy pages to public servers you do not really want users to see MySQL errors. Therefore you could adapt the code to look more like this:
$link=mysql_connect($mysqlserver, $mysqlusername, $mysqlpassword) or die ("Page error 4287: sorry there seems to be a problem."));
Taking out the mysql_error() function means no possibly useful information about your database is revealed but the page still stops if there is an error. The error code here is not necessary but now if a user reports the error to you it will be possible to find which line of code went wrong (assuming you use different codes each time).
