Inserting Data
The series of forms for adding records to the MY_PRODUCTS table is much simpler than the three-step table-creation sequence-it has only two steps.
To begin, open your favorite text editor, create a file called show_addrecord.html, and set up an HTML "shell":
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Add a Record</TITLE> </HEAD> <BODY> <!-- your HTML form will go here --> </BODY> </HTML>
Give the page a heading so that you know what you're doing:
<h1>Adding a Record to MY_PRODUCTS</h1>
To create the form code, assume that step two in the sequence will be a PHP script called do_addrecord.php and that your form will use the POST method:
<FORM method="POST" action="do_addrecord.php">
Next, create three text fields and a text area to capture the values for $_POST[item_id], $_POST[item_title], $_POST[item_desc], and $_POST[item_price]-the names of the columns in MY_PRODUCTS. The following sample form uses an HTML table to display the form fields, but feel free to display the form however you'd like:
<FORM method="POST" action="do_addrecord.php"> <table cellspacing=5 cellpadding=5> <tr> <td valign=top><strong>Item ID:</strong></td> <td valign=top><INPUT type="text" name="item_id" size=5 maxlength=5></td> </tr> <tr> <td valign=top><strong>Item Title:</strong></td> <td valign=top><INPUT type="text" name="item_title" size=30 maxlength=50></td> </tr> <tr> <td valign=top><strong>Item Description:</strong></td> <td valign=top><TEXTAREA name="item_desc" cols=30 rows=5></TEXTAREA></td> </tr> <tr> <td valign=top><strong>Item Price:</strong></td> <td valign=top>$ <INPUT type="text" name="item_price" size=10></td> </tr>
Finally, add the Add Record button:
<tr> <td align=center colspan=2><INPUT type="submit" value="Add Record"></td> </tr>
Don't forget the closing </FORM> and </TABLE> tags!
Your HTML source code should look something like this:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Add a Record</TITLE> </HEAD> <BODY> <h1>Adding a Record to MY_PRODUCTS</h1> <FORM method="POST" action="do_addrecord.php"> <table cellspacing=5 cellpadding=5> <tr> <td valign=top><strong>Item ID:</strong></td> <td valign=top><INPUT type="text" name="item_id" size=5 maxlength=5></td> </tr> <tr> <td valign=top><strong>Item Title:</strong></td> <td valign=top><INPUT type="text" name="item_title" size=30 maxlength=50></td> </tr> <tr> <td valign=top><strong>Item Description:</strong></td> <td valign=top><TEXTAREA name="item_desc" cols=30 rows=5></TEXTAREA></td> </tr> <tr> <td valign=top><strong>Item Price:</strong></td> <td valign=top>$ <INPUT type="text" name="item_price" size=10></td> </tr> <tr> <td align=center colspan=2><INPUT type="submit" value="Add Record"></td> </tr> </table> </FORM> </BODY> </HTML>
Place this file on your Web server, and access it with your browser at its URL, http://127.0.0.1/show_addrecord.html. In your browser window, you should now see something like what is shown in Figure 4.5.
Figure 4.5: The form to add a record
Next, you'll create the PHP script that takes your form input, creates a proper SQL statement, creates the record, and displays the record for you as a confirmation. It's not as difficult as it sounds. Since the form action in show_addrecord.html is do_addrecord.php, open your favorite text editor and create a file called do_addrecord.php.
Before your script does anything else, you'll want to check that values were actually entered in the form. Set up a statement that looks for these values. If they don't exist, redirect the user to the form:
<?php if ((!$_POST[item_id]) || (!_POST[$item_title]) || (!$_POST[item_desc]) || (!$_POST[item_price])) { header("Location: http://127.0.0.1/show_addrecord.html"); exit; } ?> Next, add the HTML "shell" after the if statement:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Add a Record</TITLE> </HEAD> <BODY> <!-- your HTML form will go here --> </BODY> </HTML>
Give the page a heading so that you know what you're doing:
<h1>Adding a record to MY_PRODUCTS</h1>
The next section of PHP code will build the SQL statement that will be sent to MySQL. Remember, the INSERT syntax is
INSERT INTO [table name] (column1, column2) VALUES ('valuel', 'value2'); When you initially created the MY_PRODUCTS table, the field order was ITEM_ID, ITEM_TITLE, ITEM_DESC, and ITEM_PRICE. Use this same order in the INSERT statement used to create a record. Hold the SQL statement in a variable called $sql, and build the VALUES list using the variable names from the form:
$sql = "INSERT INTO MY_PRODUCTS (ITEM_ID, ITEM_TITLE, ITEM_DESC, ITEM_PRICE) VALUES ('$_POST[item_id]', '$_POST[item_title]', '$_POST[item_desc]', '$_POST[item_price]')"; After the SQL statement, simply add the connection and query code you used earlier. Next, add an if statement to print the full text of the record that was successfully added. One trick, though-use the stripslashes() function on the strings to remove any slashes that were automatically added by PHP. You want to use the original strings during the insertion into the database, as all elements will be properly escaped. However, you don't need to see the slashes when displaying the results to the screen.
if ($sql_result) { echo " <P>Record added!</p> <table cellspacing=5 cellpadding=5> <tr> <td valign=top><strong>Item ID:</strong></td> <td valign=top>".stripslashes($_POST[item_id])."</td> </tr> <tr> <td valign=top><strong>Item Title:</strong></td> <td valign=top>".stripslashes($_POST[item_title])."</td> </tr> <tr> <td valign=top><strong>Item Description:</strong></td> <td valign=top>".stripslashes($_POST[item_desc]."</td> </tr> <tr> <td valign=top><strong>Item Price:</strong></td> <td valign=top>\$ ".stripslashes($_POST[item_price]."</td> </tr> </table>"; } ?> That's all there is to it. Your source code should look something like this:
<?php if ((!$_POST[item_id]) || (!$_POST[item_title]) || (!$_POST[item_desc]) || (!$_POST[item_price])) { header("Location: http://127.0.0.1/show_addrecord.html"); exit; } ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Add a Record</TITLE> </HEAD> <BODY> <h1>Adding a Record to MY_PRODUCTS</h1> <?php $sql = "INSERT INTO MY_PRODUCTS (ITEM_ID, ITEM_TITLE, ITEM_DESC, ITEM_PRICE) VALUES ('$_POST[item_id]', '$_POST[item_title]', '$_POST[item_desc]', '$_POST[item_price]')"; // create connection; substitute your own information $conn = mysql_connect("localhost","joeuser","34Nhjp") or die (mysql_error())"; // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); // execute SQL query and get result $sql_result = mysql_query($sql,$conn) or die (mysql_error()); if ($sql_result) { echo " <P>Record added!</p> <table cellspacing=5 cellpadding=5> <tr> <td valign=top><strong>Item ID:</strong></td> <td valign=top>".stripslashes($_POST[item_id])."</td> </tr> <tr> <td valign=top><strong>Item Title:</strong></td> <td valign=top>".stripslashes($_POST[item_title])."</td> </tr> <tr> <td valign=top><strong>Item Description:</strong></td> <td valign=top>".stripslashes($_POST[item_desc]."</td> </tr> <tr> <td valign=top><strong>Item Price:</strong></td> <td valign=top>\$ ".stripslashes($_POST[item_price]."</td> </tr> </table>"; } ?> </BODY> </HTML> Place this file on your Web server, and go back to the form at http://127.0.0.1/show_addrecord.html. Create a sample product such as the one shown in Figure 4.6.
Figure 4.6: Adding a sample product
Go ahead and click on the Add Record button to execute the do_addrecord.php script. If the query is successful, you should see a results page like the one shown in Figure 4.7.
Figure 4.7: Successful record addition
Use this form to add several more products to the MY_PRODUCTS table. In the next section, you'll select some records to display (it won't be any fun with just one record!).
EAN: 2147483647
Pages: 74