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

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

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

Post 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?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
Deemo
Forum Contributor
Posts: 418
Joined: Sun Jan 18, 2004 11:48 am
Location: Washington DC

Post 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
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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

Post 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";
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 :)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

One armed space goat wrote: I like that method.
Providing you spell 'group' correctly - whoops
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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;
:)
Post Reply