Building Web Sites All-in-One For Dummies® (87 page)

The second and third field names are the same names as those in the form you created. The value you enter is the same value you specified for Max Chars when you created the form. The first field generates a number every time a record is added to the database. This number is used to locate information in the database. The ID is incremented by a value of 1 every time a record is added to the database.

10.
Click Save.

PHPMyAdmin creates the table and fields. Next, you create the PHP code that transfers the information from the form to the database.

Creating the PHP Code

After you have the form and the database ready to go, create the PHP code that will populate the database with e-mail addresses. You create variables for each form field and then create a connection to the database. Your code also puts the information in the database. Just follow these steps:

1.
Create a new document in Dreamweaver.

Click Blank Page and choose PHP from the Page Type column.

2.
Switch to Code view.

3.
Create a new line after the

tag and then enter the following code:

$Name=$_POST[‘Name'];

$EMail=$_POST[‘EMail'];

$connection=mysql_connect (“localhost”, “myusername”, “mypassword”) or die (‘I cannot connect to the database.');

mysql_select_db (“mydatabase”,$connection) or die( “Unable to select database”);

$query = “INSERT INTO `mytable` (`ID`, `Name`, `EMail`) VALUES (‘','$Name','$EMail')”;

mysql_query($query);

mysql_close();

?>

You'll have to change the code to suit your Web server and the database information, but the first three lines of code will remain unchanged. Here's what's happening in this code:

• The first line signifies that PHP code follows.

• The next lines of code create two variables,
$Name
and
$EMail
, which are set equal to the names of the fields from the form you created where users enter their names and e-mail addresses.

• The line of code that begins with
$connection
creates a variable with the information needed to connect to the database. Most Web hosting services use
localhost
as the location for the database; however, some Web servers keep their MySQL databases on a separate server, in which case you'll need to enter the URL for the database server. Check with your Web hosting service's technical support staff for more information.

• The line of code that begins with
mysql_select_db
selects the database using the information from the
$connection
variable. Replace
mydatabase
with the name of your database.

• The line that begins with
$query
creates a variable with the instructions that insert the data into the database. Replace
mytable
with the name of the table into which you're inserting the form data. You might recognize the information
(‘ID', ‘Name', ‘EMail')
. These are the fields in your database into which the information is being inserted. The information being inserted is specified by the word
VALUES
. The values being inserted are
(‘','$Name','$EMail')
. The single quotes are a placeholder for the ID field in the database. When the information is inserted in the database, MySQL assigns a number to the record. The ID field is the primary field in the database and is set to
auto increment,
which means that sequential numbers are assigned to the records entered: 1, 2, 3, 4, and so on.

• The line of code that begins with
mysql_query
tells MySQL that a query operation is being requested. The query performed gets instructions from the variable
$query
.

• The line
mysql_close();
closes the connection to the database. And if you've read Chapter 3 of this minibook, you know that
?>
ends the PHP code.

4.
Save the document.

Save the document as
addrecord.php
, the same filename specified in the action of the form you created.

When you upload the HTML page with the form and the PHP file to your server, site visitors can enter their names and e-mail addresses into the form. When they click the Submit button — or whatever clever name you gave the button — their information is added to the database.

You need to add additional code to the head of the
addrecord.php
document. Otherwise, the site visitor will click the Submit button and get stuck on the
addrecord.php
page. The code you need to add is

After adding this code to the
addrecord.php
page, you need to create a new HTML document called
thanks.htm
, which is uploaded to the Web site. The browser refreshes with this page after the code executes. Your Thank You page should contain site navigation and a message to site visitors thanking them for adding their information to the database.

Retrieving Information from a Database

Okay, after you have a database that accepts information from a Web page, how do you access it? You can choose from many commands to search a database. You can set up a form where the user enters the information he's searching for and then create the code that searches a particular field of the database and returns information that is identical to or similar to the user's request. This is a
query.

To show you everything you can do with a database requires several chapters. To show you everything you can do with PHP and a MySQL database requires an entire book. Neither option is available, and our project editor was chomping at the bit for this chapter. To find out everything you ever wanted to know about MySQL and PHP, consider
PHP and MySQL For Dummies,
3rd edition,
PHP & MySQL Everyday Apps For Dummies,
both by Janet Valade (both by Wiley), or
PHP & MySQL Web Development All-in-One Desk Reference For Dummies,
by Janet Valade, Tricia Ballad, and Bill Ballad (Wiley).

Now that the disclaimer's out of the way, we want to show you how to retrieve the data from the simple mailing list we show you how to create in the previous section.

If you didn't read the previous section, do so now.

To retrieve the data from the mailing list database, follow these steps:

1.
Create a new document in Dreamweaver.

Click Blank Page and then choose PHP from the Page Category column.

2.
Switch to Code view.

3.
Create a new line after the

tag and then enter the following code:

$connection=mysql_connect (“localhost”, “myusername”, “mypassword”) or die (‘I cannot connect to the database.');

mysql_select_db (“mail_list”,$connection) or die( “Unable to select database”);

$query=”SELECT * FROM users ORDER by name”;

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;

while ($i < $num) {

$name=mysql_result($result,$i,”Name”);

$email=mysql_result($result,$i,”EMail”);

?>

$i++;

}

?>

4.
Save the file and upload it to your server.

When you load the PHP file in a browser, it plucks all the data from the database. Figure 4-5 shows a hypothetical mailing list displayed using the code in the previous steps.

You're probably thinking, “Yikes, that's a lot of code!” We don't leave you in a lurch, though. Read on to find out what the code means.

Listing 4-1 shows the code for the table that displays the titles for the information that will be retrieved. The titles use the

tag (table header), which boldfaces the text. The code also sizes the headers and aligns the text to the left.

Listing 4-1: The Table Titles

In Listing 4-2, the first line of code creates a new table and the second line begins the PHP script.

Listing 4-2: Connecting to the Database

$connection=mysql_connect (“localhost”, “myusername”, “mypassword”) or die (‘I cannot connect to the database.');

mysql_select_db (“mail_list”,$connection) or die( “Unable to select database”);

$query=”SELECT * FROM users ORDER by name”;

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

The new table stores the results of your query, which in this case is all of the names and e-mail addresses in the database. The second line of code creates a variable that contains all of the information needed to connect to the database. The line of code that begins with
mysql_select_db
creates a connection to the database, which in this case is named
mail_list
. The
$query
variable in the fourth line of code is set equal to the query that will be performed to select the information from the
users
table of the database. The results will be ordered by name. The
$result
variable in the fifth line of code is set equal to the
mysql_query
command, which queries the database using the information in the
$query
variable. The
$num
variable in the sixth line of code is set equal to the number of rows of data returned from the query. The final line of code closes the database.

Listing 4-3 shows the final lines of code. It's not as bad as it looks.

Listing 4-3: Using a while Loop to Extract the Data

$i=0;

while ($i < $num) {

$name=mysql_result($result,$i,”Name”);

$email=mysql_result($result,$i,”EMail”);

?>

$i++;

}

?>

The
$i
variable is set to
0
(zero), which, incidentally, is the ID number of the first record row returned from the database. The
while
loop will run as long as the value of
$i
is less than the variable
$num
. The next variable in the lineup is
$name
, which is set equal to the result of the first query. The variable
$i
, which is initially set to
0
(zero), is the first record that matches the search criteria and returns the data from the Name field of the database. The
$email
variable is similar but returns the result from the E-Mail field of the first record that matches the criteria. Now that you have your variables in a row, put some data in the table rows.

The next line ends the PHP code and is immediately followed by a table row, which has two cells. The first cell contains the contents of the
$name
variable, which contains the data from the Name field of the first record that matches our results. Notice the manner in which the PHP code is entered:

. This is a shortcut when you need to insert PHP in an HTML tag. The
tag signifies the start of PHP code, and the
?>
tag ends it. The next table cell contains the contents of the
$email
variable, which contains the data from the E-Mail field of the first record that matches the query results.