| [ Team LiB ] |
|
Changing DataYou can change data using the mysql_query() function in conjunction with an UPDATE statement. A successful UPDATE statement does not necessarily change any rows. You need to use a function to call mysql_affected_rows() to discover whether you have changed data in your table. mysql_affected_rows() optionally accepts a link resource; if this is missing, the most recent connection is assumed. This function can be used with any SQL query that can alter data in a table row. Listing 13.6 builds a script that enables an administrator to change any of the values in the domain column of our sample table. Listing 13.6 Using mysql_query() to Alter Rows in a Database
1: <?php
2: $user = "p24_user";
3: $pass = "cwaffie";
4: $db = "p24";
5: $link = connect( $user, $pass, $db );
6:
7: function connect( $user, $pass, $db ) {
8: $link = mysql_connect( "localhost", $user, $pass );
9: if ( ! $link ) {
10: die( "Couldn't connect to MySQL: ".mysql_error() );
11: }
12: mysql_select_db( $db, $link )
13: or die ( "Couldn't open $db: ".mysql_error() );
14: return $link;
15: }
16:
17: function update( $dblink, $domain, $id ) {
18: $id = mysql_real_escape_string( $id );
19: $domain = mysql_real_escape_string( $domain );
20: $query = "UPDATE domains SET domain='$domain' where id=$id";
21: $result = mysql_query( $query );
22: print "<h3>Table updated". mysql_affected_rows().
23: " row(s) changed</h3>\n\n";
24: }
25:
26: function getSelect( $dblink, $id ) {
27: $result = mysql_query( "SELECT * FROM domains" );
28: $select = "<select name=\"id\">\n";
29: while( $a_row = mysql_fetch_object( $result ) ) {
30: $select .= "<option value=\"$a_row->id\"";
31: if ( $id == $a_row->id ) {
32: $select .= "selected=\"selected\" ";
33: }
34: $select .= ">$a_row->mail: $a_row->domain</option>\n";
35: }
36: $select .= "</select>\n";
37: return $select;
38: }
39: ?>
40: <!DOCTYPE html PUBLIC
41: "-//W3C//DTD XHTML 1.0 Strict//EN"
42: "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
43: <html>
44: <head>
45: <title>Listing 13.6 Updating Data</title>
46: </head>
47: <body>
48:
49: <h1>Correct domains</h1>
50:
51: <?php
52: if ( ! empty( $_REQUEST['domain'] ) &&
53: ! empty( $_REQUEST['id'] ) ) {
54: update( $link, $_REQUEST['domain'], $_REQUEST['id'] );
55: }
56: ?>
57:
58: <form action="<?php print $_SERVER['PHP_SELF'] ?>" method="post">
59: <div>
60: <?php
61: print getSelect( $link, $_REQUEST['id'] );
62: ?>
63: <input type="text" name="domain" />
64: </div>
65: </form>
66: </body>
67: </html>
We open a connection to the database server and select a database as normal using the function declared on line 7. We test for the presence of the request arguments, domain and id, on line 17. If these are present, we call the update() function, which is defined on line 17, passing it the database resource acquired from the connect() function and the id and domain request parameters. The update() function builds a SQL UPDATE query on line 20 that changes the value of the domain field where the id field contains the same value as our $id argument. We do not get an error if a nonexistent id is used or if the $domain variable is the same as the current value for domain in the relevant row. Instead, the mysql_affected_rows() simply returns 0. We print this return value (usually 1 in this example) to the browser on lines 22 and 23. Starting on line 58, we print an HTML form to enable the administrator to make her changes. Most of the work is delegated to the getSelect() function, which is declared on line 26. We use mysql_query() (line 27) again to extract the values of the id and domain columns and incorporate them into an HTML SELECT element (lines 28–36). The administrator uses this pop-up menu to choose which domain to change. If the administrator has already submitted the form and the id value she chose matches the value of the id field we are currently outputting, we add the string selected="selected" to the option element (line 32). This ensures that her changed value will be instantly visible to her in the menu. |
| [ Team LiB ] |
|