How to make SQL query more readable in PHP?

Question

When you have long fields in a SQL query, how to make it more readable?

For example:

public function findSomethingByFieldNameId($Id) { $sql = "SELECT field1, field2, field3 as Field3_Something, field4, field5, field6, field7, field8, field9 FROM table JOIN table2 AS TNS ON TNS.id = table.id WHERE something = 1"; return $this->db->fetchData($sql, null, 'all'); } 

Answer 1

You can configure it to make it more readable:

 $sql = "SELECT field1, field2, field3 as Field3_Something,"; $sql.= " field4, field5, field6, field7, field8, field9"; $sql.= " FROM table JOIN table2 AS TNS ON TNS.id = table.id"; $sql.= " WHERE something = 1"; 

Note Make sure that when concatenating a query, be sure to leave spaces before starting a new line between your double quotes, otherwise you will receive an invalid query error.

Answer 2

I prefer the Heredoc syntax, although Nowdoc will also work for your example:

Heredoc:

http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc

Nowdoc: http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.nowdoc

The advantage of both is that you can copy and paste direct SQL into and out of this block without having to run or format it. If you need to enable parsing, for example, you will use variables from a string with two quotes, you should use Heredoc. Nowdoc behaves like single quotes.

Nowdoc:

 public function findSomethingByFieldNameId($Id) { $sql = <<<'SQL' SELECT field1, field2, field3 as Field3_Something, field4, field5, field6, field7, field8, field9 FROM table JOIN table2 AS TNS ON TNS.id = table.id WHERE something = 1 SQL; return $this->db->fetchData($sql, null, 'all'); } 

Heredoc:

 public function findSomethingByFieldNameId($Id) { $sql = <<<SQL SELECT field1, field2, field3 as Field3_Something, field4, field5, field6, field7, field8, field9 FROM table JOIN table2 AS TNS ON TNS.id = table.id WHERE something = '$Id' SQL; $sql = mysql_real_escape_string($sql); return $this->db->fetchData($sql, null, 'all'); } 

Answer 3

I use the free tool @ http://www.sqlinform.com

 <?php public function findSomethingByFieldNameId($Id) { $sql = "SELECT field1 , field2 , field3 AS Field3_Something, field4 , field5 , field6 , field7 , field8 , field9 FROM TABLE JOIN table2 AS TNS ON TNS.id = table.id WHERE something = 1"; return $this->db->fetchData($sql, null, 'all'); } ?> 

Answer 4

  $sql = "SELECT field1, field2, field3 as Field3_Something, field4,.... FROM table JOIN table2 AS TNS ON TNS.id = table.id WHERE something = 1"; 

Answer 5

 <?php public function findSomethingByFieldNameId($Id) { $sql = "SELECT field1, field2, field3 as Field3_Something, field4, field5, field6, field7, field8, field9 FROM table JOIN table2 AS TNS ON TNS.id = table.id WHERE something = 1"; return $this->db->fetchData($sql, null, 'all'); } ?> 

Answer 6

This is another way.

Note that array concatenation is faster than string concatenation.

 $sql = join(" \n", Array( 'SELECT ', ' [...fields...]', ' [...more fields...]', 'FROM table', 'JOIN table2 AS TNS ON TNS.id = table.id', 'WHERE something = 1', ));