<<

. 16
( 132 .)



>>


We want to be able to update the income field of the income table to the sum of
the donations acquired by each person. As before, we can do this by deleting the
current records in the income table and then creating new ones. Or, we can just use
replace, as in the following code:

replace income (id, income)
select id, sum(amount) from donations group by id
;

Table 3-11 shows the results.


TABLE 3-11 THE INCOME TABLE

Id income salary

15000 NULL
1

42000 NULL
2

1028.15 NULL
3

30000 NULL
4




Notice that we™ve lost the data from our salary column. The trouble is that we
are not allowed to include the table we are replacing into the select statement. To
change some fields and keep others, we have to create a temporary table storing the
current values in income and join it to donations in the replace statement. How
much better that is than a delete and an insert is a matter of taste. Remember,
replace follows the same syntax as insert. There is no where in replace.
64 Part I: Working with MySQL


The Basic select Statement
When it comes time to take the information from your database and lay it out on
your Web pages, you™ll need to limit the information returned from your tables and
join tables together to get the proper information. So you™ll start with your data-
base, the superset of information, and return a smaller set. In the select statement
you™ll choose columns from one or more tables to assemble a result set. This result
will have columns and rows and thus can be effectively thought of as a table (or a
two-dimensional array, if your mind works that way). This table doesn™t actually
exist in the database, but it helps to think about it this way.
The basic select statement requires you to indicate the table or tables you are
selecting from and the column names you require. If you wish to select all the
columns from a given table, you can substitute an asterisk (*) for the field names.
For example:

select column_1, column_2, column_3 from table_name

or

select * from table_name

Keep in mind that with a select statement you are not actually altering the
tables involved in the query. You are simply retrieving information. From PHP, you
will send the query to MySQL from the mysql_query() function.
There are all sorts of ways you can choose to lay out the information, but at
times you™re going to want a simple HTML table with the column names put in a
header row. The simple PHP code in Listing 3-1 will lay out any SQL query in an
ultra-simple HTML table. It includes a simple form that will enable you to enter a
query. If you don™t understand this code just yet, don™t worry about it; all the PHP
functions will be covered in Chapter 6. Alter the mysql_connect() and
mysql_select_db() functions if you wish to change the database used. I wouldn™t
advise putting this script on a server that is publicly available, as it would open up
a huge security hole.

Listing 3-1: A PHP Script That Converts a SQL Query to an HTML Table
<?php
mysql_connect(“localhost”, “username”, “password”) or
die(“Could not connect to database.”);

mysql_select_db(“test”) or
die(“Cannot select database”);
Chapter 3: Inserting, Editing, and Selecting Data 65

if( !empty($_GET[“query”]) ){
$query = stripslashes($_GET[“query”]);
} else {
$query = “SELECT * FROM users”;
}

$result = mysql_query($query) or
die( mysql_error() );

$number_cols = mysql_num_fields($result);

echo “<b>Query: $query</b>”,
˜<table border=”1”>™,
˜<tr align=”center”>™;

for ($i=0; $i < $number_cols; ++$i) {
echo ˜<th>™ . mysql_field_name($result, $i) . “</th>\n”;
}
echo “</tr>\n”;

while( $row = mysql_fetch_row($result) ){
echo “<tr>\n”;
foreach( $row as $field ){
echo ˜<td>™ . (is_null($field) ? ˜NULL™ : $field) . “</td>\n”;
}
echo “</tr>\n”;
}

echo ˜</table>™;
?>

<form action=”<?php echo $_SERVER[˜PHP_SELF™]; ?>” method=”GET”>
<input type=”text” name=”query” size=”50” value=”<?php echo
$query; ?>”><br>
<input type=”submit”>
</form>

For the remainder of this chapter you will see how to build on the complexity of
the select statement. To show you things in action, we created a table in MySQL
against which we can run these queries. The create statement in Listing 3-2 makes
a table named users that holds basic personal information.
66 Part I: Working with MySQL

