Best practice for writing sql statements in php

Question

Can someone point me towards a resource or show me an example of a good way of writing sql statements in php.

Most statements seem so ugly and unreadable.

Answer 1

1. Use prepared statements
2. Feel free to format your code

Code sample:

$stmt = $pdo->prepare('
    SELECT ...
    FROM   ...
    JOIN   ...
    JOIN   ...
    WHERE  ... AND abc = :abc AND def = :def
');

$stmt->execute(array(
    'abc' => 'abc value',
    'def' => 'def value'
));

Answer 2

Avoid SELECT *

Use ENUM over VARCHAR

Prepared Statements (PHP's PDO http://www.php.net/manual/en/intro.pdo.php)

Answer 3

I use a MySQL Client to work on my queries (SQLWave). Very handy tool that lets you do queries and save them as SQL, it will output a file like this:

/*
get-all-tags.sql

get all tags from t.tags, combine info with t.taglink to 
2012-12-18: updated blabla

*/
select tags.*,group_concat(subid) as subs from t.tags

    join t.taglink on taglink.id=tags.id

group by id
order by category,tag,subid

It's the layout I prefer, but you can use your own preference of course. The query can then be inserted in your code like this:

mysql_query(file_get_contents("sql/get-all-tags.sql"));

etc. You can also dynamically alter the content of the file 'get-all-tags' by replacing strings with values you might need.

NOTE!! I'm NOT saying this is fast. The is a lot of unneeded overhead in this. But if you want to go for the sole purpose of keeping your PHP code clean and clear this is a very good alternative.

For better performance you could 'compile' your PHP code into code you publish by replacing "file_get_contents("sql/get-all-tags.sql") with the actual contents of that file before you upload your stuff.

So, again, this is not fast code, but very readable!

Of course you can put the same format straight into your PHP code, without copying it from a client program:

<?
    $result=mysql_query("
       select tags.*,group_concat(subid) as subs from t.tags
          join t.taglink on taglink.id=tags.id
       group by id
       order by category,tag,subid
    ");
?>

No overhead, no accessing files, so it's (relatively) fast

Answer 4

http://en.wikibooks.org/wiki/Programming:PHP:SQL_Injection

See the section at the bottom on parameterized statements.

Answer 5

Consider prepared statements

$stmt = $db->prepare("SELECT col1, col2, col3 FROM tbl WHERE col1 = ? AND col2 = ?");
$stmt->bindParam(1, $col1);
$stmt->bindParam(2, $col2);

Or using sprintf().

$sql = sprintf("SELECT col1, col2, col3 FROM tbl WHERE col1='%s' AND col2='%s'",
    mysql_real_escape_string($col1),
    mysql_real_escape_string($col2)
);

Either way, you'll end up with one large unconcatenated SQL string which is more readable.

Answer 6

I personally like to seperate my SQL out like this:

SELECT * FROM `table` a
INNER JOIN `table2` b
ON a.`id`=b.`id`
WHERE b.`colour` = "frink"

So I like to put the seperate commands on new lines which keeps things easy to read.

Answer 7

PHP Security Guide: SQL Injection. The book Essential PHP Security is also a quick, easily digested read.

Answer 8

SQL, IMO, often seems ugly and unreadable because of the tight coupling with other layers of code. Complex SQL queries often need to be conditionally built and when you start throwing HTML in between the SQL query, in addition to business logic, etc the result is unreadable code.

Consider using a DAL (data access layer) or more formally, the table data gateway or just a SQL gateway. It will provide incredible advantages:

1. Abstracts the SQL code making switching/porting to another RDBMS much easier
2. Isolates the SQL code from your business logic and templates, etc making the code much more readable.

Cheers, Alex