The PHP Interface To MySQL [top]
So now that we know how variables that are sent by the web browser can be manipulated by PHP, and how we can set up our database to store and manipulate these values, we need to connect PHP to the database. There are several ways to interface PHP to MySQL - the approach that we'll take is to use the built-in functions that PHP provides for interfacing with the MySQL database. Should you ever need to connect to a database other than MySQL using PHP, you can use a package from PEAR (i.e. the PHP Extension and Application Repository) called DB.
Practical Considerations on Stage [top]
In order to connect to your MySQL database via PHP, you need to use your MySQL login and password that was assigned to you. Because this is sensitive information, we'll create an include file that will live in our script directory and that can access this information without making it public to other users. I named mine "info.php" and it looks something like this:
<?php /* info.php */ # Define the connection parameters $mySqlHostname = "localhost"; # On Stage, your scripts run on the same machine as MySQL $mySqlDatabase = "net_id"; # On Stage, the name of your database is your Net ID $mySqlUsername = "net_id"; # On stage, your MySQL username is your Net ID $mySqlPassword = "your MySQL password"; # Your MySQL password, not your Stage passwordIMPORTANT: Notice how there was no trailing "?>" at the end of this file. For this type of file, you will fare better in the long run if you keep the end of the file in PHP. There is no rule that a PHP file needs a trailing ?>. This is only useful if you are exiting PHP within the script to write HTML.
After creating this file, you can reference this information at any time by issuing the statement:
# Get our DB info require "info.php";In general, anytime you have information to be accessed by more then one file, you should put that in a separate file and then use the "require" keyword to obtain that information. This way, if that information should ever change, you only need to edit one file, instead of every file that uses that information.
For security purposes, please set the file permissions of this file to be 600, which means it can be read and written by your account, but not by anyone else's. This can be done using your SFTP client, or by using your SSH interface and issuing the UNIX command inside the directory where this file lives:
chmod 600 info.php
Common PHP-MySQL Methods [top]
You can achieve a lot with only a few commands using the native MySQL methods available in the base PHP installation. The main thing to keep in mind is that you need to be able to connect to your database, you need to be able to select the specific database of interest, you need to be able to make SQL queries, and you need to be able to disconnect. You connect to the DB once and only once at the beginning of your script, and you disconnect once and only once at the end of your script.
mysql_connect
Establishes a database connection, or session, to the requested MySQL server. It returns a MySQL link identifier if the connection succeeds, which can be repeatedly used to communicate with the specific MySQL server. Use the mysql_close() method to terminate the database connection that this method created.
$connection = mysql_connect($mySqlHostname, $mySqlUsername, $mySqlPassword);
mysql_select_db
Select a specific database to use for this connection
$db_selected = mysql_select_db($mySqlDatabase, $connection);In the real world, the connection goes something like this, requiring the info.php file that we created to hold our MySQL login info. Note that if the result of any of these mysql_* functions is not valid, we can use the functions mysql_errno() and mysql_error() to get some debugging info:
# Get our DB info
require "info.php";
#########################################################
# Connect to the database.
#########################################################
$connection = mysql_connect($mySqlHostname, $mySqlUsername, $mySqlPassword);
if (!$connection)
die("Error " . mysql_errno() . " : " . mysql_error());
# Select the DB
$db_selected = mysql_select_db($mySqlDatabase, $connection);
if (!$db_selected)
die("Error " . mysql_errno() . " : " . mysql_error());
mysql_close
Closes the current connection to the database. The mysql_close() method is used before exiting the program if a current connection exists.
######################################################### # Disconnect from the database. ######################################################### # Always the last thing you do before exiting your script mysql_close($connection);
mysql_query
The mysql_query() method is used to send a SQL query to the database. It returns a MySQL resource containing various aspects of the results of the SQL statement:
# Run the query on the database through the connection
$result = mysql_query("DELETE FROM table WHERE id=5",$connection);
if (!$result)
die("Error " . mysql_errno() . " : " . mysql_error());
The above code is all we need for INSERT, UPDATE, and DELETE queries since we didn't specifically ask for certain data like we do with a SELECT query. Here's some real world examples of the mysql_query() method using INSERT, UPDATE, and DELETE:
#########################################################
# Inserting
#########################################################
$myName = "Morgan";
$myName = str_replace("'", "''", $myName);
$SqlStatement = "INSERT INTO class05_table1 (name) VALUES ('$myName') ";
$result = mysql_query($SqlStatement,$connection);
#########################################################
# Updating
#########################################################
$myName = "Nancy";
$myName = str_replace("'", "''", $myName);
$SqlStatement = "UPDATE class05_table1 SET name='$myName' WHERE id=1 ";
$result = mysql_query($SqlStatement,$connection);
#########################################################
# Deleting
#########################################################
$myName = "Morgan";
$myName = str_replace("'", "''", $myName);
$SqlStatement = "DELETE FROM class05_table1 WHERE name='$myName' ";
$result = mysql_query($SqlStatement,$connection);
Note that there is very little difference in how you make INSERT, UPDATE, and DELETE statements using the mysql_query() method. It's all in how you construct the string that holds the actual SQL statement. In the above examples, we have put this statement into the variable $SqlStatement.
After you perform a query, you can find out how many records were affected using the aptly named mysql_affected_rows() method :
$result = mysql_query("DELETE FROM table WHERE id=5",$connection);
if (!$result)
die("Error " . mysql_errno() . " : " . mysql_error());
print "I deleted " . mysql_affected_rows() . " record(s) from the table <br>";
mysql_fetch_array
OK, so if you've noticed, I've neglected using the mysql_query() method with SELECT statements until now. Because SELECT statements return data of interest, we need a way to obtain that data and work with it. Thus, we have the mysql_fetch_array() method. When we call it after issuing a SELECT statement, it contains all the values for the fields requested, returning one row at a time. We can use the while loop structure in PHP to obtain all the rows from this query.
#########################################################
# SELECT using mysql_fetch_array()
#########################################################
# $SqlStatement = "SELECT * FROM class05_table1"; # We can use wildcards to get all fields
$SqlStatement = "SELECT id, name FROM class05_table1"; # Or explicitly label fields
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result)
die("Error " . mysql_errno() . " : " . mysql_error());
while ($row = mysql_fetch_array($result,MYSQL_NUM))
{ $current_id = $row[0];
$current_name = $row[1];
}
In this example, we gave mysql_fetch_array() a parameter called MYSQL_NUM which describes how we want to receive the data. For this setting, the columns in our request match up with the indexes in our $row array in the order that we requested them. Thus, "id" is at index 0 in our array, "name" is at index 1. If our SELECT statement had been:
$SqlStatement = "SELECT name, id FROM class05_table1";then "name" would be at index 0 in our array, and "id" would be at index 1.
Another common parameter to give mysql_fetch_array() is MYSQL_ASSOC, which allows us to access the values by field name instead of a numeric index:
#########################################################
# SELECT using mysql_fetch_array() using MYSQL_ASSOC
#########################################################
$SqlStatement = "SELECT id, name FROM class05_table1";
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result)
die("Error " . mysql_errno() . " : " . mysql_error());
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{ $current_id = $row["id"];
$current_name = $row["name"];
}
If you don't provide a 2nd parameter, mysql_fetch_array() will return the data in both forms
#########################################################
# SELECT using mysql_fetch_array() using MYSQL_BOTH (default)
#########################################################
$SqlStatement = "SELECT id, name FROM class05_table1";
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result)
die("Error " . mysql_errno() . " : " . mysql_error());
# Below is same as:
# while ($row = mysql_fetch_array($result,MYSQL_BOTH))
while ($row = mysql_fetch_array($result))
{ $current_id = $row[0];
$current_name = $row["name"];
}
mysql_fetch_assoc
This is the equivalent of mysql_fetch_array($result,MYSQL_ASSOC). My preference is to use this in combination with a while loop to put all the selected data into a PHP array of associative arrays - that is, take each row of data from sql and add it to an array that you can work on while your PHP script is still executing. This way, you free up the resources of the database sooner, and you aren't bound to working with the data only as it comes out of DB. If it's all in a PHP array, you can slice it, dice it, and format it however you like.
#########################################################
# SELECT using mysql_fetch_assoc() and a local array
#########################################################
$SqlStatement = "SELECT id, name FROM class05_table1";
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
while(($rows[] = mysql_fetch_assoc($result)) || array_pop($rows));
# Loop through the data
foreach($rows as $row):
print "{$row['id']} {$row['name']} <br>\n";
endforeach;
In this example, the conditions in my while loop are assign the current row to my array $rows. If it's true, this row is valid data, it was just added to my array, so it goes to the next row. If it's false, then this row is bad, so it uses array_pop() to remove it from the end of my array, thus leaving me with only good data in my $rows array.
One other useful function is mysql_num_rows() which returns the number of rows in a result from a SELECT statement:
$SqlStatement = "SELECT id, name FROM class05_table1";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
print "There are " . mysql_num_rows() . " record(s) in our table <br>";
So let's see these methods in action. We'll look at some specific examples where we being to see how calls to the database can be interspersed with HTML and PHP scripting:
The Web Browser To PHP To DB and Back [top]
So now it's the moment we've all been waiting for. We can submit HTML forms and have PHP parse the values, manipulate the database, and then show the results back to the user. Here's an example where we can conduct a poll
of everyone's favorite sites.
Here's an expanded example that allows new site submission:
Here's an example that allows deletion of one site at a time:
Here's an example that uses checkboxes for multiple deletion at one time:
One to Many Relationships (in Practice) [top]
Let's extend our favorite sites example to include the ability to assign a category (e.g. Sports, Art, Music, etc) when we enter a new site. If a single category can be assigned, then this creates a one-to-many relationship between sites and categories since a site can only have one category, but a category can be assigned to many sites. Thus we now need a second table containing the category info with a primary key (let's assume id and name), and we need to add a category_id to our sites table.
For the INSERT of a new site, when we create our input form, we can grab the existing category names from our category table and use them to populate a select widget:
echo <<<END
<select name="$catName">
<option value="">--- Select ---
END;
# Write the available categories from the DB
$SqlStatement = "SELECT id, name FROM class05_category WHERE id>1 ORDER BY name";
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
while(($rows[] = mysql_fetch_assoc($result)) || array_pop($rows));
foreach($rows as $row):
$id = $row['id'];
$name = $row['name'];
$extraText = "";
if ($id==$catValue) $extraText = " selected";
echo <<<END
<option value="$id" $extraText>$name
END;
endforeach;
# Unset $rows in case we use it again for another result set later in the script
unset($rows);
echo <<<END
</select>
END;
The value that gets passed in the HTML form for the category is the actual DB ID of that category, and that value is put directly into the site table for category ID:
$catName = "category"; # The name of select widget that has the relevant category
$catValue = $_POST[$catName]; # The value of category widget
# If no value for category, assume it is ID 1, meaning undefined
if (empty($catValue))
$catValue = 1;
$SqlStatement = "INSERT INTO class05_table2 (title,url,description,category_id)
VALUES ('$titleDB','$urlDB','$descDB',$catValue) ";
When we need to obtain it from our DB, we simply use a straight inner join of the two tables to get both the site info from the site table, and the category name from the category table:
$SqlStatement = "SELECT s.id, s.title, s.url, s.description, s.votes, c.id, c.name FROM class05_table2 s, class05_category c WHERE s.category_id=c.id ORDER BY s.votes desc, s.title";
Many to Many Relationships (in Practice) [top]
Let's again extend our favorite sites example to include the ability to add tags (e.g. itp, cool, etc) when we enter a new site. If multiple tags can be assigned, then this creates a many-to-many relationship between sites and tags, since a site can have many tags, and a tag can be assigned to many sites. Thus, we now need a second table containing the tags with a primary key (let's assume id and name), and we need to add a third (linking) table between tags and sites.
For the INSERT of a new site, when we create our input form, one easy way to allow for tag entry is to simply add a text field and have the user enter tags comma-separated, e.g. "itp, cool". Then, when we do the INSERT of the site, we split this value by comma to obtain an array of applicable tags and then processing goes as follows:
# Create the SQL query
$SqlStatement = "INSERT INTO class05_table2 (title,url,description)
VALUES ('$titleDB','$urlDB','$descDB') ";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
# Now get the ID of this site and use it to populate any tags
if (!empty($textTagValue))
{
# mysql_insert_id returns last ID created
$site_id = mysql_insert_id($connection);
# Get our individual tags
$tag_array = explode(",",$textTagValue);
foreach ($tag_array as $tag)
{
$tag = trim($tag);
if (!empty($tag))
{
# Does this tag exist? If so, get it's ID
$tag_id = -1;
$tagDB = str_replace("'", "''", $tag);
$SqlStatement = "SELECT id FROM class05_tag WHERE name='$tagDB'";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
if ($row = mysql_fetch_array($result,MYSQL_NUM))
{ $tag_id = $row[0];
}
else
{
# We need to insert it and get its ID
$SqlStatement = "INSERT INTO class05_tag (name) VALUES ('$tagDB')";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
# mysql_insert_id returns last ID created
$tag_id = mysql_insert_id($connection);
}
if ($site_id>0 && $tag_id>0)
{
$SqlStatement = "INSERT INTO class05_table2_x_tag (site_id,tag_id)
VALUES ($site_id,$tag_id)";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
}
}
}
}
When we need to obtain our sites with tag info from our DB, our SQL becomes a bit more complicated because we have the task of getting all applicable sites, even if they don't have tags. Our usual INNER JOIN, a la:
SELECT s.id, s.title, t.name FROM class05_table2 s, class05_tag t, class05_table2_x_tag x WHERE s.id = x.site_id AND t.id = x.tag_id ORDER BY s.votes desc, s.titlewill not work because this will only return sites that have been tagged and not any of the ones that haven't. For this, we use what's called an OUTER JOIN, specifically a LEFT OUTER JOIN, which will return all relevant rows on the left-side of the join even if it doesn't match an entry on the right side. For a many-to-many relationship, a LEFT OUTER JOIN looks like the following, noting that our sites table is on the left side of the join, since we want to get all applicable rows:
SELECT s.id, s.title, t.name FROM class05_table2 s LEFT OUTER JOIN class05_table2_x_tag x ON s.id = x.site_id LEFT OUTER JOIN class05_tag t ON t.id = x.tag_id WHERE (conditions) ORDER BY (conditions)Unfortunately, the PHP to gather these values and show them to the user is even more convoluted because if a site has multiple tags, the rows might come out like:
1 Yahoo portal 1 Yahoo general 2 CNN news 3 ITP cool 3 ITP red 3 ITP nyu etc ...This means that while we are looping we need to selectively output a site if we've collected all its associated tags. We know when we've finished collecting a site's tags when the site ID of the new row is not equal to the old one. In practice, the PHP goes something like this:
# Retrieve all rows from the table and put in normal array.
$SqlStatement = "SELECT s.id as site_id, s.title, s.url, s.description, s.votes,
t.id as tag_id, t.name
FROM class05_table2 s
LEFT OUTER JOIN class05_table2_x_tag x
ON s.id = x.site_id
LEFT OUTER JOIN class05_tag t
ON t.id = x.tag_id
ORDER BY s.votes desc, s.title";
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
while(($rows[] = mysql_fetch_assoc($result)) || array_pop($rows));
$row_prev = array();
$tag_text = "";
foreach($rows as $row):
if ($row_prev['site_id']>0 && $row_prev['site_id']!=$row['site_id'])
{
# Found new site so write the previous site if we recorded it
echo <<<END
<tr bgcolor="$table_row_color">
<td align="left"><a target="newSite" href="{$row_prev['url']}">{$row_prev['title']}</a></td>
<td align="left">{$row_prev['url']}</td>
<td align="left">{$row_prev['description']}</td>
<td align="left">$tag_text</td>
<td align="right">{$row_prev['votes']}</td>
</tr>
END;
# Clear the tag text
$tag_text = "";
}
# Now record the tag text for this entry if exists
if ($row['tag_id']>0)
{ if ($tag_text!="") $tag_text .= ", ";
$tag_text .= $row['name'];
}
# Record the current row for the next go round
$row_prev = $row;
endforeach;
# Write the last site if we have valid site ID
if ($row_prev['site_id']>0)
{
echo <<<END
<tr bgcolor="$table_row_color">
<td align="left"><a target="newSite" href="{$row_prev['url']}">{$row_prev['title']}</a></td>
<td align="left">{$row_prev['url']}</td>
<td align="left">{$row_prev['description']}</td>
<td align="left">$tag_text</td>
<td align="right">{$row_prev['votes']}</td>
</tr>
END;
}
echo <<<END
</table><p>
END;
Related Resources [top]