"Handling a database with PHP" can be a bit misleading. PHP is not the one responsible for doing anything to your database. All it does is send the query string to your database and your database executes the query.

PHP's job is to build that query string. This can be done statically or dynamically. Static means not changing. Dynamic means changing. A static query string would look something like this:

SELECT somecol FROM table WHERE id = 1

id is assigned the value of 1. It's hardcoded into the script, no changing that whatsoever, unless for instance you use a variable in place of the value, like this:

SELECT somecol FROM table WHERE id = $id

Using variables is one of the most basic ways of dynamically building a query string. You can also use variables in place of the column name, or even use conditions to dynamically add pieces to the query string. Your query string is just that - a string - so you can use any string manipulation technique, function, construct, etc.. out there on your query string, as long as the end result produces a valid query for your database.

In our script, we are going to execute a few database queries by using some basic methods of dynamic query string creation. There are, of course, a million and one better ways to do what we want to do in this script, but in my experience, elegant coding does not usually make an elegant tutorial example.

The Setup

For this tutorial, we are using MySQL as the database. You need to create a table called "info" with 2 columns: "id" type int auto_increment and "name" type varchar(20).

CREATE TABLE `info` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`id`)
)

For your convenience I have include a query string to make it. Go to your phpmyadmin or whatever and run it. If you are scratching your head wondering what all this means, then this tutorial isn't really for you. Go read up on how to mess around with your database directly (or at least through something like phpmyadmin) and then come back.

The Goods

Okay, okay! Here's the whole script! Step away from the google button!

Anyways, the script will display the names from the database. You can edit the names, delete names, add a new name, and sort by the name or id. The code is divided into two parts: "Dealing with the database" and "List everything out."

<?php
/**** Dealing with the database ****/
// connect to db
$conn = mysql_connect('localhost','dbusername','dbpassword') or trigger_error("SQL", E_USER_ERROR); 
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR); 

// INSERT: if we have a name to add...
if($_POST['name']) {
   // little bit of cleaning...
   $name = mysql_real_escape_string($_POST['name']);
   // insert new name into table
   $sql = "INSERT INTO info (id, name) VALUES ('','$name')";
   $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
} // end if

// UPDATE: if we have name(s) to change...
if($_POST['cname']) {
   // for each name to change...
   foreach($_POST['cname'] as $cid => $cname) {
      // little bit of cleaning...
      $id = mysql_real_escape_string($cid);
      $name = mysql_real_escape_string($cname);
      // update name in the table  
      $sql = "UPDATE info SET name = '$name' WHERE id = '$id'";
      $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
   } // end foreach
} // end if
	 
// DELETE: if we have a name to delete...
if($_GET['name']) {
   // little bit of cleaning...
   $name = mysql_real_escape_string($_GET['name']);
   // delete name from table
   $sql = "DELETE FROM info WHERE name = '$name'";
   $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
} // end if

// ORDERBY: if one of the links was clicked..
if ($_GET['orderby']) {
   // make an aray of allowed names
   $allowed = array('id','name');
   // bit of cleaning...
   $order = mysql_real_escape_string($_GET['orderby']);
   // is it a valid column name? yes: use it. no: default to 'id'
   $order = (in_array($order, $allowed))? $order : "id";
// if no link clicked, default to 'id'
} else {
   $order = "id";
} // end else

// SELECT: get the list of names from database
$sql = "SELECT id, name FROM info ORDER BY $order";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
/**** end deal with the database ****/

/**** list everything out ****/
// list columns
echo <<<LISTCOLS
<form action = '{$_SERVER['PHP_SELF']}' method = 'post'>
<table border = '1'>
   <tr>
      <td><a href = '{$_SERVER['PHP_SELF']}?orderby=id'>id</td>
      <td><a href = '{$_SERVER['PHP_SELF']}?orderby=name'>name</td>
      <td>delete</td>
   </tr>
LISTCOLS;

// loop through list of names 
while ($list = mysql_fetch_assoc($result)) {
echo <<<LISTINFO
   <tr>
      <td>{$list['id']}</td>
      <td><input type = 'text' name = 'cname[{$list['id']}]' value = '{$list['name']}'>
      <td><a href = '{$_SERVER['PHP_SELF']}?name={$list['name']}'>delete</a></td>
   </tr>
LISTINFO;
} // end while

