This chapter is from the book
This chapter is from the book
This chapter is from the book
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 sample database called domains. The table was created with five columns: a primary key field called id that will automatically increment an integer as data is added, a domain field that will contain a variable number of characters (VARCHAR), a sex field that will contain a single character, and a mail field that will contain 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( 20 ),
sex CHAR(1),
mail VARCHAR( 20 ) );
To add data to this table, we will need to construct and execute a SQL query. PHP provides the mysql_query() function for this purpose. mysql_query() 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 a positive value if the query is successful. If your query contains a syntax error, or if you don't have permission to access the database in question, then query() returns false. Note that a successful query does not necessarily result in any altered rows. Listing 12.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 sample database.
Listing 12.2 Adding a Row to a Table
1: <html> 2: <head> 3: <title>Listing 12.2 Adding a row to a table</title> 4: </head> 5: <body> 6: <?php 7: $user = "harry"; 8: $pass = "elbomonkey"; 9: $db = "sample"; 10: $link = mysql_connect( "localhost", $user, $pass ); 11: if ( ! $link ) 12: die( "Couldn't connect to MySQL" ); 13: mysql_select_db( $db, $link ) 14: or die ( "Couldn't open $db: ".mysql_error() ); 15: $query = "INSERT INTO domains ( domain, sex, mail ) 16: values( '123xyz.com', 'F', 'sharp@adomain.com' )"; 17: mysql_query( $query, $link ) 18: or die ( "Couldn't add data to \"domains\" table: " 19: .mysql_error() ); 20: mysql_close( $link ); 21: ?> 22: </body> 23: </html>
Notice that we did not insert a value for the id column in line 15. This field will auto-increment.
Of course, every time we reload the script in Listing 12.2, the same data is added to a new row. Listing 12.3 creates a script that will enter user input into our database.
Listing 12.3 Adding User Input to a Database
1: <html>
2: <head>
3: <title>Listing 12.3 Adding user input to a database</title>
4: </head>
5: <body>
6: <?php
7: if ( isset( $domain ) && isset( $sex ) && isset( $domain ) ) {
8: // check user input here!
9: $dberror = "";
10: $ret = add_to_database( $domain, $sex, $mail, $dberror );
11: if ( ! $ret )
12: print "Error: $dberror<BR>";
13: else
14: print "Thank you very much";
15: } else {
16: write_form();
17: }
18:
19: function add_to_database( $domain, $sex, $mail, &$dberror ) {
20: $user = "harry";
21: $pass = "elbomonkey";
22: $db = "sample";
23: $link = mysql_pconnect( "localhost", $user, $pass );
24: if ( ! $link ) {
25: $dberror = "Couldn't connect to MySQL server";
26: return false;
27: }
28: if ( ! mysql_select_db( $db, $link ) ) {
29: $dberror = mysql_error();
30: return false;
31: }
32: $query = "INSERT INTO domains ( domain, sex, mail )
33: values( '$domain', '$sex', '$mail' )";
34: if ( ! mysql_query( $query, $link ) ) {
35: $dberror = mysql_error();
36: return false;
37: }
38: return true;
39: }
40:
41: function write_form() {
42: global $PHP_SELF;
43: print "<form method=\"POST\">\n";
44: print "<input type=\"text\" name=\"domain\"> ";
45: print "The domain you would like<p>\n";
46: print "<input TYPE=\"text\" name=\"mail\"> ";
47: print "Your mail address<p>\n";
48: print "<select name=\"sex\">\n";
49: print "\t<option value=\"F\"> Female\n";
50: print "\t<option value=\"M\"> Male\n";
51: print "</select>\n";
52: print "<input type=\"submit\" value=\"submit!\">\n</form>\n";
53: }
54: ?>
55: </body>
56: </html>
To keep the example brief, we have left out one important process in Listing 12.3, testing user input. We are trusting our users. We should in fact check any kind of user input. We deal with the string functions that help you test user input in Hour 17, "Working with Strings."
We check for the variables $domain, $sex, and $mail on line 7. If they exist, we can be fairly certain that the user has submitted data, and we call the add_to_database() function on line 10.
The add_to_database() function declared on line 19 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 will change the original argument rather than a copy.
We attempt to open a connection to the MySQL server on line 23. If this fails, we assign an error string to $dberror and end the execution of the function by returning false on line 26. We select the database that contains the domains table on line 28 and build an SQL query to insert the user-submitted values. We pass this to mysql_query() on line 34, which makes the query for us. If either mysql_select_db() or mysql_query() fail, we assign the value returned by mysql_error() to $dberror and return false. Assuming that all went well, the function returns true on line 38.
Back in the calling code, we can test the return value from add_to_database() on line 11. If the function returns true, we can be sure that we have added to the database and thank the user on line 14. Otherwise, we write an error message to the browser. We know that the $dberror variable that we passed to add_to_database() will now contain useful information, so we include it in our error message.
If our initial if statement fails to find $domain, $sex, or $mail variables, we can assume that no data has been submitted and call another user-defined function, write_form() on line 16, which outputs an HTML form to the browser.