Active Web Hosting Logo


Using PHP With A MySQL Database

Using PHP Scripting and your MySQL database, you can create interactive web sites. Some examples of such sites are forums, news pages, and web blogs. You can also create your own scripts to store information in your database. This tutorial may not be for the total beginner but if you take your time to learn some of the background information, you should be well on your way to using PHP with your MySQL database.

What You Need To Get Started

Before you get started, you will need a CGI account so that you can run the PHP scripts, which all have to have a filename extension of .php and you'll also need to have a database. First request a CGI account then request your database. After this, you will need to learn about databases, how they store information, how to write HTML and PHP code. Here is a list of links to get you started. You'll need to follow these in order:

  1. How To Request A CGI Account
  2. How To Request A MySQL Database
  3. Create A Web Page
  4. Upload A Web Page
  5. Using PHP
  6. Using PHP With A MySQL Database

After you have reviewed the above list of tutorials, you have enough background to start using PHP to create database tables, add, retreive, change and delete database records. This tutorial only covers the basics. If you want to learn more or get into more advanced topics, please see More Information About CGI, Perl, and Other Scripting Languages.

Creating Tables

In order to use PHP in a database, you have to insert tables and fields so that you have some place to put your data. Let's create a simple a simple table with a set of fields to store a person's name, address, city, state and zip code.

<html>
<head>
<title>Database Setup</title>
</head>
<body>

<?php
$database = mysql_connect("localhost", "yourdomaincom", "your_mysql_password");
mysql_select_db("yourdomaincom", $database);

$outcome = mysql_query("CREATE TABLE users(id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, name varchar(20), address varchar(50), city varchar(30), state varchar(2), zip varchar(5), PRIMARY KEY(id), UNIQUE id(id))");

mysql_close();

echo "Your table has been created.";
?>
</body>
</html>

Looking at the above code, the first two lines will let you connect to your database. You can use any variable name instead of $database if you choose. Just be sure to use the same one in place of $database in the mysql_select_db() function. All instances of yourdomaincom should be your actual domain name without the dot. your_mysql_password should be replaced with your MySQL password. Be sure to use mysql_close() anytime you are done accessing the database.

What if I don't want my MySQL password displayed on my web page!

It won't be. If you followed the Using PHP tutorial, you already saw that the server will never print the actual php code in the web browser, but merely the results. So your database username and password will never show up in anyone's web browser, even if they choose to view the page source code.

Going back to our code above, notice the highlighted area:

$outcome = mysql_query("CREATE TABLE users(id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, name varchar(20), address varchar(50), city varchar(30), state varchar(2), zip varchar(5), PRIMARY KEY(id), UNIQUE id(id))");

First, this entire piece of code should be typed on one line and not split up in multiple lines.

Looking at the code, you'll se the mysql_query() function. This says to ask MySQL to do something. Here we are asking it to CREATE a TABLE called users. Then in the parenthesis, we see what fields will go into the users table. The first field is the id field. It's always nice to have one of these as it makes sorting and retrieving data much easier. We set it up as a small number value tinyint(4), and a few other fields, including the ability to AUTO_INCREMENT the id number for each entry added. Basically just use the id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, for the first field in your tables if you want an unique ID set for each record that gets added to that table.

Now we set up the fields we will use. Note that we are separating every field with a comma. varchar means create a text field. The number in parenthesis after varchar is the maximum number of characters each field should hold. Lastly, since we are using an id field to number each entry, we use PRIMARY KEY(id), UNIQUE id(id) at the very end of our field list to set this up. You have now created a table in which you can store data. Save this file as createtable.php, upload it to your cgi server and then point your browser to http://cgi.yourdomain.com/createtable.php to run it. You should see a message saying your table has been created.

Adding Data to the Database

Next we will add some data to the table. Start a new file called insertdata.php

