What is a Prepared Statement?
The MySQL Manual states The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.
So far, so good. Well there is also a performance issue to consider too. From the same source The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.
So it is a two step process. Set up the query as a template and then plug in the value. If you need to reuse the query, just plug in a new value into the template.
So lets look at how it is done.
On PHP.NET, there are a lot of really great examples. Question marks (?) are used as placeholders that will be filled in at execution time.
$sth = $dbh->prepare('SELECT name, colour, calories
WHERE calories < ? AND colour = ?');$sth->execute(array(150, 'red'));$red = $sth->fetchAll();$sth->execute(array(175, 'yellow'));$yellow = $sth->fetchAll();
So that is the basics. But what do they look like with the other two extensions?
So what does the MySQLi version look like? Once again question marks are used as placeholders.
$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);$code = 'DEU';$language = 'Bavarian';$official = "F";$percent = 11.2;/* execute prepared statement */$stmt->execute();printf("%d Row inserted.\n", $stmt->affected_rows);
But what is that sssd stuff? That is where you declare the type of variable you are want to use. Use 's' for string, 'i' for integer, 'd' for double, and 'b' for a blob (binary large object). So you get the advantage of type checking.
The much newer X DevAPI is for the new X Protocol and the MySQL Document Store. Unlike the other two examples it is not Structured Query Language (SQL) based.
$res = $coll->modify('name like :name')->arrayInsert('job', 'Calciatore')->bind(['name' => 'ENTITY'])->execute();
$res = $table->delete()->orderby('age desc')->where('age < 20 and age > 12 and name != :name')->bind(['name' => 'Tierney'])->limit(2)->execute();
Note that this is not an object relational mapper as it is the protocol itself and not something mapping the object to the SQL.
So now you know how to use prepared statements with all three PHP MySQL Extensions.