Comparing PDO::query, exec, and prepare statements
Following table shows a brief comparison of PDO::exec, PDO::query and PDO::prepare:
exec() | query() | prepare() | |
|---|---|---|---|
| Returns | Number | PDOStatement | PDOStatement |
| Data escape inside the query | Required | Required | Not required |
| SQL injection safe | No | No | Yes |
| Fetch data with SELECT statement | No | Yes | Yes |
| Performance | Normal | Normal | Better |
| Secure | No | No | Yes |
| Safe with web forms data | No | No | yes |
Sending SQL Queries
Before proceeding, we first connect to the database:
<?php
$user = 'Brain';
$pass = 'Bell';
$dsn = 'mysql:host=localhost;dbname=brainbell;charset=utf8';
$dbh = null;
try {
$dbh = new PDO ($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connection Failed: '.$e->getMessage();
exit;
}
See Connecting different databases via PDO.
We can use $dbh->exec, $dbh->query and $dbh->prepare methods to send SQL queries.
PDO::exec
PDO::exec executes an SQL statement and returns the number of affected rows. You can use this method to execute any SQL query where you don’t expect a result set, so this method must not use to SELECT rows from a DB.
PDO::exec – updating records:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
$sql = 'UPDATE lessons SET pdate="2019-10-01"
WHERE pdate="2010-10-10"';
$affectedRows = $dbh->exec($sql);
echo "Rows updated: $affectedRows";
PDO::exec – deleting records:
As we discussed in Connecting Databases via PDO tutorial that if there’s a problem executing the query (a typing mistake in the SQL statement), the PDO will throw a PDOException for you to catch.
<?php
//...
$sql = 'DELETE FROM lessons
WHERE pdate="2010-10-10"';
try{
$dbh = new PDO ($dsn, $user, $password);
$affectedRows = $dbh -> exec($sql);
echo "Rows deleted: $affectedRows";
}
catch (PDOException $e) {
die ( $e->getMessage() );
}
PDO::exec – inserting record:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
$sql = 'INSERT INTO lessons (name, pdate)
VALUES ("PHP", "2019-10-01"),
("MySQL", "2019-10-02")';
$dbh -> exec($sql);
PDO::query
PDO::query executes an SQL statement, returning a result set as a PDOStatement object. You must properly escape query data if it is coming from an untrusted source to avoid SQL injection. To check the affected rows we need to use the PDOStatement::rowCount() method.
PDO::query – update records:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
$sql = 'UPDATE lessons SET pdate="2019-10-01"
WHERE pdate="2010-10-10"';
$sth = $dbh -> query($sql);
echo 'Rows updated: '. $sth -> rowCount();
PDO::query – delete records:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
$sql = 'DELETE FROM lessons
WHERE pdate="2010-10-10"';
$affectedRows = $dbh -> query($sql);
echo 'Rows deleted: '. $sth -> rowCount();
PDO::query – select a record:
You also can use SELECT statement to fetch rows from the database (which is not possible with PDO::exec method):
<?php //... $dbh = new PDO ($dsn, $user, $password); $sql = 'SELECT * FROM lessons WHERE id=5 LIMIT 1'; $sth = $dbh -> query($sql); $row = $sth -> fetch(); //Fetch a single row/record echo $row['title']. '<br>' .$row['content'];
PDO::query – select multiple records:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
$sql = 'SELECT * FROM lessons LIMIT 10';
$sth = $dbh -> query($sql);
while ( $row = $sth -> fetch() ) {
echo $row['title']. ',' .$row['content'].'<br>';
}
PDO::prepare
PDO::prepare prepares a statement for execution and returns a PDOStatement object. You must use prepared statements if data is coming from an untrusted source, such as from a web form, to prevent an SQL injection attack.
We use the PDOStatement::bindParam and bindValue methods to bind parameters to the form variables’ names. After binding the form parameters and executing the query you can run the PDOStatement methods, such as rowCount(), fetch or fetchAll etc.
PDO::prepare – update records:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
//Form data
$oldDate = $_POST['edate'];
$newDate = $_POST['ndate'];
$sql = 'UPDATE lessons SET pdate = ?
WHERE pdate = ?';
$sth = $dbh -> prepare($sql);
$sth -> bindParam(1, $oldDate);
$sth -> bindParam(2, $newDate);
$sth -> execute();
echo $sth->rowCount();
PDO::prepare – select records:
Selecting records from the database using PDO::prepare:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
//Form data
$startDate = $_POST['sdate'] ?? null;
$endDate = $_POST['edate'] ?? null;
$sql = 'SELECT * FROM lessons
WHERE pdate > ? AND pdate < ?';
$sth = $dbh -> prepare($sql);
$sth -> bindParam(1, $startDate);
$sth -> bindParam(2, $endDate);
$sth -> execute();
//$rows = $sth -> fetchAll();
while ( $row = $sth -> fetch() ) {
echo $row['title']. ',' .$row['content'].'<br>';
}
PDO::prepare – insert records:
Use prepare() to insert a record:
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
//Form data
$date = $_POST['date'] ?? null;
$name = $_POST['name'] ?? null;
if (!($date && $name)){
die ('Error! Date or Name not submitted');
}
$sql = 'INSERT INTO lessons (name, pdate) VALUES (?,?)';
$sth = $dbh -> prepare($sql);
$sth -> bindValue(1, $name);
$sth -> bindValue(2, $date);
$done = $sth -> execute();
if (!$done) {
//record not saved to db
echo $sth->errorCode();
}
PDOStatement::bindParam
The bindParam method can be used with positioned (anonymous) and named placeholders. When used with positioned (anonymous) placeholders, the first argument is a number, counting from 1, representing the placeholder’s position in the SQL statement. With named placeholders, the first argument is the named placeholder as a string. The second argument is the value you want to insert in the query.
Example: Using positioned/anonymous placeholders
<?php //... $dbh = new PDO ($dsn, $user, $password); $sql = 'INSERT INTO lessons (name, pdate) VALUES (?,?)'; $sth = $dbh -> prepare($sql); $sth -> bindParam(1, $name); $sth -> bindParam(2, $date); $done = $sth -> execute();
Example: Using named placeholders
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
$sql = 'INSERT INTO lessons (name, pdate) VALUES (:NAME,:PDATE)';
$sth = $dbh -> prepare($sql);
$sth -> bindParam(':NAME', $name);
$sth -> bindParam(':PDATE', $date);
$done = $sth -> execute();
PDOStatement::bindValue
The bindValue method works the same as the bindParam method, both can be used with positioned (anonymous) and named placeholders with a little difference:
bindParam(), the second argument must be a variable. You can not directly input a value, for example, you must write a variable $name (or any other variable) but not “PHP” (or any other value).
bindValue(), the second argument can be a value or variable.
<?php
//...
$dbh = new PDO ($dsn, $user, $password);
$sql = 'INSERT INTO lessons (name, pdate) VALUES (:NAME,:PDATE)';
$sth = $dbh -> prepare($sql);
$sth -> bindValue(':NAME', $name);
$sth -> bindValue(':PDATE', $date);
$done = $sth -> execute();
Working with Databases: