In PHP, working with databases using the 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.

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.

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.

Retrieving Data From a Database

$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.

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.

$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.

I must admit there is a little bit of a better way to do this without question marks.

    $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.