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

Avoid SELECT *

Use ENUM over VARCHAR

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

Answer 2

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

See the section at the bottom on parameterized statements.

Answer 3

This blog post right here is a good way to do it.

http://www.communitymx.com/content/article.cfm?page=1&cid=A5B54EAAE1BC138F

Personally, I don't include the empty newlines.

Answer 4

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 5

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 6

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 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