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:
- How To Request A CGI Account
- How To Request A MySQL Database
- Create A Web Page
- Upload A Web Page
- Using PHP
- 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 <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:
|
