How to avoid Heredoc SQL statements

Question

How can I avoid using Heredoc string delimiters for SQL statements? Or maybe a better question, what is the modern way to create and call SQL statements in PHP?

I am relatively new to php. I often saw something like this in the projects I was working on:

        $sql = <<<SQL
SELECT
    `data`.`key`,
FROM `tableName`
ORDER BY `data`.`key`
SQL;

When researching more about Heredoc (I had to use the SO symbol reference post to find what it was called) I got the feeling that there are better ways to make SQL statements. For exapmle SO users would say they used Heredoc for SQL "back in the day".

Using Heredoc seems to not be the best solution as little formatting changes can mess it up. Not being able to indent the closing tag makes the code harder to read and format. It also seems like just using Heredoc encourages littering the code with SQL statements and logic.

Is that just the 'way it's done' or is there a better way?

This question about managing your SQL queries is from 6 years ago. PDO or an ORM are mentioned in this answer from 5 years ago. Are these still the best way?

The heredoc statement is just a way to quote your string without using quotes. You can use any method you like. Heredoc is just common when you have multiple lines in a string. — Jonathan Kuhn, Feb 20 '14 at 18:30
It's a good thing SQL doesn't care about such trite formatting. — user2864740, Feb 20 '14 at 18:30
You don't have to use a heredoc - you could just use a quoted string instead. The only thing the heredoc gains you is not having to internally escape quotes. But you shouldn't often need to do that anyway if you are using a prepared statment — Michael Berkowski, Feb 20 '14 at 18:30
Smart editors will see that SQL is the heredoc delimiter and do the appropriate syntax highlighting. It's really just about readability for longer queries. Especially if you've imposed limits like an 80 character line. — miken32, Feb 20 '14 at 18:34

score 3 · Answer 1 · answered Feb 20 '14 at 18:30

There is nothing wrong with your HEREDOC statement, but here is another way to create the $sql variable by quoting the string.

$sql = "SELECT `data`.`key`, FROM `tableName` ORDER BY `data`.`key`";

In the case of longer statements where additional readability is needed, you can take advantage of the fact that whitespace is ignored:

$sql = "SELECT `data`.`key`, 
    FROM `tableName`
    ORDER BY `data`.`key`";

Also consider using PDO for database interactions in an object-oriented manner: http://www.php.net/PDO

How to avoid Heredoc SQL statements

1 Answers1