Washington Apple Pi

A Community of Apple iPad, iPhone and Mac Users

Connecting to a MySQL Database using PHP

By Sheri German

Washington Apple Pi Journal, reprint information

For those of you who have been following along with the PHP/MySQL tutorials in the past couple of Journals, this article will provide the next step in learning to create database driven Web sites. We will build upon the knowledge acquired by creating the "rescued pets" database in phpMyAdmin. Now we will actually build HTML pages with embedded PHP scripts that will allow us to connect to the MySQL server and our database, as well as insert, update, and delete data. We're getting very close to our ultimate goal of creating a Content Management System, a true interactive database driven Web application.

This tutorial does assume you know a little HTML because you will have to work with raw code. Because we will be doing a lot of hand scripting, I have put the source code up on my Web site at http://www.swanilda.com/phpTutorial.html. It is very easy to forget a closing quote, semicolon, or other small detail, and believe me, the devil is in the details when one gets into scripting. I highly recommend BBEdit Pro 7.01 for creating PHP pages, but BBEdit Lite, or even TextEdit will work just fine.

Connecting to the server and a particular database

The first thing we have to do is connect to the MySQL server, then select the database we want to use. This is accomplished by the use of functions, which are like pre-built mini programs that are a part of the PHP scripting language. We are going to use three functions: mysql_connect, mysql_select_db, and mysql_close.

Before we start our "mini programs" we're going to have to add the PHP delimiters that declare that we are presenting a block of PHP code. <?php begins the script, and ?> ends it. We can go back and forth between PHP and HTML as long as we always remember to surround the PHP scripts with these delimiters. In the case of the script below, we're going to place it between the opening body tag and the closing body tag.

Next we are going to set up a variable, which is like an imaginary box in which to store specific values. PHP variables start with a dollar sign ($), and can be numeric or strings (of characters.) See our first variable? When we connect to the MySQL server, a positive integer is returned which labels the connection. $connectID is the name I gave this variable, but I could have named it $linkID or $labelID or anything meaningful to me.

The mysql_connect function takes three "arguments." The first is the name or IP address of the host computer. If you are using your own Web server, you can either type in "localhost" or "127.0.0.1." The second argument is the user name. If you set up MySQL, you may be using it under the Super User or "root." Finally, we need the password of root in the MySQL server. When you installed MySQL, you should have set up a password as a security precaution.

The second function, the mysql_select_db will allow us to choose which database we would like to work with. In this case, we could use the animal_rescue database we created in a previous article. We're also going to designate that connection we are using.

You may notice what we call a conditional statement. If and else can tell the script to perform one task if a certain condition is true, but another if it is not. So all we're doing here is telling the browser to present on the screen one statement if we successfully connect to the database, but another if we do not. != is the operator that tells us something is not equal to something else.

Finally, we are going to explicitly close the connection to the database with the mysql_close function, though it is not really necessary to do so since the connection automatically closes after the script is run. (Note: There is a "persistent connection" you can use instead that will not automatically close the connection after the script is run.) Again, this function takes the argument that tells us which connection we are closing.

So there's your first script. If you have this database in the MySQL server, try it out. Otherwise, enter the name for some other database you have.

<body>
<?php 
$connectID = mysql_connect("localhost", "root", "mypasswordhere");
if (mysql_select_db("animal_rescue", $connectID) !=FALSE)
{
	print "Animal Rescue was successfully selected.";
}
else
{
	print "The connection to animal_rescue failed.";
}
mysql_close($connectID); 
         
?>
</body>

Display the selected contents of the database

Let's do something a little more useful, like actually displaying some data from our selected database. After establishing the connection to, and then selecting the database as before, we'll issue a query using the SQL language. Our new function is called mysql_query. If you recall the fields of the adoption_list table that we created in the animal_rescue database, you can decide which ones you want to display in the browser. We probably don't want to display the ID, and for now, let's skip the image field. The syntax for selecting the fields we want from a particular table is actually much like natural language. (Note: If you just wanted to display every field of the database, you could use the wildcard * (asterisk) which stands for all.) Notice that connection identifier showing up again though? And of course, we put all of this into a variable I called $resultID so we can use it "in shorthand" later in the script.

It is time to actually "echo" or print the information to the browser. All we have going on here is HTML table code. You'll notice that each of the table cell headers contains the name of a different field we want to display. To get each unique record to display, we're going to use the "while" programming loop and another mysql function, mysql_fetch_row. All this means is that while there is still another new record in the table, that a new row gets generated to display it in our HTML table. When there are no more new records, the loop ends. See if you can follow along how the different variables are used to create shortcuts in the code.

