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);
I 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.
