How to make SQL query more readable in PHP?

Question

When you have a long fields in SQL query, how do you 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');
    }

you've got some solid suggestions here - why not pick one as the accepted answer? — matt lohkamp, Apr 18 '13 at 23:15
Surely after 6 months an answer is warranted. Select one of the below suggestions. — philwinkle, Apr 29 '13 at 14:22

philwinkle · Answer 1 · 2013-12-09T04:32:34.357

35

I prefer Heredoc syntax, though Nowdoc would 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 with both is you can copy and paste straight SQL to and from this block without having to escape or format it. If you needed to include parsing, such as you would do with variables from a double-quoted string, you'd 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');
}

edited Dec 09 '13 at 04:32

answered Oct 08 '12 at 14:04

philwinkle

7,036
3
27
46

4

And, FWIW, Sublime Text 2 recognizes this syntax as SQL instead of a PHP string and switches the syntax highlighting intelligently. Pretty nice. – philwinkle Oct 08 '12 at 14:06
my vote is for this one - in some ways this is exactly what the heredoc/nowdoc syntax is made for, and giving your code editor a cue for how to apply syntax highlighting is a nice bonus. – matt lohkamp Apr 18 '13 at 23:15
3

Note that your examples will not work: the closing identifier (SQL) cannot have any indentation in front of it. – nullability Dec 06 '13 at 22:13
Good answer, but if the query is in some kind of nested if statements then the here doc terminator has be be at start of newline. which i think looks really weird. I love Heredoc syntax. – Abdul Rehman Dec 06 '16 at 05:55
Be aware of Heredoc and inserting parameters directly into a SQL Statements. You should always decouple statement and data because of SQL Injections. – R. Daumann Aug 20 '18 at 07:20
Hope we can copy paste directly the format SQL string in side this FOrmat – Anoop P S Apr 28 '21 at 07:46

score 11 · Accepted Answer · answered Oct 08 '12 at 14:03

11

You can concatenate it like this 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: Be sure while concatinating your query, don't forget to leave spaces before you start a new line between your double quotes, else you'll get query invalid error

answered Oct 08 '12 at 14:03

Mr. Alien

153,751
34
298
278

1

Concatenating SQL can lead to misplaced semicolons in PHP. It looks ugly to me - I'd prefer `sprintf` over this. – philwinkle Apr 29 '13 at 14:21
4

In PHP, you can concatenate with white-space over multiple lines. So, open a double quote on a line and end it in another line. No need to write $sql.= multiple times. This way, it looks clean. – Bimal Poudel Aug 24 '14 at 19:59
@Jobin gave a better answer. there is no need to concatenate . it's more prone to error, less efficient, and less likely to have useful syntax highlighting. – Symbolic May 13 '22 at 18:09

score 9 · Answer 3 · answered Oct 08 '12 at 14:03

9

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

answered Oct 08 '12 at 14:03

Jobin

8,238
1
33
52

This answer should have the big green checkmark. – Symbolic May 13 '22 at 18:09

score 1 · Answer 4 · answered Dec 16 '16 at 16:53

This is just another way.

Note that array join 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',
));

score 0 · Answer 5 · answered Nov 29 '12 at 13:03

<?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');
}
?>

I always write SQL queries in this format. – SandroMarques Feb 17 '23 at 09:04 — SandroMarques, Feb 17 '23 at 09:04

How to make SQL query more readable in PHP?

5 Answers5