Wern Ancheta

Adventures in Web Development.

Using PDO in PHP

| Comments

In this tutorial I’m going to walk you through how to use the PDO extension in PHP to connect to a MySQL database.

Connecting to the Database

The PDO extension is installed by default on modern versions of PHP so you don’t have to worry about not having it on the machine. You should be upgrading to a recent version any way to take advantage of the security patches and other updates.

To connect to the database, you have to supply a data source name (DSN) as the first argument when creating a new instance of the PDO class. And then pass in the username and password of the user you want to login to the database as the second and third argument. You need to wrap the code in a try..catch block because it will return an error if there’s a problem connecting to the database. Next, set the error mode to return an exception if there’s an error executing a specific database query. And finally, set the emulation of prepared statements to false. This allows you to use the native parametrized query feature of the database instead of emulating it in the PHP side.

1
2
3
4
5
6
7
8
9
10
11
12
<?php
$dsn = 'mysql:dbname=DATABASE-NAME;host=DB-HOST';
$username = 'DATABASE-USER';
$password = 'USER-PASSWORD';
try {
    $db = new PDO($dsn, $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (PDOException $e){
    echo 'could not connect to the database';
}
?>

Inserting Data

Open up phpmyadmin or any database management tool that you’re using and create a new database. Then execute the following query to create a users table. We will be using this table for executing queries in the database. If you’re following along, don’t forget to update the data source name to match the name of the database.

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL,
  `name` varchar(300) NOT NULL,
  `age` int(11) NOT NULL,
  `email` varchar(300) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

Once that’s created, you can now try inserting a new user row in the users table. Just pass in the typical INSERT query as an argument to the query method using the new PDO instance that you created earlier.

1
2
3
4
5
6
7
<?php
$name = 'yoh asakura';
$age = 10;
$email = 'yoh-asakura@gmail.com';

$db->query("INSERT INTO users SET name = '$name', age = '$age', email = '$email'");
?>

Updating Data

If you want to update a specific user, you still use the query method. The only difference is the query that you are passing in. This time you have to pass in an UPDATE query to set the new values and then use the WHERE clause to specify which specific row you want to update. In this case it’s the first row in the users table.

1
2
3
4
5
6
7
8
<?php
$id = 1;
$name = 'ren tao';
$age = 10;
$email = 'ren-tao@gmail.com';

$db->query("UPDATE users SET name = '$name', age = '$age', email = '$email' WHERE id = '$id'");
?>

Deleting Data

If you want to delete a specific row, use the DELETE query and a WHERE clause to specify which rows you want to delete.

1
2
3
4
<?php
$id = 1;
$db->query("DELETE  FROM users WHERE id = '$id'");
?>

Selecting Data

Selecting data still uses the same query method. Only this time you have to pass in a SELECT query. Then you can loop through the results and access the column that you want to output just like you would when accessing an item in an associative array.

1
2
3
4
5
6
<?php
$results = $db->query("SELECT name FROM users");
foreach($results as $row){
    echo $row['name'] . "<br>";
}
?>

If you think objects look better, you can use the setAttribute method to modify the default fetch mode.

1
2
3
<?php
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
?>

Prepared Statements

Prepared statements makes your queries more secure. Why? Because data is treated as data when you use prepared statements. This means that SQL statements such as OR DROP TABLE users wouldn’t work if the user inputs an SQL statement instead of the expected data. This is implemented in PDO by calling the prepare method and then passing in the query that you wish to execute. But instead of supplying the data directly, you replace it with placeholders. In this case the placeholder is :name. You then call the execute method to actually execute the query. This method accepts an associative array containing the placeholder as the key and the actual value that you want to pass in as its value. Finally, you call the FetchAll method to fetch all the results that was returned.

1
2
3
4
5
6
7
8
9
10
11
<?php
$query = $db->prepare("SELECT name FROM users WHERE name LIKE :name");
$query->execute(array(
    ':name' => 'Mr.%'
));

$results = $query->FetchAll();
foreach($results as $row){
    echo $row->name . "<br>";
}
?>

For queries that’s only expected to return a single row, you can use the fetch method instead.

1
2
3
4
5
6
<?php
$query = $db->prepare('SELECT name FROM users WHERE id = :id');
$query->execute(array(':id' => 2));
$user = $query->fetch();
echo $user->name;
?>

Transactions

Transactions in databases is a sequence of database operations that are treated as a single unit. You can use it to ensure that all the queries that you want to execute are all completed successfully. If one fails, nothing is actually committed to the database.

To test transactions, open up your database management tool of choice and execute the following. This will create the other_details table which stores the other details of a user.

1
2
3
4
5
CREATE TABLE IF NOT EXISTS `other_details` (
`id` int(11) NOT NULL,
  `school` varchar(300) NOT NULL,
  `city` varchar(300) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

For the example below, I’m using a library called Faker. You can use it to generate fake data to be inserted into the database. You can install it through composer by executing the following command in your working directory.

1
composer require fzaninotto/faker

Once that’s done installing, you can include the autoload file so that it’s loaded in the current file. You can then create a new instance of it and then use the generator that you want. In the code below I’m using the name, numberBetween, email, company and city generators.

1
2
3
4
5
6
7
8
9
10
11
12
<?php
require_once 'vendor/autoload.php';

$faker = Faker\Factory::create();

$name = $faker->name;
$age = $faker->numberBetween(10, 35);
$email = $faker->email;

$school = $faker->company;
$city = $faker->city;
?>

You can then create a new transaction by calling the beginTransaction method. Any queries that are executed after calling this wouldn’t actually commit to the database until you call the commit method. In the example below, I’m executing a query to insert a new user into the users table as well as the other_details table. If any of those 2 queries fails, it won’t be committed to the database.

1
2
3
4
5
6
7
8
9
10
11
<?php
$db->beginTransaction();

$query = $db->prepare("INSERT INTO users SET name = :name, age = :age, email = :email");
$query->execute(array(':name' => $name, ':age' => $age, ':email' => $email));

$query2 = $db->prepare("INSERT INTO other_details SET school = :school, course = :city");
$query2->execute(array(':school' => $school, ':city' => $city));

$db->commit();
?>

Conclusion

That’s it! In this tutorial, you’ve learned how to use the PDO extension in PHP. Note that it’s not only the MySQL database that PDO supports. It also supports other databases such as CUBRID, PostgreSQL, SQLite and many others.

Comments