By Michael Marr
Expert Author
Article Date: 2010-06-22
One of my fondest childhood memories revolves around my Grandmother's cooking. She was able to take out store bought hot dogs, boil them in a pot of water, and serve them up on piece of bread for many of my childhood lunches. Likewise, one my of fondest SQL memories is the first time I used conditional statements in my SQL to significantly shorten my overall code. Nothing better than hot dogs and SQL...
SQL conditional statements can be used anywhere to replace an IF/THEN or SWITCH statement you might otherwise use on the results of a SQL statement. However, an even bigger benefit comes from using conditional statements to handle the data from two adjoined tables to produce the output that otherwise would take a considerable amount of code to organize.Let's say we have the main content of our site in table1, and a secondary table called table2. Table2 links to table1's content, and sometimes will use that content verbatim. If table2 data exists, however, it does not copy table1's values.
table1
id
title
content | table2
id
origin_id
title
content |
$combined = array();
$query = "SELECT * FROM table1";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
$tmp = array();
$tmp['table'] = 1;
$tmp['id'] = $row['id'];
$tmp['title'] = $row['title'];
$tmp['content'] = $row['content'];
$combined[] = $tmp;
}
$query2 = "SELECT * FROM table2";
$result = mysql_query($query2);
while ($row = mysql_fetch_array($result))
{
$tmp = array();
$tmp['table'] = 2;
$tmp['id'] = $row['id'];
$origin = find_origin($row['origin_id'], $combined);
if ($origin != FALSE)
{
if (empty($row['title'])
$tmp['title'] = $origin['title'];
else
$tmp['title'] = $row['title'];
if (empty($row['content'])
$tmp['content'] = $origin['content'];
else
$tmp['content'] = $row['content'];
} else {
$tmp['title'] = $row['title'];
$tmp['content'] = $row['content'];
}
$combined[] = $tmp;
}
// $combined now has all items from table1 and table2
This obviously gets the job done, but I wasn't asking my grandma to fix me a six-course dinner, complete with filet mignon and truffles. Those simple and quick hot dogs did the trick, and so can a nice clean SQL statement for this block of code:
$query = 'SELECT IFNULL(table2.id, table1.id) as id,
IFNULL(table2.title, table1.title) as title,
IFNULL(table2.content, table1.content) as content
FROM table1 LEFT JOIN table2 ON
table1.id = table2.origin_id';
This SQL statement will generate the same output as the code above! The biggest key is using the right type of join, i.e. an outer join. We need this type of join because we want all the unique table1 rows, plus the related table1 row for each of the table2 rows. The IFNULL function checks to see if a table2 value exists, and if it does, uses that value. Otherwise, it uses the table1 value. For all the table1 unique rows, there will be no table2 values, and thus we'll be pulling the table1 values. For all of the table2 values, we'll only be pulling the table1 values when the table2 values do no exist.
So, we can take a long and cumbersome piece of code and, and with some clever SQL, make it short and sweet - just like Grandma.
