Some Basic SQL

To interact with databases in PHP, you use SQL (Structured Query Language). This chapter is designed to give you the experience you need to work with SQL in PHP. Say that you have a database table named "fruit" you want to work with. To get a copy of that table to work with in your code, you can execute this SQL statement, called a query:

 SELECT * FROM fruit 

To interact with MySQL, you'd use the mysql_query function to execute that SQL query like this:

 $query = "SELECT * FROM fruit"; $result = mysql_query($query)     or die("Query failed: ".mysql_error()); 

We'll go through a few SQL statements here to get up to speed. You may already have a good handle on SQL, in which case you can skip this chunk; otherwise, take a look because we'll be using SQL throughout the chapter. We'll assume we have a database table named fruit, which has two fields, name (such as "apples" or "oranges") and number (representing the number of a particular kind of fruit you have in stock).

You use the SELECT statement to retrieve fields from a table; here's an example where we're retrieving all the records in the fruit table, using the wildcard character *:

 SELECT * FROM fruit 

This returns a recordset that holds all the records in the fruit table. You can also select specific fields from a table like this, where we're selecting the name and number fields from the fruit table:

 SELECT name, number FROM fruit 

Using the WHERE clause, you can set up selection criteria that the records in the recordset generated by the query must meet. For example, to select all the records in the fruit table where the name field equals apples, you can execute this statement:

 SELECT * FROM fruit WHERE name= "apples" 

You don't have to use an equals sign here; you can test fields using these operators:

• < (less than)

• <= (less than or equal to)

• > (greater than)

• >= (greater than or equal to)

You can use an IN clause to specify a set of values that fields can match. For example, here's how you can retrieve records that have values in the name field that match apples or oranges:

 SELECT * FROM fruit WHERE name IN ("apples", "oranges") 

You can also use logical operations on the clauses in your SQL statements. Here's an example where we're specifying two criteria: the name field must hold either "apples" or "oranges", and there must be some value in the number fieldyou use the NULL keyword to test if there's anything in a field:

 SELECT * FROM fruit WHERE name NOT IN ("apples", "oranges") AND number IS NOT NULL 

You can use these logical operators to connect clauses: AND, OR, and NOT. Using AND means that both clauses must be true, using OR means either one can be true, and using NOT flips the value of a clause from trUE to FALSE or FALSE to trUE.

As you might expect, you can also order the records in the recordset produced by a SQL statement. Here's an example where we're ordering the records in the fruit table using the name field:

 SELECT * FROM fruit ORDER BY name 

You can also sort records in descending order with the DESC keyword:

 SELECT * FROM fruit ORDER BY name DESC 

You can use the DELETE statement to delete records like this, where we're removing all records from the fruit table that have name values that are not apples or oranges:

 DELETE  FROM fruit WHERE name NOT IN ("apples", "oranges") 

You use the UPDATE statement to update a database when you want to make changes. For example, here's how to change the value of the number field in the record that contains the number of apples:

 UPDATE fruit SET number = "2006" WHERE name = "apples" 

You can also insert new data into a tablehere's an example that inserts a new row into the fruit table:

 INSERT INTO fruit (name, number) VALUES('apricots', '203') 

OK, we've gotten as much SQL under our belts as we'll need. Now how about connecting database servers to PHP?