Building Database-Driven Applications with PHP and MySQL
Querying the Database
The mysql_query() function is essentially a PHP wrapper for performing SQL commands. Any valid and complete SQL statement can be passed as a parameter to this function.
Four types of queries make up SQL's data manipulation statements. They are as follows: SELECT, UPDATE, INSERT, and DELETE.
Essentially, this statement says, "Select all the fields in all the rows of the table called users." The asterisk is a wild card character that tells MySQL that it should grab anything available in the specified tables.
To request this data from a PHP script, we pass the query to the mysql_query() function. Because we are expecting records to be returned from the database, the function must be assigned to a variable. The query is a string, so PHP requires it to be surrounded by quotes. (Variables are often passed as part of queries and SQL requires that string values in WHERE clauses and INSERT statements be surrounded with single quotes. Though single quotes can be used here, it is generally preferable to use double quotes for clarity.)
Just like mysql_connect() and mysql_select_db(), this function will return FALSE if there is an error in the SQL. If the function is successful, however, $result will hold a resource identifier.
Because the variable just holds an identifier, we cannot get any of our records by outputting $result. To get the data, we need to pass the result to another PHP function, mysql_fetch_array(). This function returns an array of each record from a SELECT query, line by line. Unless you know for a fact that your query will only return a single record, it is advisable to use a while loop to itinerate through each row of the result set. The function maintains an internal place mark of which record it is on, so the loop will terminate once all of the records have been returned. Let's return to our sample database from the start of the article and the select statement above to see how you would output the data:
Notice that the array indexes of $row match the column names of the table. The array generated by mysql_fetch_array() can optionally be an associative or a numeric array. By default, records are accessible by both types of keys. So, the id column (the first column in the table) can be accessed as $row['id'] or $row;
Limiting Returned Fields
The SELECT query we've looked at so far is very simple, but SQL gives us the ability to form more complex statements. The more detail added to the query, the more we drill down our results.
Let's say we only wanted the id and email of our users. Rather than selecting all the fields in all the records, we only want to retrieve two fields in each record. To do this, one change is made to the query:
Notice that the wildcard character has been replaced with the specific columns we want to return. In large tables, it is always advisable to only select those columns you actually need. This saves on both processing power and memory.
The Power of WHERE and Limiting Returned Records
In addition to limiting a query by which fields are returned, we also can specify which records should be returned. Suppose we only wanted to return two rows from our example table, the one where the email address is firstname.lastname@example.org and the one where the email address is email@example.com. The query would look like this:
The WHERE clause allows logical statements to be appended to the query. These statements limit which records returned. WHERE is also used in DELETE and UPDATE statements to determine which records should be affected. The clause typically includes one or more fields and conditions that the contents of each must meet. In this way, WHERE acts a lot like an If Statement: A record is tested against the conditions in the WHERE clause. If the conditions are found to be true, the record is returned.
Additionally, there are always ways to directly limit the number of records that are returned. Logically enough, this is done by appending LIMIT to the query. Here are a few examples:
When two numbers are included following LIMIT, the first denotes the starting point, or offset. The second number defines how many records from that starting point should be returned. If no offset is specified, the records will be returned starting from the first match.
Sorting Returned Records: ORDER BY
Sometimes, it is useful to be able to sort the rows that are returned in a result. For example: Imagine you had a simple script that displayed messages posted by users on your site; these messages were stored in a table. When a user posts a new message, it gets added to the table. Retrieve the messages from the database and output them, and they will show up in the same order that they were added. The newest message will be shown last because it was added last.
But what if you wanted to display the newest message first? You could load all the results from the query into an array, use an array sorting function, and then output it all. But there is an easier way built right into SQL:
The ORDER BY clause can be appended to any select statement—after the WHERE clause, and before the LIMIT clause—to order the results by one or more columns. In the case of string fields, the records are sorted alphabetically, from a-z, by default. Numeric fields are sorted from smallest to largest by default. Multiple columns can be included in the ORDER BY clause by separating them with commas.
INSERT is used to add a new record to a table. The syntax looks like this:
By now, the first part of this query should look fairly familiar to you. It begins with the SQL command we are going to perform, in this case INSERT. Because we are inserting a new record, INTO is included, followed by the name of the table we are adding it to.
The first set of parentheses surrounds a list of the fields within the table. These must be listed in the same order that they occur in the actual table.
The second portion defines the values that are going to be added. They must match the order of the fields within the query and the table for them to be inserted in the proper location.
Like the field names, SQL requires that each value be surrounded by quotes, because they are strings. If there is no value to be inserted, the position for the value must still be reserved by using empty quotes.
If you are inserting values for all of the fields, the field listing may be left out:
MySQL will insert the values into the columns in the order that they appear.
So far we've covered selecting and adding information to a table, but what if you want to change existing data? UPDATE allows us to do just that. The basic syntax for an UPDATE statement looks like this:
In this case, we're updating table users, and changing the value of the column called email to "firstname.lastname@example.org". Take another look at the query. You'll notice that we have not specified which records should have the field email updated. If this query were run, all the fields called "email" in all the records would be changed. To confine the changes to a single (or multiple) specific records, the WHERE clause makes a re-appearance. In the select statement, it was used to evaluate which records should be returned; here, it is used to evaluate which records should be updated:
Now, only records where the id number has a value of one will be updated.
Multiple fields within the same record can also be updated. In this example, both name and email are altered:
What gets added must eventually get deleted. The DELETE query is the simplest query and looks like this:
Because entire records are deleted, there is no need to specify a field list. The WHERE clause is essential; without it, all the records in the table would be removed.
Page 2 of 3