Beginning Databases with PostgreSQL - Chapter 15: Accessing PostgreSQL from PHP

Building Queries

We have already seen a simple example of executing a query from PHP. In this section, we will cover the topic of query building and execution in more depth.

SQL queries are merely strings, so they can be built using any of PHP's string functions. The following are three examples of query string construction in PHP:

$lastname = strtolower($lastname);    
$query = "SELECT * FROM customer WHERE lname = '$lastname'";

This example performs the lower case conversion of $lastname first. Then, it builds the query string using PHP's standard string syntax.

Note that the value of $lastname will remain lower case after these lines.

$query = "SELECT * FROM customer WHERE lname = '" .    
        strtolower($lastname) . "'";

This example uses an inline call to strtolower(). Functions can't be called from inside string literals (in other words between quotation marks), so we need to break our query string into two pieces and concatenate them (using the "dot" operator) with the function call in between.

Unlike the previous example, the result of the strtolower() function will not affect the value of $lastname after this line is executed by PHP.

$query = sprintf("SELECT * FROM customer WHERE lname = '%s'",    
                strtolower($lastname));

This final example uses the sprintf() function to generate the query string. The sprintf() function uses special character combinations (the %s in the above line, for example) to format strings. More information on the sprintf() function is available at http://www.php.net/manual/en/function.sprintf.php.

Each of these approaches will produce exactly the same query string. The best method to use, like most things, will depend on the situation. For simple queries, a direct string assignment will probably work best, but when the situation calls for the interpolation or transformation of a large number of variables, you might want to explore different approaches. In some cases, you might encounter a trade-off between execution speed and code readability. This is true of most programming tasks, so you will have to apply your best judgment.

Here's an example of a complex query written as a long assignment string:

$query = "UPDATE table $tablename SET " . strtolower($column) . " = '" .    
        strtoupper($value) . "'";

This could be rewritten using the PHP sprintf() function as:

$query = sprintf("UPDATE table %s SET %s = '%s'", $tablename,    
                strtolower($column), strtoupper($value));

The second expression is clearly more readable than the first, although benchmarking will show that this readability comes at a slight performance cost as programmer time is much more expensive than machine time. In this case, the tradeoff of readability over execution speed is probably worth it, unless you are doing hundreds of these types of string constructions per page request.