[ Team LiB ] Previous Section Next Section

Adding Data to a Table

Now that we have access to our database, we can add information to one of its tables. For the following examples, imagine that we are building a site that allows people to buy domain names.

We have created a table within the p24 database called domains. The table was created with five columns: a primary key field called id that automatically increments an integer as data is added, a domain field that contains a variable number of characters ((VARCHAR)), a sex field that contains a single character (M or F), and a mail field that contains a user's email address. The following SQL statement was used in the MySQL client to create the table:


create table domains (
                 id INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY( id ),
        domain VARCHAR( 200 ) ,
        sex ENUM('M', 'F') NOT NULL,
        mail VARCHAR( 200 ) );

To add data to this table, we need to construct and execute a SQL query. PHP provides the mysql_query() function for this purpose; it requires a string containing a SQL query and, optionally, a link resource. If the resource is omitted, the query is sent to the database server to which you last connected. mysql_query() returns true if the query is successful. If your query contains a syntax error or you don't have permission to access the database in question, mysql_query() returns false. Listing 13.2 extends our previous examples starting at line 15 and uses mysql_query() (line 17) to send an INSERT statement to the domains table in the p24 database.

Listing 13.2 Adding a Row to a Table
 1: <!DOCTYPE html PUBLIC
 2:   "-//W3C//DTD XHTML 1.0 Strict//EN"
 3:   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
 4: <html>
 5: <head>
 6: <title>Listing 13.2 Adding a Row to a Database</title>
 7: </head>
 8: <body>
 9: <div>
10: <?php
11: $user = "p24_user";
12: $pass = "cwaffie";
13: $db = "p24";
14: $link = @mysql_connect( "localhost", $user, $pass );
15: if ( ! $link ) {
16:   die( "Couldn't connect to MySQL: ".mysql_error() );
17: }
18: print "<h2>Successfully connected to server</h2>\n\n";
19: @mysql_select_db( $db )
20:   or die ( "Couldn't open $db: ".mysql_error() );
21: print "Successfully selected database \"$db\"<br />\n";
22:
23: $query = "INSERT INTO domains( domain, sex, mail )
24:    values( 'example.com', 'F', 'sharp@example.com' )";
25: print "running query: <br />\n$query<br />\n";
26: mysql_query( $query, $link )
27:   or die ( "INSERT error: ".mysql_error() );
28:
29: mysql_close( $link );
30: ?>
31: </div>
32: </body>
33: </html>

Notice that we did not insert a value for the id column in line 15. This field auto-increments.

Of course, every time we reload the script in Listing 13.2, the same data is added to a new row. Listing 13.3 creates a script that enters user input into our database.

Listing 13.3 Adding User Input to a Database
 1: <!DOCTYPE html PUBLIC
 2:   "-//W3C//DTD XHTML 1.0 Strict//EN"
 3:   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
 4: <html>
 5: <head>
 6: <title>Listing 13.3 Adding user input to a database</title>
 7: </head>
 8: <body>
 9: <?php
10:
11: if ( ! empty( $_REQUEST['sex'] ) &&
12:     ! empty( $_REQUEST['domain'] ) &&
13:     ! empty( $_REQUEST['mail'] ) ) {
14:   // check user input here!
15:   $dberror = "";
16:   $ret = add_to_database( $_REQUEST['domain'],
17:               $_REQUEST['sex'],
18:               $_REQUEST['mail'], $dberror );
19:   if ( ! $ret ) {
20:     print "Error: $dberror<br />\n";
21:   } else {
22:     print "Thank you very much<br />\n";
23:   }
24: } else {
25:   write_form();
26: }
27:
28: function add_to_database( $domain, $sex, $mail, &$dberror ) {
29:   $domain = mysql_real_escape_string( $domain );
30:   $sex = mysql_real_escape_string( $sex );
31:   $mail = mysql_real_escape_string( $mail );
32:   $link = mysql_pconnect( "localhost", "p24_user", "cwaffie" );
33:   if ( ! $link ) {
34:     $dberror = mysql_error();
35:     return false;
36:   }
37:   if ( ! mysql_select_db( "p24", $link ) ) {
38:     $dberror = mysql_error();
39:     return false;
40:   }
41:   $query = "INSERT INTO domains ( domain, sex, mail )
42:         values( '$domain', '$sex', '$mail' )";
43:   if ( ! mysql_query( $query, $link ) ) {
44:     $dberror = mysql_error();
45:     return false;
46:   }
47:   return true;
48: }
49:
50: function write_form() {
51:   print <<<EOF
52:     <form method="post" action="{$_SERVER['PHP_SELF']}">
53:
54:     <p><input type="text" name="domain" />
55:     The domain you would like</p>
56:
57:     <p><input type="text" name="mail" />
58:     Your mail address</p>
59:
60:     <p><select name="sex">
61:     <option value="F">Female</option>
62:     <option value="M">Male</option>
63:     </select></p>
64:
65:     <p><input type="submit" value="submit!" /></p>
66:     </form>
67: FORM;
68: }
69: ?>
70: </body>
71: </html>

To keep the example brief, we have left out one important process in Listing 13.3, testing user input. We are trusting our users. We should, in fact, check any kind of user input to ensure that we are getting sensible values.

We check for the request parameters domain, sex, and mail on line 11. If they exist, we can be fairly certain that the user has submitted data, and we can then call the add_to_database() function on line 16.

The add_to_database() function declared on line 28 requires four arguments: the $domain, $sex, and $mail variables submitted by the user and a string variable called $dberror. We populate this last argument with any error strings we encounter. For this reason, we accept $dberror as a reference to a variable. Any changes made to this string within the function change the original argument rather than a copy. We use the function mysql_real_escape_string() to transform the user-submitted values held by $domain, $sex, and $mail. This adds backslash characters into the string to escape characters such as single and double quotation marks. You should always escape data that is passed in from the user.

We attempt to open a connection to the MySQL server on line 32. If this fails, we assign an error string to $dberror and end the execution of the function by returning false on line 35. We select the database that contains the domains table on line 37 and build a SQL query to insert the user-submitted values. We pass this to mysql_query() on line 43, which makes the query for us. If either mysql_select_db() or mysql_query() fails, we assign the value returned by mysql_error() to $dberror and return false. Assuming that all went well, the function returns true on line 47.

Back in the calling code, we can test the return value from add_to_database() on line 19. If the function returns true, we can be sure that we have added to the database and thank the user on line 22. Otherwise, we write an error message to the browser. We know that the $dberror variable we passed to add_to_database() now contains useful information, so we include it in our error message.

If our initial if statement fails to find domain, sex, or mail request parameters, we can assume that no data has been submitted and call another user-defined function—write_form()—on line 16 to output an HTML form to the browser.

    [ Team LiB ] Previous Section Next Section