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.â€ť);

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

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.