PHP Tutorial - Databases in PHP - MySQL in PHP - Picking a DB

By Vince Barnes

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!

 



Make a Comment

Loading Comments...

  • Web Development Newsletter Signup

    Invalid email
    You have successfuly registered to our newsletter.
  •  
  •