300+ free video programming tutorials ― Learn for free!

PHP Tutorial: Prepared Statements Explained

PHP Prepared Statements: SELECT, UPDATE, DELETE, INSERT, INSERT MULTIPLE ROWS AND LAST INSERTED ROW ID

Author: Karl Hadwen — Category: PHP — Post Date: Nov 7th, 2016

In this blog post I want to show you the correct way to manage a database using PHP. More specifically I’ll be showing you how to use PHP prepared statements using a MySQL backend. I’ve done a bunch of video tutorials on this over on my YouTube channel Cognitive Surge (subscribe here!) so if you learn by watching then go ahead and watch those videos. If you’re just here for a quick fix then feel free to just copy and paste my code, but be sure to read through it and make sure that you have a good understanding as to what exactly is going on within the code. Let’s get into it!

PHP’s prepared statements are used for database access and they work in way that helps secure your database queries. If you’re working with a large-scale application, then I recommend that you just forgot about any other ways to connect to a database and just use prepared statements.

Setting Up Our PDO Database Connection

Here we just setup a bunch of PHP constants and then we create a new PDO database object that we use to connect to our database. We can also setup the return time, so we can return an object or an array.

define('DB_TYPE', 'mysql');
define('DB_HOST', 'localhost');
define('DB_NAME', 'pod_tutorial');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_CHARSET', 'utf8');
$db = new PDO(DB_TYPE . ':host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET, DB_USER, DB_PASS);

Selecting database data using PHP Prepared Statements

$sql = "SELECT username, email_address, location, full_name, description FROM users
            WHERE location = :location";
$query = $db->prepare($sql);
$parameters = array(':location' => "Yorkshire");

$query->execute($parameters);
$data = $query->fetchAll();

Updating database data using PHP Prepared Statements

$sql = "UPDATE users SET location=:location WHERE email_address = :email_address";
$query = $db->prepare($sql);

$query->execute(array(':location' => 'New York', ':email_address' => "karlhadwen@gmail.com"));

Deleting database data using PHP Prepared Statements

$sql = "DELETE FROM users WHERE username = :username";
$query = $db->prepare($sql);
$query->execute(array(':username' => 'alanturing'));

Inserting database data using PHP Prepared Statements

$stmt = $db->prepare("INSERT INTO users(username, email_address,
    location, full_name, description)
    VALUES (:username, :email_address, :location, :full_name, :description)");

$stmt->execute(array( "username" => "karlhadwen", "email_address" => "karlhadwen@gmail.com",
"location" => "Yorkshire", "full_name" => "Karl Hadwen", "description" =>
"Whenever you find yourself on the side of the majority, it is time to pause and reflect. ? Mark Twain"));

Inserting multiple rows using PHP Prepared Statements

$rows = array( array( "karlhadwen1", "karlhadwen@gmail.com", "Yorkshire",
    "Karl Hadwen", "Whenever you find yourself on the side of the majority, it is time to pause and reflect. ? Mark Twain"),
    array( "alanturing2", "alanturing@gmail.com", "Yorkshire", "Alan Turing",
    "We can only see a short distance ahead, but we can see plenty there that needs to be done. ? Alan Turing"));
$args = array_fill(0, count($rows[0]), '?');

$query = "INSERT INTO users(username, email_address, location, full_name, description)
VALUES (".implode(',', $args).")"; $stmt = $db->prepare($query);

foreach ($rows as $row) {
    $stmt->execute($row);
}

Retrieving the last insert database id using PHP Prepared Statements

echo $db->lastInsertId();

So that's it. Do note that the difference between fetch and fetchAll is pretty self explanatory, if we know we're looking to bring back one row then use fetch, if we know we're looking to bring back more than one row use fetchAll.

Resources

Owasp: SQL Injection Prevention Cheat Sheet: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

PHP.net Prepared statements and stored procedures: https://php.net/manual/en/pdo.prepared-statements.php

PHP.net Prepared statements quick start: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Related Videos

https://www.youtube.com/watch?v=A9CzubAEBqk&list=PLpP9FLMkNf57Vzie8-t-87UQA0qWP_xPo&index=93

https://www.youtube.com/watch?v=sGteH1cXarU&list=PLpP9FLMkNf57Vzie8-t-87UQA0qWP_xPo&index=94

https://www.youtube.com/watch?v=PVY5TFAOlwM&list=PLpP9FLMkNf57Vzie8-t-87UQA0qWP_xPo&index=95

https://www.youtube.com/watch?v=oan7RRio1AI&list=PLpP9FLMkNf57Vzie8-t-87UQA0qWP_xPo&index=96

https://www.youtube.com/watch?v=jJns7RjLOq8&list=PLpP9FLMkNf57Vzie8-t-87UQA0qWP_xPo&index=97

https://www.youtube.com/watch?v=cOOKGU7gcwc&index=98&list=PLpP9FLMkNf57Vzie8-t-87UQA0qWP_xPo