What are the best practices for representing SQL within PHP code? [closed]

0

I've been programming PHP together with SQL (mainly MySQL) for a long time, I already have some experience, but I've never found a good solution for this: what is the best way to insert SQL into PHP code?

Normally what people do is insert the code in quotation marks, for example:

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

However, for logging purposes it is not very interesting because SQL appears the way 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 error prone.

Another syntax I've seen but I find it a bit bizarre is HEREDOC or NOWDOC:

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

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

Note that I'm not asking, for example, whether to use SQL flat, DQL or QueryBuilder; the question is about styles when representing a "pure" SQL in the middle of a PHP code:)

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

    
asked by anonymous 04.02.2014 / 14:19

4 answers

2

Each form has advantages and disadvantages.

Text file

At the time when I was developing a framework in PHP, I thought a lot about the possibility of storing queries 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 reading and processing 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 PHP file with queries in strings. heredoc is an interesting representation of this approach.

The advantage is that you can get an overview of queries , making SQL refactoring easier, and your code gets cleaner. The downside is that sometimes you need to keep switching files multiple times to see the contents of the variables.

Queries inline

Today I avoid to the utmost, in any language put the queries directly inside 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 "lost" queries in files and methods.

Second, because although it works well for queries very simple, it usually ends up "spoiling" the formatting of the code and "breaking" the flow of the code, disrupting its reading.

Representing SQL in String

To represent SQL in a PHP String, I would avoid to the maximum the way it concatenates line by line. This is asking for a tendinitis.

Using multi-line quotes, it does not look so ugly if you do not use tabs. Example:

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

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

The heredoc format seems to me to be the most interesting. First because there is no problem with the line break or formatting. Also, it allows you to copy and paste the query easily, which I consider important, since I do this frequently during development.

Once you get used to it a bit will not seem so strange. ;)

On the other hand, as mentioned in the question itself, there is still the Nowdoc format. According to the documentation, it works as single quotation marks, where the content is not parsed and therefore variables are not interpolated. The use of Nowdoc is interesting because it avoids unnecessary processing on "constant" strings, that is, it does not have PHP variables inside.

General considerations

Adopting a pattern where to store queries facilitates developers' understanding of the system and avoids the introduction of bugs , as there are no SQLs sprinkled by the code. p>     

04.02.2014 / 15:55
1

I used a normal tabbed form, but today I've used the dibi class a lot, it's an extremely powerful, lightweight and useful database wrapper and layer abbstraction ...

link

The class also supports fluent programming, which is extremely handy for maintenance, for example:

My normal select would look like this:

  

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

Do not dibi fluently you can use this way:

  

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

There's also a way to do it in a very practical way, like this:

  

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

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

Finally it's practical and I can change my db from PGSQL to MYSQL or SQL Server or others without having to change practically any code ...

I think it's worth checking the class is very good ... Strong hug.

    
04.02.2014 / 16:20
0

I always use this method:

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

I find it easier and less prone to error!

    
04.02.2014 / 14:21
0

I think there is not a more correct form, I usually write in a same line, some organize in several to make it easier to read, but if the topic is organization I would say that I like the way you write in Zend, so you write object oriented and at the end the result is a normal query in a single line:

Example

    
04.02.2014 / 14:25