PHP PDO prepared statements

Question

I was told today that I should really be using PDO and prepared statements in my application. Whilst I understand the benefits, I am struggling to understand how I implement them into my workflow. Aside from the fact that it makes code much cleaner, should I have a specific database class which houses all my prepared statements or should I create one each time I want to run a query? I'm finding it very hard to understand when I should use a standard PDO query and when I should use a prepared statement. Any examples, tips or tutorial links would be greatly appreciated.

Answer 1

There are two great examples on the pdo::prepare() documentation.

I have included them here and simplified them a bit.

This one uses ? parameters. $dbh is basically a PDO object. And what you are doing is putting the values 150 and 'red' into the first and second question mark respectively.

/* Execute a prepared statement by passing an array of values */ $sth = $dbh->prepare('SELECT name, colour, calories                       FROM fruit                       WHERE calories < ? AND colour = ?');  $sth->execute(array(150, 'red'));  $red = $sth->fetchAll(); 

This one uses named parameters and is a bit more complex.

/* Execute a prepared statement by passing an array of values */ $sql = 'SELECT name, colour, calories         FROM fruit         WHERE calories < :calories AND colour = :colour';  $sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $sth->execute(array(':calories' => 150, ':colour' => 'red'));  $red = $sth->fetchAll();