In PHP, working with databases using the PHP Data Object – PDO class is one of the best ways to create a dynamic website. Now, it should be worth mentioning that to work with databases, you need to know how to talk to a database. Please review the SQL tutorials before attempting to use databases in PHP.

In SQL, we talk to databases primarily using queries. It has its own syntax and structure that is pretty logical. However, it is also very sensitive because it deals with all of your data. SQL injections can ruin your life. No worries. PDO has a couple of excellent ways of overcoming SQL injections.

Before we get started, I will not covering all of the SQL queries because I just want you to get the idea. Now, Let’s get started:

PHP Data Object – PDO

Database Connection

A database connection is always required to talk to a database because I mean, we have to know what data we want to work with right? Now, we need to know a few identifiers to access the database, including the location of that database, the database name, username, and password.

You should be able to figure all of these out from your hosting provider. It would be too difficult for me to show you how to find them because of the tons of hosting companies that all do it differently.

Example

$host = 'myHostingURL.hostedresource.com';
$dbName = 'databaseName';
$username = 'myUsername';
$password = 'topSecretPassword';
 
$dbCon = new PDO("mysql:host=".$host.";dbname=".$dbName, $username, $password);

Now, you have to change those variables to whatever database settings, username, and password you have. Let’s figure out what the heck is going on here. After the configuration variables, we use $dbCon = new PDO and pass in our various parameters. Of course, you don’t have to split it up like I did, but that makes it much easier to modify down the road.

Basically, this is how you set up your link to the database. Your $dbCon variable is now an object of the PDO class. We’ll talk more about this as we see it in action. This is the absolute link to anything and everything you want to do with your data in the database. That is fantastic, but I want to see some data!

Retrieving Data From a Database

Example

$sql = 'SELECT * FROM tableName';
$stmt = $dbCon->prepare($sql);
$stmt->execute();
while ($row = $stmt->fetch())
{
    echo  $row[0] . " | " . $row[1] . " | " . $row[2] . "<br/>";
}

The $sql variable simply holds our SQL that we learned how to create in the SQL tutorials. tableName is just the name of the table in our database (yours will probably be different). After this, things start to get interesting. As I said earlier, PDO is a class, which means we create an object from the PDO class.

Objects in PHP use the -> syntax to use a method in that class. So $dbCon->prepare($sql) is calling the prepared method, passing in our SQL. We save that to a new object $stmt so we can now can focus on a particular query. Then, we use $stmt->execute() that tells PHP to run the query.

In our while loop, we use the somewhat complicated $row = $stmt->fetch(). Basically, what we are doing here is setting a variable $row equal to each record that is returned until there are no more records left. $stmt->fetch() is just getting one record at a time.

Finally, we echo out results out using an index on the column names. So for example, $row[0] will return the first column of that record, $row[1] will return the second column, and so on.

Inserting Data

Inserting data pretty much follows the same flow as the SELECT query we just ran. However, I am going to use this type to introduce you some of the awesome things about PDO. If don’t know what SQL injection is, it’s basically whenever someone injections SQL into one of the POST or GET parameters.

This is a problem for novice coders, because they might directly put that variable in the query without escaping it, which means someone can modify your SQL query! Instead, PDO lets you escape the query easily.

Example

$sql = 'INSERT INTO tableName(column1,column2) VALUES(?,?)';
$stmt = $dbCon->prepare($sql);
$stmt->execute(array('aaa',"bbb"));

Now, that doesn’t look quite like SQL you might say. Notice the ? in the query. Well my friends, that is the beginning of the awesome “PDO factor”. We are simply telling PHP, “Hey, we are going to give you these values in order later”. So, we just move on and prepare our statement like we are cool.

Finally, we get back to telling PHP what exactly we are talking about with that query. In our execute, we pass an array with the values in the order we have the question marks. Pretty awesome, huh? Note: the the column1 and the column2 are just the names of your columns. I must admit there is a little bit of a better way to do this without question marks.

Example

$sql = 'SELECT * FROM tableName
            WHERE columnName = :myVar';
    $stmt = $dbCon->prepare($sql);
    $stmt->execute(array(':myVar'=>'someValue'));
    $row = $stmt->fetch();
    print_r($row);

This is a SELECT query, but we can still pass variables into it. We just replace the ? with our new variable :myVar. Note: the : is important, as it specifies that we are using a temporary variable.

Then later in our execute statement, we can tell PHP what we meant by that variable by placing all of those variables in an array and placing that temporary variable as a string in our index, and the intended value of that variable after the =>.

The point of the array is so we can pass in many values into our SQL query. For now, this concludes using PDO, but there is so much more for you to learn in the following resources. Try to go through a little bit of the official documentation to get a real feel for how to use PDO.

References