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