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";
  6.  
  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] );
  4.  
  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] );
  4.  
  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
webdevetc

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



More...


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

What is the difference between var, let and const in Javascript?

How to Extract Query String (from a URL) Into an Associative Array in PHP

How to do comments in Blade files?

How to check if a relation was loaded on an Eloquent model already?

How to set the timezone to UTC in .htaccess

How to set the Expires header in .htaccess

What are Template literals (Template strings) in JS?

How to find the average value of items in a Laravel Collection?

What are route patterns in Laravel?

How to set up an alias to map one directory to another destination