PHP MYSQL PDO Prepared statements examples

To avoid injections and vulnerabilities when doing MYSQL interaction in PHP use PDO and prepared statements. It’s a bit more work but is the safest way to ensure you are not open to an attack.

Setup the connection

First you want to define your MYSQL credentials and database as well as some PDO attributes.

$db = new PDO('mysql:host=localhost;dbname=DATABASENAME;charset=utf8mb4', 'USERNAME', 'PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

MYSQL PDO SELECT query with prepared statement

The following code selects the town value from the database where id = 472

$id = '472';
$statement = $db->prepare("SELECT `town` FROM `user_details` WHERE `id` = :id");
$statement->execute(array(':id' => $id));
$row = $statement->fetch();
$town = $row['town'];

MYSQL PDO INSERT query with prepared statement

The following code  inserts Ryan Smith into the database for first_name and last_name where id = 472

$id = '472';
$first_name = 'Ryan';
$last_name = 'Smith';
$statement = $db->prepare("INSERT INTO `user_details` (`first_name`, `last_name`) VALUES (:firstname, :lastname) WHERE `id` = :id");
$statement->execute(array(':firstname' => $first_name, ':lastname' => $last_name, ':id' => $id));

MYSQL PDO UPDATE query with prepared statement

The following Updates email_verified to 1 for where id = 472

$id = '472';
$is_verified = 1;
$statement = $db->prepare("UPDATE `user_details` SET `email_verified` = :isverified WHERE `id` = :id");
$statement->execute(array(':id' => $id, ':isverified' => $is_verified));

MYSQL PDO Check if entry exists query with prepared statement

The following checks the database for a row where id = 472

$id = '472';
$statement = $db->prepare("SELECT `id` FROM `user_details` WHERE `id` = :id");
$statement->execute(array(':id' => $id));
$row = $statement->fetch();
$count = $statement->rowCount();
   if ($count == 0) {
//There is no entry for id = 472
    } else {
//There is an entry for id = 472
    }

MYSQL PDO for all SELECT prepared statement

The following code example SELECTS ALL emails and email_verified values from the database then it echos them out in a formatted fashion.

$statement = $db->prepare("SELECT `email`, `email_verified` FROM `user_details`");
$result->execute();
while ($row = $result->fetch(PDO::FETCH_ASSOC))
{
$email = $row['email'];
$verified = $row['email_verified'];
echo "".$verified."  email: ".$email."<br>";
}