<html>
<head>
<title>Insert Data</title>
</head>
<body>
<?php
As usual we start off the same way we did earlier.
// No input yet, please fill in form.
if (isset($HTTP_POST_VARS['done'])){
  // Read the form data into variables
  $name    = $HTTP_POST_VARS['visitor_name'];
  $address = $HTTP_POST_VARS['address'];
  $city    = $HTTP_POST_VARS['city'];
  $state   = $HTTP_POST_VARS['state'];
  $zipcode = $HTTP_POST_VARS['zipcode'];

In the code above, we are creating variables for each of the form values. Here we see a new useful function, isset() This function is used to check to see if a certain variable contains a value. It will return true if it does and thus result in the if statement to turn out to be true above. This is often a better way to write the code as it insures that the value is properly tested. It also comes in handy when you need to test more than one value as shown below:

  if(isset($name) && isset($address) && isset($city) && isset($state) && isset($zipcode)){

Notice now we have the && between each isset() function. This means "and". In other words, the $name variable and the $address variable and the $city variable (and so on) must have values in them for the if statement to be true. If any one of them does not have a value, then the test will not pass and the php code will continue from the else below. This means that the visitor must fill in all the mentioned values to get their information added in the database.

    // Add the information to the database.
    $database = mysql_connect("localhost", "yourdomaincom", "your_mysql_password");
    mysql_select_db("yourdomaincom", $database);

    $outcome = mysql_query("INSERT INTO users (name,address,city,state,zip) VALUES
    ('$name','$address','$city','$state','$zipcode')");
    mysql_close();
    echo "Your data has been added.";

In the above code, the highlighted area should be all on one line. First we again establish a connection to the database. Now we see new values in the mysql_query() function. The INSERT INTO tells MySQL to actually add information to the database. Next we put in parenthesis the field names that we want to add information to, in the order we intend to add them. After that the VALUES says the actual values are coming up next. In parenthesis after that you'll notice our variable names which referred to the form values. These are also in single quote marks. And, you'll notice they are in the exact same order as we put the field names before. Where's the id field? Since that is automatically updated with the next number each time we add a record, we don't need to add anything to it. Again, always close the database connection when you are done accessing it.

  }else{
    echo "You must fill in all fields.";
    echo 'Try again&nbsp;<a href="http://cgi.yourdomain.com/insertdata.php">here</a>';
  }

Above is the code you'd use to warn users to fill in all fields. Remember, if the previous if statement was false, then the block is skiped and the else block is executed instead.

// Here is the form visitors fill out.
} else {
  ?>
    <form method="post" action="<?php echo $_SERVER['PHP_SELF']?>"
      <input type="hidden" name="done">
      <table border="0">
      <tr><td align="right" valign="top">Name:</td>
      <td><input type="text" name="visitor_name" size="20"></td>
      </tr><tr>
      <tr><td align="right" valign="top">Address:</td>
      <td><input type="text" name="address" size="50"></td>
      </tr><tr>
      <tr><td align="right" valign="top">City:</td>
      <td><input type="text" name="city" size="30"></td>
      <tr><td align="right" valign="top">State:</td>
      <td><input type="text" name="state" size="2"></td>
      <tr><td align="right" valign="top">Zip Code:</td>
      <td><input type="text" name="zipcode" size="5"></td>
      </tr><tr>
      <td align="center" colspan="2">
        <input type="submit" value="Send">
        <input type="reset">
      </td></tr></table>
    </form>

Above is the actual form. Note the name= values correspond with what we used in the beginning of the script when assigning our variables.

  <?php
} ?>
</body>
</html>

And now we are done! You can copy and paste the code below if you wish. Go ahead and run it, and then add a few names and addresses to the database.

Displaying Data from the Database

Now we'll take a look at how to retrieve information from the database. The code below shows you how this can be done. Note that the printf() funtion should be all on one line.

$outcome = mysql_query("SELECT * FROM users", $database);
$column = mysql_fetch_array($outcome);
if (isset($field)){
  do{
    if($name == $column["name"]){
      printf("%s<br>%s<br>%s, %s %s<br><br>", $column["name"], $column["address"], $column["city"], $column["state"], $column["zip"]);
    }
  } while ($column = mysql_fetch_array($outcome));
  mysql_close();
} else {
  echo "No matching records were found.";
}

In the above code, you'll notice the highlighted area introduces a new function, mysql_fetch_array() which will get all the fields in a column. However, it will only print out the column that matches the same name as the $name value in the form. Notice there is a do {} while loop. This means that the statements between the brackets {} will be done over and over as long as the while statement is true, meaning as long as there are records to look at. It will search each record that is in the database until there are no more records to look at. The if statement is checking to see if the value from the name field from the form matches the name field currently being looked at from the databasse. If it matches, then the printf() function prints out the user's data. Below is the entire code you can copy and paste:

Changing Data

You can also use PHP to actually change the data in the database. Basically, you would first search for the record you want to change, and then you would use that record ID to change that record's data.

$outcome = mysql_query("SELECT * FROM users", $database);
$column = mysql_fetch_array($outcome);
if(isset($column)){
  do{
    // If the name is found, update the record
    if($name == $column["name"]){
      $id = $column["id"];
      $outcome_update = mysql_query("UPDATE users SET name='$newname' WHERE id=$id");
      echo "Your record has been updated.";
    }
  } while ($column = mysql_fetch_array($outcome));
  mysql_close();
}

Above we are again testing to see if the name field from the form matches the name field of the current record in the database. If it is, then we can proceed to change the value. Notice here we also make use of the ID field. We set it to the current record since that is the one we wish to change.

UPDATE users SET name='$newname' WHERE id=$id is what actually changes the record. We get $newname from a field from the form (see full code below). We are only changing the name field. But we could change more than one if we wish, by separating the fields with a comma. The last field before WHERE should not be followed by a comma, however. Here's an example if we wanted to change both name and address:

UPDATE users SET name='$newname', address="$new_address' WHERE id=$id

You'll also notice that the id field is used after WHERE. This tells MySQL what record to change. Below is the entire code for you to look at and try.

Deleting Data

It is just as easy to delete a record from the database:

do{
  // If the name is found, delete the record
  if($name == $column["name"]){
    $id = $column["id"];
    $outcome_update = mysql_query("DELETE FROM users WHERE id=$id");
    mysql_close();
    echo "Your record has been deleted.";
  }
} while ($column = mysql_fetch_array($outcome));

As you can see, a simple DELETE FROM referencing the id is all you need. Keep in mind that when you delete a record from the database, the id numbers are not reordered to fill in the 'hole'. So when you add another record, the new record would be the next number, not the number deleted.

For example, suppose our records look like this:

Record 1: John Doe
Record 2: Jane Smith
Record 3: Mary Jones
Record 4: [next new record]

If you delete Jane Smith's name (Record #2) then this would happen:

Record 1: John Doe
Record 2:
Record 3: Mary Jones
Record 4: [next new record]

So what happens to these 'holes'? Do they get used? Actually, there are no 'holes'. Just skipped numbers. The ID is just a number to be sure you have the right record you want to work with. The actual number doesn't even matter, as we can just retrieve it as we have in the previous example.

Here is the full code showing how you could ask for and delete a record:

Tips For Creating Scripts

  • The first thing you want to do is plan out exactly how you want to manage your data. Write down on paper the different fields of information you'll want to store and then build your database according to your notes.

  • Be sure your form has all the required fields you need to work with. Also make sure that your database can not be altered unless the correct information is provided.

  • Work with temporary tables such as test_users, etc. so that you do not alter or destroy data already in your database.

  • Use phpMyAdmin to view your data. You would just click on the table you wish to view and then click the browse tab above it to see how your data was entered. Clicking the browse tab anytime you make a change to the database with your script will refresh the view.

  • As your code gets longer and more complicated, you'll want work slowly. It can get frustrating when something is not working right and it's hard to find. Check for a lack of semi-colon at the end of a line, a missing parenthesis or bracket, or a misspelled variable name. Also indent your code and be sure all your if statements and loops (like do/while) are enclosed in brackets {}. These are the most common errors made when working with PHP scripts.

  • Be sure you have the right username, password and database set up. Always use localhost for the host. Also be sure you are working with the correct table within your code.

  • Creating scripts that work with the MySQL database takes a lot of time and practice. The learning curve is worth it once you master this skill and build your own interactive web site.

For More Information

If you would like more information on how to use PHP with your MySQL database, you may want to try finding one of these books at your local library or book store:

 


Home - Support - Management - About Us
... Active Web Hosting, 1445 American Pacific Dr. Ste 110-318, Henderson, NV 89074 ...
Toll-Free (800) 946-7764   Fax: (702) 567-1831