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

Complex Queries

In an ideal world, all of our queries would be as simple as those used in the previous examples, but we all know that is seldom true. In those cases where more complex queries need to be built, we find that PHP offers a number of convenient functions to aid us in our task.

For example, consider the case where a large number of table deletions need to be performed. In raw SQL, the query might look something like this:

DELETE FROM items WHERE item_id = 4 OR item_id = 6

Now, that query alone doesn't appear all that complicated, but what if this query needed to delete a dozen rows, specifying the item_id of each row in the WHERE clause? The query string gets pretty long at that point, and because the number of expressions in the where clause probably needs to be varying we need to account for these details in our code.

We will probably be receiving our list of item IDs to be deleted from the user via some method of HTML form input, so we can assume they will be stored in some kind of array format (at least, that's the most convenient means of storing the list). We'll assume this array of item IDs is named $item_ids. Based on that assumption, the above query could be constructed as follows:

<?php    
$query = "DELETE FROM items WHERE ";    
$query .= "item_id = " . $item_ids[0];    
if (count($item_ids) > 1) {    
   array_shift($item_ids);    
   $query .= " or item_id = " .    
             implode(" or item_id =", $item_ids);    
}    
?>

This will produce an SQL query with an arbitrary number of item IDs. Based on this code, we can write a generic function to perform our deletions:

<?php    
   
function sqlDelete($tablename, $column, $ids) {    
   $query = '';    
   if (is_array($ids)) {    
       $query = "DELETE FROM $tablename WHERE ";    
       $query .= "$column = " . $ids[0];    
       if (count($ids) > 1) {    
           array_shift($ids);    
           $query .= " or $column = " .    
                     implode(" or $column =", $ids);    
       }    
   }    
   return $query;    
}    
   
?>