TalkPHP - Indenting SQL

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - Indenting SQL (http://www.talkphp.com/mysql-databases/2291-indenting-sql.html)

Haris

02-20-2008 09:42 AM

Indenting SQL

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

Francisco

12-16-2009 10:55 AM

poor example, but here is how i do it.

Code:

SELECT t1.col1, t1.col2, t3.col3

  FROM tab1 as t1, tab2 as t2

       LEFT JOIN tab3 as t3 on t3.colx = t1.col1

 WHERE t1.col1 = cond1

   AND t1.col2 = cond2

 GROUP BY t1.col1, t1.col2

 ORDER BY t1.col1, t1.col2 ASC

HAVING func(t1.col1)

chrisadam

09-14-2010 09:19 AM

Indenting SQL

SQL standard

In general the SQL92 standard can be followed. Support for this in various databases are in most cases complete. This document will explain in which cases the standard cannot be followed.

__________________________________________________ _____________

Want to get-on Google's first page and loads of traffic to your website? Hire a SEO specialist from Ocean Groups seo specialist

All times are GMT. The time now is 09:46 PM.