We’re in the process of adding access to a MySQL Database to our HTML page
and now we need to pick out the database we want to use and make sure that
everything is properly available.
Most of the time, when you’re using PHP in your web pages, the database you
want to use already exists. The most common method for creating databases
is by use of a MySQL management interface which is installed on the server
system. (Although it’s really outside the scope of this tutorial series, I
would like to point you to a couple of examples of administration tools:
http://www.mysql.com/products/administrator is a link to the MySQL
administrator from the MySQL folks themselves, and
http://www.phpmyadmin.net/home_page/index.php is a link to an example of an
administration tool that runs through the web — very cool, and it’s written in
PHP!) Even though this is the case, I will include an example of how to
create a database using PHP. If I didn’t, Murphy’s Law would come into
play, and your first requirement would be to create a database from within a web
page!
Selecting A Database
Here’s something that’s not too complicated: to select a MySQL database in
PHP use the mysql_select_db ( ) function, like this:
mysql_select_db ("mydatabase");
Couldn’t be simpler, as long as everything went well — more on that in a
minute.
Creating a Database
OK, we’re going to create a database, and to honor the one responsible for
this need, our database will be called "Murphy". A database is created by
using a query, and therefore the mysql_query ( ) function. (Remember that
I talked about the way that everything is thought of as a query, even though it
might not actually be making any sort of "enquiry".) The query will be
given the CREATE DATABASE parameter, like this:
mysql_query (‘CREATE DATABASE murphy’);
Again not too complicated, as long as everything went well. Now it’s
time to think about what to do if everything didn’t quite go as planned.
I’ve said this before, and I’ll add the caveat that I use the word "hacker" in
it’s old sense: the difference between a hacker and a programmer is that a
hacker works and works until they find a way to make their code work, but a
programmer works and works until they also find every way it won’t work.
Being so, as programmers, we must find a way to handle every error that might
come our way.
Handling Errors
In the
last part of this tutorial series, I discussed the use of the Error Control
Operator and the die ( ) function. Now we’ll add one more function to our
arsenal. The MySQL support in PHP includes a function called mysql_error (
) whose very useful purpose is to provide a text version of an error returned by
the MySQL server. We can certainly take advantage of that in our error
handling!
I also want to introduce another element here. This is purely a matter
of coding technique. There are many ways to write this piece of code, but
I feel this form to be quite elegant. You will remember that an "if"
statement has this basic format:
if (condition)
{do this if condition is true}
else
{do this if condition is false}
We can take advantage of that. When a function performs properly it
returns with a condition of "true", and when something goes wrong it
returns with a "false" condition. Of course, it can also return values,
error codes and the like, but for the moment lets ponder just the condition.
Since it returns a condition, we can use the function call as the "condition" in
an "if" statement. This will allow us to put code in the "true" side
of the statement to do what we want when everything is ok, and to put code in
the "false" side to handle our errors.
Let’s build an example. First, we’re going to connect to the server,
and if everything is good, we’ll create a database. I there were errors,
we’ll terminate the program, displaying an error message. In the process
of creating the database, if everything goes well, we’ll select the database so
that we can use it, and if there are errors we’ll terminate the program,
displaying an appropriate message. Similarly, when selecting the database,
we’ll use messages to let us know how things went.
Programmatically, this means putting an "if" statement inside the "do this if
condition is true" code. When an "if" statement is put into either side of
another "if" statement in this fashion, it is know as "nesting" the "if"
statements. When you write nested "if"s it is highly recommended that you
use indentation and spacing to help you keep track of where you are. It is
altogether too easy to have and extra or a missing parenthesis and completely
change the logic flow of your program — often without causing any PHP syntax
error. Be warned! Be careful!
Now, here’s our code (you might want to spend a little time following through
this to make sure you fully understand it):
if ( $connid = mysql_connect ( ‘localhost’ ‘username’ ‘password’ ) )
{ print ‘<p> Connected to MySQL Server. </p>’;
if ( @mysql_query ( ‘CREATE DATABASE murphy’ ) )
{ print ‘<p> Database murphy
created. <p>’;
if ( @mysql_select_db
( ‘murphy’ ) )
{
print ‘<p> Databse murphy selected </p>’;
}
else
{
die ( ‘ <p> Database selection failed because ‘ , mysql_error ( ) , ‘ </p>’ )
}
}
else
{ die ( ‘ <p> Database creation
failed because ‘ , mysql_error ( ) , ‘ </p>’ )
}
}
else
{ die ( ‘ <p> Database Server connection failed because ‘ ,
mysql_error ( ) , ‘ </p>’ )
}
Oh yes, we’re getting into it now!