<?php 
$connectID=mysql_connect("localhost", "root", "yourpasswordhere");
mysql_select_db("animal_rescue", $connectID);
$resultID = mysql_query("SELECT species, breed, name, age, personality FROM
adoption_list", $connectID);
print "<table border=1><tr><th>Species</th><th>Breed</th><th>Name</th>
<th>Age</th><th>Personality</th></tr>";
while ($row=mysql_fetch_row($resultID))
{
	print "<tr>";
	foreach ($row as $field)
	{
		print "<td>$field</td>";
	}
	print "</tr>";
}
print "</table>";
         
mysql_close($connectID); 
         
?>

Database display

Insert a new record

Very nice, but what if we need to add another pet into our database? Sadly, too many abandoned animals are turned into rescue centers every day, and inserting new records will be a much-repeated task.

Our script starts out pretty much the same. We connect to the server, identify our connection, select our database, and submit an SQL query. This time we will use the INSERT statement, which requires that first we identify the fields of our table, then we list the values that will go into each of the fields.

We'll also add feedback to let the user know that the insertion was successful. Here you see a new operator, the double equal sign. If you can remember that one equal sign is an assignment operator (we are assigning a value, say, to a variable), you will understand why we need to distinguish that from a true comparison. Two equal signs tell us that one thing is equal to another.

After we have given our feedback, we will run the SELECT query again so that we can display our database and see that new pet for ourselves.

<?php 
$connectID = mysql_connect("127.0.0.1", "root", "dancer");
mysql_select_db("animal_rescue", $connectID);
$result = mysql_query("INSERT INTO adoption_list (ID, species, breed, name, age,
personality, image) VALUES ('', 'Cat','Himalayan', 'Sassy', 7, 'Devoted to her one
person', 'sassy.jpg')", $connectID);
         
if ($result == TRUE)
{
	print "The record was added successfully.<p>";
}
else
{
	print "The record could not be added.<p>";
}
$resultID = mysql_query("SELECT species, breed, name, age, personality FROM
adoption_list", $connectID);
         
print "<table border=1><tr><th>Species</th><th>Breed</th><th>Name</th>
<th>Age</th><th>Personality</th></tr>";
while ($row=mysql_fetch_row($resultID))
{
	print "<tr>";
	foreach ($row as $field)
	{
		print "<td>$field</td>";
	}
	print "</tr>";
}
print "</table>";
         
mysql_close($connectID);
         
?>

Add record

Updating Records

Being that we're humans, and not perfect like these beautiful pets, we'll make mistakes while adding our records. Fortunately, there is an SQL statement to cover that, UPDATE, which tells the server which table to update. Then we add SET to indicate which field needs updating, and what value we're inserting instead. We also need to give the ID number of the record we want to alter, and we do that by using WHERE. (For now, look in your database in phpMyAdmin to identify the ID of the record you would like to change. In later tutorials, when we set up interactive forms, we'll talk about passing URL parameters.)

<?php 
$connectID = mysql_connect("127.0.0.1", "root", "dancer");
mysql_select_db("animal_rescue", $connectID);
$result = mysql_query("UPDATE adoption_list SET name='Allegra' WHERE ID=7",
$connectID);
         
if ($result == TRUE)
{
	print "The record was updated successfully.<p>";
}
else
{
	print "The record could not be updated.<p>";
}
$resultID = mysql_query("SELECT species, breed, name, age, personality FROM
adoption_list", $connectID);
         
print "<table border=1><tr><th>Species</th><th>Breed</th><th>Name</th>
<th>Age</th><th>Personality</th></tr>";
while ($row=mysql_fetch_row($resultID))
{
	print "<tr>";
	foreach ($row as $field)
	{
		print "<td>$field</td>";
	}
	print "</tr>";
}
print "</table>";
         
mysql_close($connectID);
         
?>

Deleting Records

It is a happy day when a pet finds a family to call its own. We then need to delete the animal's record from the database. Now we will use the DELETE FROM statement, using the table and record ID as your arguments. Everything else is pretty much the same and it should start to look familiar, if not comfortable, to you. Be sure to identify the ID of a record you would like to delete. My record number was 7, but you may have something different you would like to delete from your database.

<?php 
$connectID = mysql_connect("127.0.0.1", "root", "yourpasswordherer");
mysql_select_db("animal_rescue", $connectID);
$result = mysql_query("DELETE FROM adoption_list WHERE ID=7", $connectID);
         
if ($result == TRUE)
{
	print "The record was deleted successfully.<p>";
}
else
{
	print "The record could not be deleted.<p>";
}
$resultID = mysql_query("SELECT species, breed, name, age, personality FROM
adoption_list", $connectID);
         
print "<table border=1><tr><th>Species</th><th>Breed</th><th>Name</th>
<th>Age</th><th>Personality</th></tr>";
while ($row=mysql_fetch_row($resultID))
{
	print "<tr>";
	foreach ($row as $field)
	{
		print "<td>$field</td>";
	}
	print "</tr>";
}
print "</table>";
         
mysql_close($connectID);
         
?>

Delete record

Are you starting to see that with a few functions, some PHP scripts, and knowledge of some basic SQL statements you can create a powerful interface to your database in a browser? This will be the key element in creating pages with which users can interact. The next tutorial will build upon this knowledge as we pour our information into forms that give true interactivity to our Pet Rescue Content Management System.

References:

BBEdit Lite 6.1: Barebones Software, free.; BBEdit 7.01: Barebones Software, $179; http://www.barebones.com/products/bbedit/
: Tutorial scripts:; http://www.swanilda.com/phpTutorial.html; "How to Set Up TextEdit as an HTML or Plain Text Editor"; http://docs.info.apple.com/article.html?artnum=106212