Cryptic SQL statements

One of the main goals of programming is to write code that others can understand. A computer never has problems understanding what you mean within your code, it just does what is written. Humans on the other hand need to understand what you have written and it is therefore much more important to write code for others to read. The following is example code that is similar to how many may write SQL statements.

$sql = "SELECT l.*, cl.*, cat.*, u.username AS username, u.name AS owner, img.filename AS link_image FROM #__mt_links AS l" . "\n LEFT JOIN #__mt_cl AS cl ON cl.link_id = l.link_id " . "\n LEFT JOIN #__users AS u ON u.id = l.user_id " . "\n LEFT JOIN #__mt_cats AS cat ON cl.cat_id = cat.cat_id " . "\n LEFT JOIN #__mt_images AS img ON img.link_id = l.link_id AND img.ordering = 1 " . "\n WHERE link_published='1' && link_approved='1' && cl.cat_id = " . $database->quote($cat_id);

We find the above code very confusing. It is hard to read and gather what is actually going on. Only after some time will you be able to work out what everything means. Here is an improved version of the above code:

$sqlStatement = " SELECT products.*, category_to_products.*, categories.*, users.username AS username, users.name AS owner, images.filename AS image_filename FROM {TABLE_PREFIX}_products AS products LEFT JOIN {TABLE_PREFIX}_category_to_products AS category_to_products ON category_to_products.product_id = products.id LEFT JOIN {TABLE_PREFIX}_categories AS categories ON _category_to_products.category_id = categories.id LEFT JOIN {TABLE_PREFIX}_users AS users ON users.id = products.user_id LEFT JOIN {TABLE_PREFIX}_images AS images ON images.products_id = products.id AND images.ordering = 1 WHERE link_published = 1 AND link_approved = 1 AND categories.id = " . $database->quote($category_id);

The above code is an example of a better structured SQL statement. It doesn't matter how you format SQL statements as long as they are easy to read.