” This very technique will come into play when it comes to creating the page for inserting information into the database.
There is one page in our application, called sign.php, that has an HTML form.
The action of the form in this page is create_entry.php. Here’s the page in all its glory:
<h2>Sign my Guest Book!!!</h2>
<form method=post action=”create_entry.php”>
<b>Name:</b>
<input type=text size=40 name=name>
<br>
<b>Location:</b>
<input type=text size=40 name=location>
<br>
3537-4 FM.f.qc 12/15/00 15:31 Page xxxvi
xxxvi
Introduction
<b>Email:</b>
<input type=text size=40 name=email>
<br>
<b>Home Page URL:</b>
<input type=text size=40 name=url>
<br>
<b>Comments:</b>
<textarea name=comments cols=40 rows=4 wrap=virtual></textarea>
<br>
<input type=submit name=submit value=”Sign!”>
<input type=reset name=reset value=”Start Over”>
</form>
When the user fills out this form and submits it, the information will be sent to create_entry.php. The first thing to do on this page is to check that the form has been submitted. If it has, take the values entered into the form and use them to create a query that you will send to MySQL. Don’t worry about the specifics of the query just yet. Just know that it will insert a row into the database table you created earlier.
<?php
include(“dbconnect.php”);
if ($submit == “Sign!”)
{
$query = “insert into guestbook
(name,location,email,url,comments) values
(‘$name’, ‘$location’, ‘$email’, ‘$url’, ‘$comments’)”
;
mysql_query($query) or
die (mysql_error());
?>
<h2>Thanks!!</h2>
<h2><a href=”view.php”>View My Guest Book!!!</a></h2>
<?php
}
else
{
include(“sign.php”);
}
?>
If the form, which is in sign.php, hasn’t been submitted, it is included and therefore will show the same form. You may notice that this page is submitted to itself.
3537-4 FM.f.qc 12/15/00 15:31 Page xxxvii
Introduction
xxxvii
The first time the create_entry.php page is called, the form in sign.php will be displayed. The next time, though, the data will be inserted into the database.
Figures I-2 and I-3 show the pages that this script will create.
Figure I-2: create_entry.php the first time through
Figure I-3: create_entry.php after submission
3537-4 FM.f.qc 12/15/00 15:31 Page xxxviii
xxxviii
Introduction
VIEWING INFORMATION IN THE DATABASE
This shouldn’t be too tough. You already know that the file will need to include dbconnect.php. Other than that, we’ve already mentioned that databases store information in tables. Each row of the table will contain information on a specific person who signed the guestbook, so to view all of the information, the page will need to retrieve and print out every row of data. Here’s the script that will do it (you should notice that it’s pretty sparse):
<?php include(“dbconnect.php”); ?>
<h2>View My Guest Book!!</h2>
<?php
$result = mysql_query(“select * from guestbook”) or
die (mysql_error());
while ($row = mysql_fetch_array($result))
{
echo “<b>Name:</b>”;
echo $row[“name”];
echo “<br>\n”;
echo “<b>Location:</b>”;
echo $row[“location”];
echo “<br>\n”;
echo “<b>Email:</b>”;
echo $row[“email”];
echo “<br>\n”;
echo “<b>URL:</b>”;
echo $row[“url”];
echo “<br>\n”;
echo “<b>Comments:</b>”;
echo $row[“comments”];
echo “<br>\n”;
echo “<br>\n”;
echo “<br>\n”;
}
mysql_free_result($result);
?>
<h2><a href=”sign.php”>Sign My Guest Book!!</a></h2>
3537-4 FM.f.qc 12/15/00 15:31 Page xxxix
Introduction
xxxix
The query asks MySQL for every row in the database. Then the script enters a loop. Each row in the database is loaded into the variable $row, one row at a time.
Rows will continue to be accessed until none is left. At that time, the script will drop out of the while loop.
As it works through the loop, each column in that row is displayed. For example print $row[“email”]
will print out the e-mail column for the row being accessed.
When run, this simple script will print out every row in the database. Figure I-4
shows what the page will look like.
Figure I-4: view.php
And that about does it for our first application.
WHY YOU SHOULD NOT USE THIS APPLICATION
If you want to load this up on your own server to see if it works, fine; be our guest.
But we wouldn’t put it anywhere where the general public could get to it. No, if you were to do that there would be problems. For instance, you could end up with Figure I-5 on your view.php page. Not good at all!
3537-4 FM.f.qc 12/15/00 15:31 Page xl
xl
Introduction
Figure I-5: Problematic guestbook entry
If you want a guestbook, you should use the super-hyper-coded application made exclusively for the readers of this book, which you will find in Chapter 8.
We call this application Guestbook2k. But before we get there, it’s time for some education.
Now get reading.
3537-4 ch01.f.qc 12/15/00 15:20 Page 3
Chapter 1
Database Design with
MySQL
IN THIS CHAPTER
◆ Identifying the problems that led to the creation of the relational database
◆ Learning the normalization process