Why put SQL statement in a variable before using mysql_query()?

Question

I'm been working with PHP on and off for a bit now, and I've seen plenty of code both ways:

$sql = "SELECT …"; mysql_query($sql); 

vs

mysql_query("SELECT…"); 

Is there a reason for separating the two, beyond being able to also pass in something such as $con?

Answer 1

There are a few reasons to do that:

1. Readability: it may not apply with a simple mysql_query("SELECT…"); (by the way, aren't you expected to use PDO? It's not year 1998!). But you see the usefulness of this when it comes to multiline arguments. When you have several of these, things become even harder to read.

2. Refactoring: it's really minor, but may apply as well. Imagine you will get the query from a config file instead of hardcoding it. It would probably be easier/more readable to have a variable defined first, then used as an argument.

3. Debugging: imagine the query is generated (the thing you must never done, but well, it's another subject). If you want to set the breakpoint after the query is generated but before the query is done, it would be possible to do only if the query is actually assigned to a variable.

4. Tracing: this may be a temporary code, and the author may know that he will add tracing of a query later.

In other words:

$sql = "SELECT …"; mysql_query($sql); 

will become:

$sql = "SELECT …"; $this->trace(123, TRACE_INFORMATION, 'Running the query ' . $sql . '.'); mysql_query($sql); 

Answer 2

Possibly because at one point the code looked like this:

$sql = "SELECT …"; echo $sql; //why is this <censored> query not working??????? mysql_query($sql); 

Otherwise, there's no compelling technical reason to go one way or the other. I personally find using the temporary variable makes the code a tad bit easier to maintain if down the road you need to expand the query. Larger queries seem easier to look at if they arent already embedded into a function call. But thats just my personal preference.

Answer 3

Most significant, it makes it possible to output the exact SQL statement; e.g.

$S_BAG = "select * from BAG where NUMBER=" . $N ; DebugLog( "S_BAG = " . $S_BAG ); mysql_query( $S_BAG ); 

Also, when things get complex you may need to construct the SQL statement in parts; e.g.

$S_SELECT = "select ..." if ( ... ) $S_WHERE = "where ..."; else $S_WHERE = "where ..."; $S_ORDER = "order by ..." $SQL = $S_SELECT . " " . $S_WHERE . " " . $S_ORDER ; DebugLog( "SQL", $SQL ); mysql_query( $SQL );