Quickstart to PDO (PHP Database Objects)

PHP is still my favorite programming language, but I have been away from development for a little while. So, this post is me relearning bits of the language along with MySQL to get some interesting new projects done.

PDO is probably the third major method used by PHP to connect the popular web-server scripting language to databases. MySQL being the most popular database associated with PHP, but not the only database supported by PDO. That seems to be the primary point, that you can essentially write SQL (Structured Query Language) for one database, and then change the type of database, and your query will still work. However, I’m sticking with MySQL for now — it’s what I know and all I need at the moment. Previously, PHP used a specific “mysql” set of commands, and then improved upon that with object-oriented version called “mysqli.” The original mysql commands are no longer supported, but the mysqli commands are and they are perfectly fine, but PDO is supposed to be a better way. One of the reasons is the use of “prepared statements” which allegedly improves security; because “SQL injections” are among the worst holes that dishonest hackers seek to exploit.

Setting up

Check your php.ini file, and make sure this line is not commented out with a semicolon (;) at the beginning:

extension-php_pdo_mysql.dll

You might have more than one php.ini file depending on your history of system updates. (ie. in the /etc/php5/apache2/ directory and also in a /etc/php/7.0/ directory).

After editing the file, you’ll need to restart your web server — eg. for Ubuntu Apache: /etc/init.d/apache2 restart

3 main classes

• PDO – connection
• PDOStatement — prepared statement and result
• PDOException — for error handling

Primary Difference

Previously, with mysql and mysqli commands, an SQL statement would have been dynamically crafted using the values sent to be stored or retrieved:

// VALUE TO LOOK FOR EXAMPLE
$idSent = $_GET["idRequested"];

// OLD STYLE OF SQL STATEMENT
$SQL = "SELECT * FROM myTable WHERE id = $idSent";

Instead, with “prepared statements,” an SQL statement will be crafted that has placeholders. There are two different kinds of placeholders: question marks (?) and colon-marked names (aka parameter binding).

// QUESTION MARK EXAMPLE EXPECTS VALUES STORED IN AN A STANDARD ARRAY
$SQL = "SELECT * FROM myTable WHERE id = ?";

// COLON-MARKED NAMES EXPECTS ASSOCIATIVE ARRAY NAMES 
// TO MATCH & REPLACE VALUE
$SQL = "SELECT * FROM myTable WHERE id = :idSent";

Connection Variables That You’ll Need To Know for Setting Up

// VARIABLES USED FOR CONNECTING
$hostName = "[host_name]"; // probably localhost
$userName = "[user_name]"; // whatever name has preferreed database access
$password = "[password]"; 
$databaseName = "[database_name]"; // chosen/assigned database name

// SET THE DATA SOURCE NAME
// STRING THAT CONTAINS INSTRUCTION TO REPRESENT 
// A CONNECTION TO A DATA SOURCE -- MYSQL IN THIS CASE
$dataSourceName = "mysql:host=" . $hostName . ";dbname=" . $databaseName;

// CREATE AN INSTANCE OF THE PDO OBJECT
$pdo = new PDO($dataSourceName, $userName, $password);

Querying: Result as either Associative Array or Objects

// CREATE A STATEMENT OBJECT
$statement = $pdo->query("SELECT * FROM posts");

// QUERY VERSION 1: LOOP THROUGH THE RESULTING ROWS AS AN ARRAY
while ($row = $statement->fetch(PDO:FETCH_ASSOC)) {
  echo $row["title"] . "<br>\n";
}

// QUERY VERSION 2: TO LOOP THE SAME DATA AS A SET OF OBJECTS
while ($row = $statement->fetch(PDO:FETCH_OBJ)) {
  echo $row->title . "<br>\n";
}

PDO Options

// TO DEFAULT TO THE RETRIEVAL AS AN OBJECT,
// PUT THIS LINE ABOVE THE $statement
$pdo->setAttribute(PDO:ATTR_DEFAULT_FETCH_MODE,PDO:FETCH_OBJ);

// TURNING EMULATION OFF SO THAT LIMIT CLAUSE 
// CAN BE USED PROPERLY (AS MENTIONED BELOW)
$pdo->setAttribute(PDO:ATTR_EMULATE_PREPARES, false);

/*
  // ALTERNATE METHOD IS TO SEND OPTIONS WHEN CREATING THE PDO OBJECT
  $options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
  ];

  // THEN CREATE PDO OBJECT, WITH ADDTIONAL 4th PARAMETER AS OPTIONS
  $pdo = new PDO($dataSourceName, $userName , $password, $options);
*/

Prepared Statements

Prepared statements separate the instruction from the data, so they tend to be safer against SQL injection when user-submitted input has not been properly filtered and made safe.

Traditional SQL statements look like:

// "SELECT * FROM tableName WHERE someValue = " . $valueSent;