Listing 3-2: A create Statement for the users Table
CREATE TABLE users (
userid int(10) unsigned NOT NULL auto_increment,
fname varchar(25) NOT NULL,
lname varchar(25) NOT NULL,
addr varchar(255) NOT NULL,
addr2 varchar(255),
city varchar(40) NOT NULL,
state char(2) NOT NULL,
zip varchar(5),
lastchanged timestamp(14),
PRIMARY KEY (userid)
);

To get things started, we loaded up the database with a few rows of information.
The insert statements that load this data are shown in Listing 3-3.

Listing 3-3: insert Statements for the users Table
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (1,™Jason™,™Greenspan™,™555 5th
St™,™apt 204™,™San Francisco™,™CA™,™94118™,20020626134625);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (2,™Brad™,™Bulger™,™666 6th St™,™apt
17™,™San Francisco™,™CA™,™94116™,20020626134704);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (3,™John™,™Doe™,™279 66th St™,NULL,™New
York™,™NY™,™11100™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (4,™Jane™,™Doe™,™987 67th
St™,NULL,™Windsor™,™MA™,™14102™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (5,™Jean™,™Banks™,™4 Elm
St™,™™,™Eugene™,™OR™,™98712™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (6,™Donny™,™Alphonse™,™25 14th
St™,NULL,™New York™,™NY™,™11104™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (7,™Meghan™,™Garcis™,™44 Maple
Dr™,NULL,™Nashville™,™TN™,™37114™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (8,™Kenny™,™Clark™,™General
Delivery™,NULL,™Washeegan™,™VT™,™10048™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (9,™Danny™,™Briggs™,™8 Palm Way™,™ste
222™,™Miami™,™FL™,NULL,20020627120644);
Chapter 3: Inserting, Editing, and Selecting Data 67

INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (10,™Luke™,™Gnome™,™8 Palm Way™,NULL,™San
Francisco™,™CA™,™94118™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (11,™Alan™,™Paine™,™27 Casa Way™,NULL,™Los
Angeles™,™CA™,™94204™,20020627120644);
INSERT INTO users (userid, fname, lname, addr, addr2, city, state,
zip, lastchanged) VALUES (12,™Jay™,™Grimes™,™718 Field
St™,NULL,™Pierre™,™ND™,™44221™,20020627120644);

When run through the PHP code above, the query select * from users will
return the results shown in Figure 3-1.




Figure 3-1: Results of query using select * from users


The where clause
The where clause limits the rows that are returned from your query. To get a single
row from a table you would a run the query against the primary key. For instance,
to get all the information on Brad you would use this query:

select * from users where userid = 2;

Figure 3-2 shows the results of this query.
68 Part I: Working with MySQL




Figure 3-2: Results of query using select * from users where userid=2;


If you™re doing a comparison to a column that stores a string (char, varchar,
and so on), you will need to surround the string used for comparison in the where
clause with single quotes.

select * from users where city = ˜San Francisco™;

MySQL has several comparison operators that can be used in the where clause.
Table 3-12 lists these operators.


Don™t be confused by the fact that the “equal to” operator is = in MySQL and
== in PHP. Be careful.




You can combine several comparisons with and or or:

select * from users
where userid = 6 or
city = ˜San Francisco™
;
select * from users
Chapter 3: Inserting, Editing, and Selecting Data 69

where state = ˜CA™ and
city = ˜San Francisco™
;



TABLE 3-12 MYSQL COMPARISON OPERATORS

Operator Definition

Equal to
=

<> or != Not equal to
Less than
<

Less than or equal to
<=

Greater than
>

Greater than or equal to
>=

Compares a string (discussed in detail later in this chapter)
like

NULL-safe comparison
<=>




It™s important to note that fields with null values cannot be compared with any
of the operators used in Table 3-12. For instance, in the table shown in Figure 3-1,

<<

. 16
( 132 .)



>>