Page 1 of 2

Anybody tell me the best way to indent/format queries in PHP

Posted: Wed Nov 02, 2005 1:04 pm
by raghavan20
Hello all,

I am recently facing problems indenting queries for processing by PHP. I am looking for suggestions/best practices on how to format/indent queries in PHP?

for ex:
query:

Code: Select all

select field_1, field_2 
from
table_1 as a
left join table_2 as b on a.field_3 = b.field_1
where a.field2 > 30
group by a.field_2
order by a.field_1
How do you write the above in PHP as value of $query PHP local variable?

Posted: Wed Nov 02, 2005 1:24 pm
by Chris Corbyn

Code: Select all

$query = "
select
    field_1,
    field_2
from
    table_1 as a
left join
    table_2 as b on a.field_3 = b.field_1
where
    a.field2 > 30
group by
    a.field_2
order by
    a.field_1";
Just the way I do it... I find it easier to work through on some of the loooooooooong queries I write.

Posted: Wed Nov 02, 2005 2:14 pm
by Deemo
what i tend to do is make all SQL keywords all in caps. so a simple query would be

SELECT * FROM table WHERE column = 1

This way you can easily tell which one is a keyword and which one is one of your own words

Posted: Wed Nov 02, 2005 2:33 pm
by foobar
Deemo wrote:what i tend to do is make all SQL keywords all in caps. so a simple query would be

SELECT * FROM table WHERE column = 1

This way you can easily tell which one is a keyword and which one is one of your own words
Exactly what I do, and it serves me well. You can clearly distinguish SQL from custom names that way.

Re: Anybody tell me the best way to indent/format queries in

Posted: Wed Nov 02, 2005 3:05 pm
by RobertGonzalez
raghavan20 wrote:I am looking for suggestions/best practices on how to format/indent queries in PHP?

Code: Select all

select field_1, field_2 
from
table_1 as a
left join table_2 as b on a.field_3 = b.field_1
where a.field2 > 30
group by a.field_2
order by a.field_1
This is how I write mine...

Code: Select all

$sql = "SELECT field_1, field_2 
        FROM table_1 AS a
        LEFT JOIN table_2 AS b 
            ON a.field_3 = b.field_1
        WHERE a.field2 > 30
        GROUP BY a.field_2
        ORDER BY a.field_1";

Posted: Wed Nov 02, 2005 3:32 pm
by timvw
It depends on how long the query is.. In both cases i prefer to write the SQL words in uppercase.

Code: Select all

$query = "SELECT foo FROM bar";

$query2 =<<<SQL
SELECT foo
FROM bar
INNER JOIN whiiii USING (blah)
WHERE x > Y 
AND location in (
  SELECT location
  FROM fooloations
  WHERE xx = foo.id
)
UNION
SELECT foo
FROM bar2
SQL;

Posted: Wed Nov 02, 2005 3:43 pm
by Chris Corbyn
I don't like having to constantly hit shift or capslock while I'm typing lol... I practically always do them all in lowercase. Granted, really short queries just get bundled onto one single line.

Posted: Wed Nov 02, 2005 3:48 pm
by RobertGonzalez
I agree with the short query thing...

Code: Select all

$sql = "SELECT * FROM table";
But I always use uppercase to denote SQL commands. I don't know, I guess it is just a habit.

Posted: Wed Nov 02, 2005 3:49 pm
by timvw
My fingers are long enough to reach at the shift keys ;) What i don't like is that i have to move my hands from the keyboard to the mouse ;) It slows me down. That is why i like CLI programs :)

Posted: Wed Nov 02, 2005 4:21 pm
by pickle
My 2 cents

Code: Select all

SELECT 
   field_1, 
   field_2
FROM
   table_1 as a
LEFT JOIN 
   table_2 as b 
   ON 
   a.field_3 = b.field_1
WHERE 
    a.field2 > 30
GROPU BY 
    a.field_2
ORDER BY 
   a.field_1

Posted: Wed Nov 02, 2005 5:25 pm
by Luke
pickle wrote:My 2 cents

Code: Select all

SELECT 
   field_1, 
   field_2
FROM
   table_1 as a
LEFT JOIN 
   table_2 as b 
   ON 
   a.field_3 = b.field_1
WHERE 
    a.field2 > 30
GROPU BY 
    a.field_2
ORDER BY 
   a.field_1
I like that method.

Posted: Wed Nov 02, 2005 5:29 pm
by pickle
One armed space goat wrote: I like that method.
Providing you spell 'group' correctly - whoops

Posted: Wed Nov 02, 2005 7:08 pm
by Ambush Commander
Heh... at least it wasn't spelled GROPE...

My two cents: I generally use `backticks` on fields even when they're not strictly necessary.

Posted: Thu Nov 03, 2005 1:54 am
by raghavan20

Code: Select all

function somefunction(){
	if (somecondition){
		$query = "
		select 
			field_1, 
			field_2 
		from 
			table_1 as a 
		left join 
			table_2 as b on a.field_3 = b.field_1 
		where 
			a.field2 > 30 
		group by 
			a.field_2 
		order by 
			a.field_1"; 
		$result = mysql_query($query);
	}
}


In a situation like the above, heredocs won't work I suppose. It's always a pain to indent in this kind of situation in editors other than Dreamweaver where you have to use so many tabs to get the query in desired format.

Posted: Thu Nov 03, 2005 2:51 am
by Jenk
The "official" way to do it is CAPS for all keywords and to backtick (`) all your table names and column headings and to line break before every key word.

Code: Select all

SELECT `column1`, `column2`, `column3`
FROM `table`
WHERE `column4` = 'value'
GROUP BY `column1`
LIMIT 20;
:)