This chapter is from the book
This chapter is from the book
This chapter is from the book
Changing Data
You 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 12.6 builds a script that allows an administrator to change any of the values in the domain column of our example table.
Listing 12.6 Using mysql_query() to Alter Rows in a Database
1: <html>
2: <head>
3: <title>Listing 12.6 Using mysql_query()
4: to alter rows in a database</title>
5: </head>
6: <body>
7: <?php
8: $user = "harry";
9: $pass = "elbomonkey";
10: $db = "sample";
11: $link = mysql_connect( "localhost", $user, $pass );
12: if ( ! $link )
13: die( "Couldn't connect to MySQL" );
14: mysql_select_db( $db, $link )
15: or die ( "Couldn't open $db: ".mysql_error() );
16:
17: if ( isset( $domain ) && isset( $id ) ) {
18: $query = "UPDATE domains SET domain = '$domain' where id=$id";
19: $result = mysql_query( $query );
20: if ( ! $result )
21: die ("Couldn't update: ".mysql_error());
22: print "<h1>Table updated ". mysql_affected_rows() .
23: " row(s) changed</h1><p>";
24: }
25: ?>
26: <form action="<? print $PHP_SELF ?>" method="POST">
27: <select name="id">
28: <?
29: $result = mysql_query( "SELECT domain, id FROM domains" );
30: while( $a_row = mysql_fetch_object( $result ) ) {
31: print "<OPTION VALUE=\"$a_row->id\"";
32: if ( isset($id) && $id == $a_row->id )
33: print " SELECTED";
34: print "> $a_row->domain\n";
35: }
36: mysql_close( $link );
37: ?>
38: </select>
39: <input type="text" name="domain">
40: </form>
41: </body>
42: </html>
We open a connection to the database server and select a database as normal. We then test for the presence of the variables $domain and $id on line 17. If these are present, we build a SQL UPDATE query on line 18 that changes the value of the domain field where the id field contains the same value as our $id variable. 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 26, we print an HTML form to allow the administrator to make her changes. Note that we use mysql_query() (line 29) once again to extract the values of the id and domain column and incorporate them in an HTML SELECT element (lines 27 to 38). The administrator will use 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 to the OPTION element (line 33). This ensures that her changed value will be instantly visible to her in the menu.