How to query mysql using PDO in PHP?

PHP: How to query mysql using PDO in PHP?

Years ago you used to see mysql_... functions everywhere. This has been (fortuantely) replaced by first the mysqli functions and more recently the object oriented way of PDO. Using PDO you can easily swap between database drivers (Mysql, Postgre, SQLite, etc). Here is how to connect to a mysql database, and then how to query it using PDO.

This also applies to mariadb

Connecting to MySQL with PDO in PHP

You should wrap this in a try/catch to handle a database connection error.

  1.   try {
  2.     $host = "your host";
  3.     $dbname = "your_database";
  4.     $user = "your_user";
  5.     $pass = "qwerrtyuiop12345678";
  7.     $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
  8.   }
  9.   catch(PDOException $e) {
  10.       echo  $e->getMessage(); // handle this better, this is just for the demo!
  11.   }

How to query the database

With PDO you don't have to think about escaping data. Every query should be 'prepared', then you can bind variables to placeholders. If you follow this example it should make sense.

In this example, we will do an insert and create a blog post with the following data:

  1. $title = "Blog post title";
  2. $post_body = "Blog post body";
  3. $status = "published_status";

Unnamed placeholders

  1.   $prepared_statement = $dbh->("INSERT INTO blog_posts (title, post_body, status) values (?, ?, ?)");

These are indexed, starting at 1.

  1. $prepared_statement->bindParam(1, $title);
  2. $prepared_statement->bindParam(2, $post_body);
  3. $prepared_statement->bindParam(3, $status);

And then you execute it:

  1. $prepared_statement->execute();

Named placeholders

You can also used named placeholders. They don't have to match anything else in the sql statement.

Although you will see a : before their names (such as :title) here and in most code, they aren't required. But they are a common convention that you should follow!

  1.   $prepared_statement = $dbh->("INSERT INTO blog_posts (title, post_body, status) values ( :title, :body, :post_status )");

Now that we have prepared the statement and used some named params, we need to bind them:

  1. $prepared_statement->bindParam(":title", $title);
  2. $prepared_statement->bindParam(":body", $post_body);
  3. $prepared_statement->bindParam(":post_status", $status);

And then you execute it:

  1. $prepared_statement->execute();

Alternative ways to do this:

You can also pass an array to ->execute with an array:

  1.   $prepared_statement = $dbh->("INSERT INTO blog_posts (title, post_body, status) values ( :title, :body, :post_status )");
  2.   $prepared_statement->execute([
  3.       "title" =>"my new blog post",
  4.       "body" => "Welcome to my new blog",
  5.       "post_status" => "published_status",
  6.   ]);
  7.   // or
  8.   $prepared_statement = $dbh->("INSERT INTO blog_posts (title, post_body, status) values ( ?,  ?, ? )");
  9.   $prepared_statement->execute([
  10.       "my new blog post",
  11.       "Welcome to my new blog",
  12.       "published_status",
  13.   ]);

An important thing to note about bindParam...

If you look at the method structure:

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )

You might notice the second parameter. It is &$variable - by reference. So if you did this:

  1.     $title = "title 1";
  2.   $prepared_statement = $dbh->("INSERT INTO blog_posts (title) values ( :title)");
  3.   $prepared_statement->bindParam(1, $title);
  4.   $title = "updated title here";
  5.   $prepared_statement->execute();?>

Then a new row would be inserted with "updated title here".

Why? because sent $title to bindParam, which saves the reference. You then update $title to "updated title here", so when you run ->execute() it uses whatever value is in $title, which is obviously the updated value.

Use bindValue() to avoid this issue.

How to get rows from the database?

By default PDO will return an array indexed by both column name and number (e.g. [1=> "some blog title", 'blog_title' => "some blog title", 2 => "some blog post body" , "post_body" => "some blog post body"].

There are a few ways to change this. You can use $statement->setFetchMode(...) (or $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);) to change this behaviour.

If I want to just work with an array I will normally use PDO::FETCH_ASSOC. The fetchObject method is nice too - it fetches the next row and returns it as an object. You can define what class it will create, so you can set it up to return model objects.

But anyway, on to some examples.

SELECT queries...

I am going to use the direct ->query() here, avoiding the binding and execute.

  1. $query = $dbh->query("SELECT * FROM blog_posts");
  2. foreach ($query as $row)
  3. {
  4.     var_dump($row);
  5. }

But I could also do this:

  1. $blog_post_id=123;
  2. $statement = $dbh->prepare("SELECT * FROM blog_posts WHERE id = ?");
  3. $statement->execute([ $blog_post_id] );
  5. //get one row:
  6. $row = $statement->fetch();
  1. $category_id=22;
  2. $statement = $dbh->prepare("SELECT * FROM blog_posts WHERE category_id = ?");
  3. $statement->execute([ $category_id] );
  5. // get all:
  6. $rows = $statement->fetchAll();

Update and delete

You can run these in a similar manner.

Delete SQL statement with PDO

  1. $category_id=22;
  2. $statement = $dbh->prepare("DELETE FROM blog_posts WHERE category_id = ?");
  3. $statement->execute([ $category_id] );

Update SQL statement with PDO

  1. $id=22;
  2. $new_title = "foobar";
  3. $statement = $dbh->prepare("UPDATE blog_posts set title = ? WHERE id = ?");
  4. $statement->execute([ $title, $id] );
webdevetc profile pic

I am a 29 year old backend web developer from London, mostly focusing on PHP and Laravel lately. This ( is my blog where I write about some web development topics (PHP, Laravel, Javascript, and some server stuff). contact me here.


Comments and discussion about How to query mysql using PDO in PHP?

Found this interesting? Maybe you want to read some more in this series?

Or see other topics in the PHP language

Or see other languages/frameworks:
PHP Laravel Composer Apache CentOS and Linux Stuff WordPress General Webdev and Programming Stuff JavaScript
Or see random questions

How to block access to certain referrers in Apache using .htaccess

What is the difference between ( for ... in ) and ( for ... of ) in javascript?

How to find an item by Primary Key in Eloquent, or throw a ModelNotFoundException exception if it isn't found?

Remove trailing slash (redirect to remove trailing slash) in .htaccess

How to group a SQL statement in brackets/parenthesis

What is the spaceship operator (<=>) in PHP?

How can you check if a variable is alphanum (alphanumeric, letters and numbers only) in PHP?

What is YAGNI?

How to set infinite execution time in a PHP script

How to find the intersection (same values) from two arrays?