What are the best practices of representing SQL within PHP code?

Question

PHP program together with SQL (mainly Mysql) for a long time, I already have some experience, however I never found a good solution for this: what is the best way to insert SQL within PHP code?

Usually what people do is insert the code in quotes, for example:

$sql = "
     SELECT *
     FROM usuarios u
     ORDER BY u.nome";

However, for logging purposes it is not very interesting, because SQL appears as it is in the code. To fix this, one solution would be to concatenate successive strings:

$sql = 
     "SELECT * ".
     "from usuarios u ".
     "ORDER BY u.nome";

However, it is impractical and subject to errors.

Another syntax I’ve seen but I think it’s kind of bizarre is HEREDOC or NOWDOC:

$sql = <<<EOT
    SELECT *
    from usuarios u
    ORDER BY u.nome
EOT;

I have also thought about putting the SQL in separate files, with their proper placeholders (for parameters, when using them with Doctrine2, for example), but I do not know if it is a common practice.

Note that I am not asking, for example, if I should use plain SQL, DQL or Querybuilder; the question is about styles when representing a "pure" SQL in the middle of a PHP code :)

Finally, what are the best practices in representing SQL within PHP code?

Answer 1

Each form has advantages and disadvantages.

Text file

At the time when I was developing a PHP framework, I thought a lot about the possibility of storing darlings in text files.

This would reinforce the separation of concepts, that is, I did not want to worry about the syntax of PHP mixed with SQL.

However, after considering the impact of read and process the files, I gave up the idea.

Separate PHP file

I used this approach in a project and found it very interesting. Each class had a respective PHP file with the darlings in strings. The heredoc is an interesting representation in this approach.

The advantage is that you get an overview of darlings, facilitating SQL refactoring, and your code is cleaner. The downside is that sometimes you need to keep switching file several times to see the contents of the variables.

Queries inline

Today I avoid to the fullest, in any language put the darlings directly within a function or method.

First because with Sqls distributed in the middle of the code, if the table changes, I often forget to update one of the darlings "lost" in files and methods.

Second, because, although it actually works well for darlings very simple, in general ends up "spoiling" the formatting of the code and "breaking" the flow of it, disturbing its reading.

Representing SQL in String

To represent SQL in a PHP String, I would avoid as much as possible the form that concatenates line by line. This is asking for a tendinitis.

Using multi-line quotes, it doesn’t look so ugly if you don’t use tab. Example:

<?php 
$sql = "
SELECT *
FROM usuarios u
ORDER BY u.nome";

The only String problem above is a line break at the beginning.

Already the format heredoc seems to me the most interesting. First because there is no problem of line breaking or formatting. Moreover, it allows you to copy and paste the query easily, which I consider important, as I often do during development.

Once you get a little used to it it won’t seem so strange. ;)

On the other hand, as mentioned in the question itself, there is still the format Nowdoc. According to the documentation, it works as simple quotes, where the content is not analyzed and thus, variables are not interpolated. The use of Nowdoc is interesting because it avoids unnecessary processing in "constant" strings, that is, there are no PHP variables inside.

General considerations

Adopt a standard from which to store darlings facilitates the understanding of the system by developers and avoids the introduction of bugs, since there are no Sqls spread by the code.

Answer 2

I used normal tabulated form, however today I have used the class "dibi" it is a wrapper and layer abbstraction database, extremely powerful, lightweight and useful...

http://dibiphp.com

The class also supports fluent programming, which is extremely practical to maintain, for example:

My normal select would be like this:

$sql = " SELECT * FROM usuarios u ORDER BY U.name";

No dibi so fluently you can use so:

$result = dibi::select('*')->from('usuarios')->as('u')->orderby('u.name')->fetchAll();

There is also how to do it in a very practical way, like this:

$resutlado = dibi::query('SELECT * FROM [users] AS [u] ORDER BY [u]. [name]')

Note that the second time I put it in square brackets because the postgree uses the columns as "u"." name" then dibi checks if vc is using PGSQL or MYSQL since it is abstraction and if it is mysql it takes the [] if it is PGSQL it converts the [] to ""

Anyway it is practical and I can change my db from PGSQL to MYSQL or SQL Server or others without having to change virtually any code...

I think it’s worth it you check the class is very good... Strong hug.

Answer 3

I always use this method:

$sql = "
 SELECT *
 FROM usuarios u
 ORDER BY u.nome";

I find it easier and less prone to error!

Answer 4

I believe that there is no more correct form, I usually write in the same line, some organize in several to facilitate reading, but if the theme is organization I would say I like the way it is written in Zend, so you write object oriented and in the end the result is a normal query in a single line:

Example