Please note:

This material is adapted from Build Your Own Database Driven Website Using PHP and MySQL, by Kevin Yank. 3rd Edition, published by Sitepoint Pty. Ltd. ISBN 0-9579218-1-0. The book includes much more information, and you are encouraged to buy it!

Chapter 4 Notes, page 1

Publishing MySQL Data on the Web

Here is a step-by-step review of what happens when a user goes to your database-driven website and looks for information:

• The visitor's web browser requests a web page via a URL.
• The web server software you are running, such as Apache or Microsoft's Internet Information Server, recognizes that the file being requested is a PHP script. It interprets the file using its PHP plug-in.
• Certain PHP commands connect to the MySQL database and request the content that belongs in the web page.
• The MySQL database responds by sending the requested information to the PHP script.
• The PHP script stores the information in one or more PHP variables, and uses the "echo" function to output the content as part of the web page.
• The PHP plug-in finishes by handing a copy of the HTML it has created to the web server.
• The web server sends the HTML to the web browser. However, instead of coming from an HTML file, the page is the output provided by the PHP plug-in.

PHP has support built in that allows it to make a connection to MySQL. Here is the PHP function call that establishes the connection:

$dbcnx = mysql_connect('localhost', 'root', 'mypasswd');

The values of the three function parameters may differ for various MySQL servers; what matters is that the value returned by "mysql_connect" (called a connection identifier), is stored in the variable "$dbcnx."

We should allow for the possibility that an attempt to connect will fail - the server could be down, or the username and password may be wrong, or something else may go awry. If that happens, the mysql_connect function will not return a connection identifier, and we would want an error message to appear to let us know that no connection had been made:

$dbcnx = @mysql_connect('localhost', 'root', 'mypasswd');
if (!$dbcnx) {
echo ('<p>Sorry, Charlie. No connection this time.</p>');
exit();
}

Note three things in the above: 1) the "@" symbol in front of the mysql_connect function. This will suppress any error messages that would normally be generated by the function if the connection failed. Hence its name, the error suppression operator. This allows us to put in our own, somewhat sarcastic, error message; 2) the "not" operator, or !, in front of the $dbcnx variable. This says, "If the connection doesn't work...." - in which case the !$dbcnx would evaluate to TRUE, and the error message would apply. If the connection works, then the "not" condition does not apply and the error message would not appear; and 3) the "exit" function, which takes no parameters. All it does is to tell PHP to stop reading the page at that point. If there's no connection, that's what you would want.

But let's say it does work. We add a message to the above:

$dbcnx = @mysql_connect('localhost', 'root', 'mypassword');
if (!$dbcnx) {
echo ('<p>Sorry, Charlie. No connection this time.</p>'');
exit();
}

if($dbcnx) {
echo ('<p>You are connected. Have fun.</p>');
}

Let's give it a shot. Click on this link to access the file connect1.php. Remember, this has to go through the web server. Fire away!

Now That You are Connected...

Well, that was fun, so let's go get some data. Continuing with the script: First, select the database you want, songs, in this case. [I couldn't use the jokes example; I don't know any jokes.]:

mysql_select_db('songs', $dbcnx);

The $dbcnx is there to tell the function which database connection to use. It's not required, because the function would use the last connection opened if you left it out. We can use it to add an error message in case we don't connect to the database:

if (!@mysql_select_db('songs')) {

die('<p>Unable to locate that there database right this second.</p>');
}

echo('<p>Ok! Got your database right here.<p>');

If the database is selected, it says so.

Note that this time we didn't assign the result of a function to a variable (as we did with the above $dbcnx = @mysql_connect('localhost', 'root', 'mypasswd');) and then check to see if the variable was true or false. We just checked to see if the result of the function is true or false. So if we can't find that database, the function will evaluate to true, and the error message will appear. BTW: "die" is a function that acts like "echo" followed by a call to "exit."

Now that we have made a connection and selected a database, we can send a query string to the database to enter, delete, change, or view data.