If the $valueSent has been submitted directly from a form without being sanitized, then SQL injection can happen. Or the person can retrieve more data than they should be allowed to see. Cleansing of the user input might involve removal of quotes, apostrophes, semi-colons, tag brackets (ie. < … >), and stop and start tags for code blocks (ie. <?php … ?> <script>…</script>).

You want to create a placeholder for a “positional parameter” or a “named parameter.”

Select Statements: Execute with either a dynamic array or associative array

// EXAMPLE INPUT REQUEST
$authorSentVar = "James";
$isPublishedVar = true;
$limitVar = 1;

// POSITIONAL PARAMETERS: VERSION 1
// THIS QUESTION MARK WILL BE REPLACED BY THE FIRST ELEMENT IN THE ARRAY
// WHICH WILL BE SENT TO THE EXECUTE STATEMENT.
// INCLUDES LIMIT, WHICH NEEDS TO HAVE EMULATION MODE 
// TURNED OFF (SEE ABOVE)
$sql = "SELECT * FROM posts WHERE author = ? && is_published = ? LIMIT ?";
$statement = $pdo->prepare($sql); // CREATES OBJECT
$statement->execute([$authorSentVar,$isPublishedVar,$limitVar]); // AN ARRAY IS SENT, ONE ELEMENT HERE
$posts = $statement->fetchAll(); // LEFT BLANK TO DEFAULT TO OBJECT 

var_dump($posts); // WILL MAKE VISIBLE OUTPUT OF OBJECTS IN RESULT

// LOOP THROUGH ARRAY OF OBJECTS
foreach ($posts as $post) {
  $post->title . "<br>\n";
}

// NAMED PARAMETERS: VERSION 2
// THESE COLONS SIGNIFY THE NAMES OF ARRAY ELEMENTS THAT IT EXPECTS TO RECEIVE
$sql = "SELECT * FROM posts WHERE author = :author && is_published = :isPublishedVar";
$statement = $pdo->prepare($sql); // CREATES OBJECT
// USES ASSOCIATIVE ARRAY TO SEND AS DATA
$statement->execute(['author' => $authorSentVar, 'is_published' => $isPublishedVar]);
$posts = $statement->fetchAll(); // LEFT BLANK TO DEFAULT TO OBJECT 

var_dump($posts); // WILL MAKE VISIBLE OUTPUT OF OBJECT RESULT

foreach ($posts as $post) {
  $post->title . "<br>\n";
}

// FETCH SINGLE POST: VERSION 3
$sql = "SELECT * FROM posts WHERE id = :id";
$statement = $pdo->prepare($sql);
$statement->execute(["id" => $id); // SENDS ASSOCIATIVE ARRAY
$post = $statement->fetch();

echo $post->title . "<br />" . $post->body;

// GET ROW COUNT EXAMPLE
// QUESTION MARK WILL BE REPLACED BY ELEMENT IN THE ARRAY IT RECEIVES
$statement = $pdo->prepare("SELECT ALL FROM POSTS WHERE author = ?");
$statement->execute([$author]); // SENDS AN ARRAY
$postCount = $statement->rowCount();

echo $postCount;

Insert, Update, Delete, Search

// INSERT DATA EXAMPLE
$title = "My Insert Example";
$body = "This is the body of my post";
$author = "Willy"

$sql = "INSERT INTO posts (title, body, author) VALUES (:title, :body, :author)";
$statement = $pdo->prepare($sql);
// SEND A CREATED-ON-THE-FLY ASSOCIATIVE ARRAY
$statement->execute(["title"=> $title, "body"=> $body, "author"=>$author]);

// UPDATE DATA EXAMPLE
$id = 1;
$body = "This is the updated version of my post";

$sql = "UPDATE posts SET body= :body WHERE id = :id";
$statement = $pdo->prepare($sql);
// SEND A CREATED-ON-THE-FLY ASSOCIATIVE ARRAY
$statement->execute(["body" => $body, "id"=>$id]);

// DELETE DATA EXAMPLE
$id = 5;

$sql = "DELETE FROM posts WHERE id = :id";
$statement = $pdo->prepare($sql);
// SEND A CREATED-ON-THE-FLY ASSOCIATIVE ARRAY
$statement->execute(["id"=>$id]);
echo "Post Deleted";

// SEARCH DATA EXAMPLE
$userSearch = "%post%";
$sql = "SELECT ALL FROM posts WHERE title LIKE ?";
$statement = $pdo->prepare($sql);
$statement->execute([$userSearch]);
$posts = $statement->fetchAll();

foreach ($posts as $post) {
  echo $post->title . "<br />";
}

Errors

> Syntax error or access violation: 1064

This error may refer to the use of a reserved keywords in your SQL syntax:

Error example:

INSERT INTO myTable (key, id, myName) VALUES (100, "myId_54", "Joe");

Here, the keyword might be “key”, so you have to put backticks (`…`) around each field name to avoid this situation.

Fixed example:

INSERT INTO myTable (`key`, `id`, `myName`) VALUES (100, "myId_54", "Joe");