// list input box for adding new entry
echo <<<NEWENTRY
   <tr>
      <td bgcolor = 'gray'></td>
      <td><input type = 'text' name = 'name'></td>
      <td bgcolor = 'gray'></td>
   </tr><tr>
      <td></td>
      <td align = 'center'><input type = 'submit' value = 'submit'></td>
      <td></td>
   </tr>
</table>
</form>	 
NEWENTRY;
/**** end list everything out ****/

?>

Dealing With The Database

The first half of the code we will handle all database interaction. We want to do this first, because we want all changes to be reflected on the list after we hit the submit button, when the page gets displayed again.

In order to do something with your database using PHP, you must first establish a connection to your database. That's just a really fancy way of saying you have to login to the database.

// connect to db
$conn = mysql_connect('localhost','dbusername','dbpassword') or trigger_error("SQL", E_USER_ERROR); 
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);

These first two lines are what we use to connect to and select your database. The mysql_connect function is what connects to the database. It takes 3 arguments: the host, the username for the database, and the password for the database.

Usually if you are running the script on the same server and you don't have weird access restrictions (like if you use one of those crappy "free" hosting services), simply putting 'localhost' as the host should work. A lot of server configurations add prefixes to your database username and database name, like phpfreak_crayonviolent for username and phpfreak_testdb for a database name (note: this name and dbname do not exist so don't bother trying to exploit, lol).

Your host should have some kind of documentation about whether prefixes are added to your stuff, or whether you have to use a specific host name instead of localhost. Talk to them if you need help.

mysql_select_db selects the database you want to use. You want to pick the one your table is in. It requires one argument: the database name. It optionally accepts a second argument, where you can specify the connection stream. I like to specify the connection stream, mostly out of habit.

The database connection (ideally) lasts as long as the script runs. I say "ideally" because hiccups happen. You can also tell PHP to close the connection with mysql_close($conn);. You do not have to supply an argument for it. If you do not supply a connection stream, it will close the last one opened (yes, that does mean you can have multiple connections opened at once). But again, PHP does automatically close the connection after the script finishes executing anyways, so to be honest, I usually don't bother with mysql_close($conn);.

Dealing With The Database: INSERT

In this first chunk of code, we will add a new name to the table, if applicable.

// INSERT: if we have a name to add...
if($_POST['name']) {
   // little bit of cleaning...
   $name = mysql_real_escape_string($_POST['name']);
   // insert new name into table
   $sql = "INSERT INTO info (id, name) VALUES ('','$name')";
   $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
} // end if

02. We first check our post array to see if the user entered a name.

