[PHP] Basics of PDO

[brsy]

Since the deprecation of MySQL, many people are confused whether they should use MySQLi or PDO. They are both relatively the same, but it's all a matter of preference. I prefer to use PDO, so I'm going to demonstrate how to utilize a few of the basic functions within PDO.

Firstly, like every database interface, you need to connect to the database first.

Spoiler: Connecting to the Database

$zip['PDO']['Hostname']    = 'localhost'; #host name: usually localhost
$zip['PDO']['Username']    = 'root'; #username: usually root
$zip['PDO']['Password']    = 'abc123'; #password: usually null or root
$zip['PDO']['Database']    = 'zip'; # name of the database

try {
    $pdo = new PDO('mysql:host='. $zip['PDO']['Hostname'] .';dbname=' . $zip['PDO']['Database'], $zip['PDO']['Username'], $zip['PDO']['Password']);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die('ERROR: ' . $e->getMessage());
}

The above code will "try" to connect to the database, and if it is unsuccessful, it will return an error message explaining exactly what the problem is. Please be sure that error reporting is enabled, which can simply be done by adding error_reporting(E_ALL); to the top of your script. Next I will show you how to run a query using the wonderful prepare() and execute() function in PDO.

Spoiler: Running a Query

$sql    = "SELECT * FROM users WHERE username = :username";
                            $stmt   = $pdo->prepare($sql);
                            $stmt->execute(array(":username" => $username));

As you can see above, instead of adding the variable directly into the SQL query, you put in something called a named parameter. You then define the values for each parameter by supplying it into an array() within the execute function. This also helps you prevent SQL injection, making your life a whole lot easier. The function names are also a bit self-explanatory, which makes it even easier to maneuver and utilize. Next I will show you how to return data after you've run your query, which is also fairly simple.

Spoiler: Fetching Data After a Query

$row = $stmt->fetch(PDO::FETCH_ASSOC); # fetches the results, and places them into the associative array $row.

echo $row['password']; # this would then echo the value of the column 'password'

I hope you enjoyed this tutorial, as I wished that I had a tutorial showing me how to use PDO. I had to research each function which was a bit more time consuming. For more information on PDO, check out the docs on

You must be registered for see links

 

[AaidenX]

I recommend you to change this line:
"Please be sure that error reporting is enabled, which can simply be done by adding error_reporting(1);"
to:
"Please be sure that error reporting is enabled, which can simply be done by adding error_reporting(E_ALL);"

And,
"$row = $stmt->fetch(PDO::FETCH_ASSOC); # fetches the results, and places them into the array $row."
to
"$row = $stmt->fetch(PDO::FETCH_ASSOC); # fetches the results, and places them into the associative array $row."

Otherwise, 9/10. Good job.