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

Answer 1

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

Answer 2

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