Indenting SQL

Haris · 02-20-2008, 09:42 AM

How do you indent your SQL?

I think there might be a proper standard since Wildhoney separates every clause on different line.

Gareth · 02-20-2008, 02:01 PM

sql Code:

SELECT * FROM
    sui_users 
WHERE 
    username = '$username'
AND 
    pass = '$md5password'

SOCK · 02-20-2008, 02:22 PM

Just hit your <enter> key and use the spacebar to indent. I've not seen a standard per se as to how to properly format SQL statements, but it makes it much more clear (and easy to edit) if it's not all bunched together.

Pete · 02-20-2008, 03:58 PM

I tend to put a tab line in and start each part of the statment on the tab line so if ofen looks like:

Code:

   $myquery=    "SELECT * FROM table
                WHERE something ='something'"

so like SOCK says but use the tab button, I like to have it in a line so its line1: selecting what, line2: criteria or results, and line3+: my orderby/sortby.

obviously if its a more complex query it changes with joins and what not but thats my basic format for queries.

SOCK · 02-20-2008, 07:38 PM

Now, if you're talking about SQL within a PHP script, I find the overall best solution (not in every case, mind you) is to use HEREDOC syntax, e.g.

PHP Code:


			

$sql = <<< END_SQL
SELECT
 column1
, column2
, date_column
FROM sometable
WHERE pkID = 1024
END_SQL;

The limitation is that the end statement (in this case END_SQL, can be anything you want) has to be on a non-indented line. So it's great for a script that doesn't have a bunch of conditional blocks that have to be intented. Unfortunately the syntax highlighter has a problem so it doesn't 'pop' as it should, but it works great. Easy to edit, not to mention allowing you the ability to have a HUGE multi-table JOIN that you can actually decipher.

Second best method, IMHO is to simply use PHP's ability to ignore whitespace as Pete pointed out.

PHP Manual Language Reference | Types : Strings | HEREDOC syntax

Nor · 02-29-2008, 05:19 PM

Heredocs aren't great because when you close a heredoc string you must not have any white space in front of it which is a problem is most cases when trying to make your code look clean.

Alan @ CIT · 02-29-2008, 06:07 PM

I just tend to split mine over multiple lines if it's a long query to make it more readable than 1 long line.

For example:

Code:

SELECT `a`.`articleid`, `a`.`title`, `a`.`content`, `m`.* 
FROM `articles` AS `a` 
INNER JOIN `authors` AS `m` 
ON a.authorid = m.authorid 
WHERE (m.name = 'Alan') AND (a.created > 1204205135) 
ORDER BY `title` ASC

As long as it's readable it doesn't really matter how you format it

Alan

wGEric · 02-29-2008, 11:22 PM

Here's how I do it.

sql Code:

SELECT col1, col2
    FROM TABLE
    WHERE col = 1
        AND col2 = 'foo'
    ORDER BY col3 ASC
    LIMIT 20