04. If he did, we use mysql_real_escape_string as a measure to prevent sql injection attacks (good idea to read up on how to prevent sql injection attacks, since you're learning how to work with databases now).

06. The next thing we do is make our query string. The string looks just like how you would put it directly into your database, except you have " "'s wrapped around and you're assigning it to a variable.

07. The last thing we do is send the query string to the database. We do this with the mysql_query function. mysql_query only requires one argument: the query string. You don't even have to give it as a variable. You can use the string itself as the argument (wrapped in quotes). I like to keep the string separated from the mysql_query because it's easier to debug if things go wrong.

As with many mysql related functions, mysql_query accepts an optional 2nd argument to specify the connection stream.

You don't technically need to assign mysql_query to a variable. When PHP executes mysql_query, it will either will return a result source if you're expecting some kind of data to be returned, a boolean value "true" (represented by "1") if the query was a success but no data is being returned, or a boolean value "false" (represented by "0") if for some reason the query failed.

Since you will usually always want to know how the query went (like, you're expecting data, or need to know if it failed or not), you need to assign that to a variable. Ideally you will want to setup some kind of error event trapping in case something goes wrong, or check if it's a success so you can tell the user or something, but to keep it simple, I haven't really done anything like that in this script, so I technically didn't need to have that "$result = " part. But it's a habit of mine to do it anyway.

Dealing With The Database: UPDATE

In this chunk of code, we will update names in the database, if applicable.

// UPDATE: if we have name(s) to change...
if ($_POST['cname']) {
   // for each name to change...
   foreach($_POST['cname'] as $cid => $cname) {
      // little bit of cleaning...
      $id = mysql_real_escape_string($cid);
      $name = mysql_real_escape_string($cname);
      // update name in the table  
      $sql = "UPDATE info SET name = '$name' WHERE id = '$id'";
      $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
   } // end foreach
} // end if

02. First we check to see if there are any names to update. If there are, then proceed to the next line of code.

04. The next thing we do is make a foreach loop, to cycle through each name. Down in our form, we made the name's id the key, so that we could pass both id and name at the same time without having to get all fancy about it. So, for each iteration of the foreach loop, $cid will be the current id and $cname will be the current name.

06. & 07. We go ahead and sanitize the variables just like we did before.

09. Here is our update query. We use $name and $id in place of a hard coded name/id, because it will change each iteration of the foreach loop.

10. We send off the query string to be executed, just like before. Again, in this instance, we don't really need to assign it to a variable, since we aren't doing anything with the result anyway.

The foreach loop will cycle through all the current names in the list, updating them in the database one at a time. Now, this isn't very efficient, as there's no reason for us to update something that doesn't need updating. Ideally you should write your script to only update it if it's actually changed. But again, writing efficient code isn't necessarily the goal of this tutorial.

Dealing With The Database: DELETE

In this block of code, we will delete a name from the table.

// DELETE: if we have a name to delete...
if($_GET['name']) {
   // little bit of cleaning...
   $name = mysql_real_escape_string($_GET['name']);
   // delete name from table
   $sql = "DELETE FROM info WHERE name = '$name'";
   $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
} // end if

02. Our form is setup to have a "delete" link next to each name. You simply click on the link and the name is deleted. Since it's a link, we pass the name through the url via the GET method. So the first thing we do is check to see if that variable exists. If it does...

04. Once again we sanitize the variable.

06. We build the query string, inserting the name in the string with $name.

07. We send the string off to the database to be executed. Again, no reason to really assign the result to $result in this example.

Dealing With The Database: ORDER BY

In this block of code, we are going to decide how to order our list.

// ORDER BY: if one of the links was clicked..
if ($_GET['orderby']) {
   // make an aray of allowed names
   $allowed = array('id','name');
   // bit of cleaning...
   $order = mysql_real_escape_string($_GET['orderby']);
   // is it a valid column name? yes: use it. no: default to 'id'
   $order = (in_array($order, $allowed))? $order : "id";
// if no link clicked, default to 'id'
} else {
   $order = "id";
} // end else

02. When we display the list, We make the column titles into links. The user can click on that column title to reorder the list by that title. Like the "delete" links, we pass how the user wants to sort the list through the url, using the GET method. So the first thing we do is check to see if that variable exists. If it does...

04. Remember when we talked about preventing sql injection? Another method to restrict what is being sent to your database, is to specify what is allowed. This is called making a white list. It's just like in the real world when something is black listed or white listed. If it's in the array, it's allowed. Everything else is not. Making a white list is generally more secure than making a black list. So anyways, this is our array of allowed column names to sort. If the $_GET['orderby'] is not one of those columns, we assign a default column.

06. Since we are making a white list of columns, using mysql_real_escape_string is technically pointless. If someone tried to use escape quotes or other such things, it wouldn't be on our list anyways. I don't really have a logical reason for doing it, other than it somehow makes me feel like it's more secure.

08. Here is where we check to see if the value being passed to us is a valid column. We're going to assign something to $order no matter what, but depending on whether it's valid or not, we will either keep it as is or assign a default. We use a ternary operator to do this. The condition is evaluated inside the ( )'s. If it is true, we assign what's on the left of the : if it's false, we assign what's on the right.

11. The else statement is to assign a default column if a column title was not clicked. The default will usually be assigned from this else statement if the user is going to the page for the first time, or if the user clicked on something other than the column title (like a delete link or the form submit button).

Dealing With The Database: SELECT

This is where we actually get the info from the table for us to display to the user. There are no conditions wrapped around it, because we want the information to be displayed every page load. We have this query after the other queries, because we want the updated information in the table to be shown, if the user makes changes.

// SELECT: get the list of names from database
$sql = "SELECT id, name FROM info ORDER BY $order";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
/**** end deal with the database ****/

02. Just a simple select query to get our from the table, except we want to order the information by whatever $order is, be it a user selected order, or the default.

03. Here we execute this query just like all the other query executions so far. The main difference between this one and all the other ones, is that this time we really do want to save the results in a variable, so we can list them.

List Everything Out

In this section, our code is all about displaying the information to the user, as well as providing him a means to alter that information, with a form. This is the user's interface, where all of our previous code gets the data it needs to do what it's got to do. We are going to build an html table to tabulate the information in a half-assed decent format.

/**** list everything out ****/
// list columns
echo <<<LISTCOLS
<form action = '{$_SERVER['PHP_SELF']}' method = 'post'>
<table border = '1'>
   <tr>
      <td><a href = '{$_SERVER['PHP_SELF']}?orderby=id'>id</td>
      <td><a href = '{$_SERVER['PHP_SELF']}?orderby=name'>name</td>
      <td>delete</td>
   </tr>
LISTCOLS;

In this chunk of code, we start out with our form opening tag. The action targets the same script that's executing, using the post method. We then open our table up, and on the first row, we list the column names. We make them links for the user to click on to order the results by, as described in the "ORDER BY" section, earlier. The last html table column is labeled "delete" because that's the column our delete links will go, for each row.

List Everything Out

// loop through list of names 
while ($list = mysql_fetch_assoc($result)) {
echo <<<LISTINFO
   <tr>
      <td>{$list['id']}</td>
      <td><input type = 'text' name = 'cname[{$list['id']}]' value = '{$list['name']}'>
      <td><a href = '{$_SERVER['PHP_SELF']}?name={$list['name']}'>delete</a></td>
   </tr>
LISTINFO;
} // end while

This is the part of our script where we list out the current info in our database table. We start out with a while loop. Why? Okay, remember earlier, the last thing we did before listing stuff out, was select all the info from the database table, and stored it in $result? Okay, that information is called a result source. Think of $result as a magician's hat. PHP provides several functions for us to reach inside that hat and pull out what we need. What we pull out depends on which one of those functions we use.

Somewhere in that black hat is our list of information, and there's a rabbit somewhere in that hat and he has that information, and when we reach inside the hat, he gives us what we need, one row at a time. When he hands us that row, he picks up the next row and waits for us to reach in again.

The row he hands us is stored in an array. What kind of array depends on what function we use. We used mysql_fetch_assoc which returns an associative array. We used an associative array because the keys to the array are labeled the same as our database table columns, which makes it a lot easier for me to keep my head wrapped around my code when trying to type it and read it.

So basically, our array will look like this: ('id' => 1, 'name' => 'Josh') instead of this: (0 => 1, 1 => 'Josh). If numerical indexes float your boat, you can use mysql_fetch_row instead. If for some reason you want both returned, you can use mysql_fetch_array. Be warned though, mysql_fetch_array merges row and assoc together, giving you an array twice as long in the end. As if that weren't enough overkill, mysql_fetch_array can have an optional 2nd argument to return only one or the other (numerical or associative), making it function the exact same as _assoc or _row.

Since we don't know how many rows there are at any given time, we use a while loop. Basically the loop says this: while the rabbit still has rows to hand us, assign the current row he gives us to $list (making it an associative array of our data), and use $list to display the information to our user.

Inside the loop we echo out the id in the first column. We do not make this editable, because MySQL assigns that automatically. It's the row's fingerprint. We then echo out an input text field. We assign a array name to it so we can cycle through it as a list, back up in that UPDATE code block. We assign the row's id number as the array position, so that we can pass the id value with the form, as described in that same previous code block. The value is the name in the database row, so that it shows as the "default" in the text field. The user can edit it, and the UPDATE code block will update it.

Last of all, we make a "delete" link in the last column of our html table. The link will pass the name so that it can be retrieved with the GET method up in the "DELETE" code block.

List Everything Out

// list input box for adding new entry
echo <<<NEWENTRY
   <tr>
      <td bgcolor = 'gray'></td>
      <td><input type = 'text' name = 'name'></td>
      <td bgcolor = 'gray'></td>
   </tr><tr>
      <td></td>
      <td align = 'center'><input type = 'submit' value = 'submit'></td>
      <td></td>
   </tr>
</table>
</form>	 
NEWENTRY;
/**** end list everything out ****/

?>

This last piece of code we simply add an empty textbox for the user to add a name, as well as a submit button. The value of the text field is used by the INSERT code block. The submit button is linked to any updates/inserts made. Close out the table, close out the form, and we're done!

Summary

Hopefully you have gained a better understanding about how to mess around with your database through php. If not, then I'm sorry, I tried! Please post any questions you may have in the comments section or on our help forums, and I or someone else will try to help out.

Until then, Happy Coding!

Crayon Violent