December 21, 2010 in PHP, Programming, Tutorials by admin

If you have been using php and mysql and have no idea what PDO is, then read this tutorial now. PDO stands for PHP Data Objects and it is a lean and efficient way to interact with databases. PDO uses a unified API allowing for better consistency and is a must learn for every PHP developer.

If you are a mysql_ kind of guy or gal and have been making mysql inserts like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
//connect to database

mysql_connect('localhost','myuser','mypass');
mysql_select_db('mydb');
//insert user into database

mysql_query("INSERT INTO sometable(name,age) VALUES('$name','$age')");
?>

Try doing that exact same call using PDO and prepared statements. Prepared statements are a more secure way of dealing with user inputted variables into your database. Prepared statements are a key safeguard to protecting against SQL injection.

Here is that same mysql connect and insert with PDO:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
//connect to database

$dbh = new PDO('mysql:host=localhost;dbname=mydb','myuser','mypass');
//insert user into database

$stmt = $dbh->prepare("INSERT INTO sometable(name,age) VALUES(?,?)");
//bind name and age to statement then execute

$stmt->bindParam(1,$name);
$stmt->bindParam(2,$age);
$stmt->execute();
?>

You can see that prepared statements take slightly more code to complete, but it is well worth the effort. Next we will try pulling some data from the database with the mysql_ method and compare to the PDO method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
//Select name and age from your table

$q = mysql_query("SELECT name,age FROM sometable WHERE id='$id'");
//loop through results

 while($results = mysql_fetch_array($q)){
       $name = $results['name'];
       $age = $results['age'];
 }
?>

Now let’s take a look at how similar this next PDO mysql select call with prepared statements example is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
//prepare mysql statement and bind id as parameter

$stmt = $dbh->prepare("SELECT name,age FROM sometable WHERE id=?");
$stmt->bindParam(1,$id);
//execute the mysql statement

$stmt->execute();
//loop through results

 foreach($stmt->fetchAll() as $results){
       $name = $results['name'];
       $age = $results['age'];
 }
?>

Not that much different really which helps a lot with understanding the syntax differences. If you have any questions, please feel free to ask in the comments and I’ll do my best to answer.