PHP and PDO

I have been playing around with PDO (PHP Data Objects) the last couple of days and I really like it. As stated in the PHP docs the PDO extension defines a lightweight, consistent interface for accessing databases in PHP. In most of the projects I have worked with I have used the MySQL database, and therefore only used the regular mysql extension in PHP. I thought it would be nice to try something else for a while, so therefore I decided to implement PDO instead of the mysql_* function calls in the development version of the Fredrikstad Klatreklubb (local climbing club) site.

There are some things I really like about PDO, like prepared statements. Using prepared statements with placeholders one does not need to remember to escape everything because PDO does that for you, and you can also run the same query several times with different values:

// connect to the db
$pdo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'password');

// make sql statement with placeholders
$sql = "INSERT INTO people(name, age, sex, location) VALUES(:n, :a, :s, :l)";

// prepare the statement
$stmt = $pdo->prepare($sql);

// bind php variables to the placeholders in the statement
$stmt->bindParam(':n', $name);
$stmt->bindParam(':a', $age);
$stmt->bindParam(':s', $sex);
$stmt->bindParam(':l', $location);

// insert one row
$name = 'Christer';
$age = 26;
$sex = 'm';
$location = 'norway';

// execute the statement
$stmt->execute();

// insert another row
$name = 'Tone';
$age = 24;
$sex = 'f';
$location = 'norway';

// execute the same statement with the updated variables
$stmt->execute();

It is also easy to fetch stuff:

$sql = "SELECT * FROM people";
$people = $pdo->query($sql)->fetchAll();

foreach ($people as $p) {
    // do something
}

Another way to do this is:

$sql = "SELECT * FROM people";

foreach ($pdo->query($sql) as $p) {
    // do something
}

or

$sql = "SELECT * FROM people";
$stmt = $pdo->query($sql);

while ($p = $stmt->fetch()) {
    // do something
}

There is one feature that I had some problems with, and that is getting the search function on the climbing site to work with PDO. Let’s say we have an array of words that can match one or more fields in a table. One way to do this using the regular mysql_* functions would be to just work your way through the array and generate sql code along the way. If one is to use the placeholders in PDO one will need to dynamically generate placeholders since a placeholder can not be used more than once in a statement, and then later map the words in the array of search words to the correct placeholders. Lets have a look:

// lets assume $_GET['query'] is 'christer tone mats'
$words = explode(' ', $_GET['query']); 

// number of words in the query
$num_words = count($words);

// start the sql string
$sql = "SELECT * FROM people WHERE ( ";

// work our way through the array of words
for ($i = 0; $i < $num_words; $i++) {
    $sql .= " name LIKE :word" . $i . "_1 OR " .
            " location LIKE :word" . $i . "_2";

    if (($i + 1) != $num_words) {
        $sql .= " OR ";
    }
}

$sql .= " ) ORDER BY name ASC";

// prepare the sql statement
$stmt = $pdo->prepare($sql);

The query would then look like:

SELECT
    *
FROM
    people
WHERE
(
    name LIKE :word0_1 OR location LIKE :word0_2 OR
    name LIKE :word1_1 OR location LIKE :word1_2 OR
    name LIKE :word2_1 OR location LIKE :word2_2
)
ORDER BY
    name ASC

So far so good. We will then have to bind the words to the placeholders:

for ($i = 0; $i < $num_words; $i++) {
    $stmt->bindValue(':word' . $i . '_1', "%" . $words[$i] . "%");
    $stmt->bindValue(':word' . $i . '_2', "%" . $words[$i] . "%");
}

$ret = $stmt->execute();

if ($ret) {
    $hits = $stmt->fetchAll();
} else {
    // do something clever here
}

Notice that I have used PDOStatement::bindValue() instead of PDOStatement::bindParam() to bind the value of the variable to the placeholder instead of the variable as a reference. The hits from the query will then be stored in the $hits array.

I started the change from mysql_* to PDO earlier this week, and as of this writing, no more mysql_* calls can be found in the code. :) For more info on how to install PDO and info about the software requirements and so forth, consult the PHP manual.

PS: There might be errors in the code presented in this post as none of it has been tested. If you test the code provided and find some errors, please let me know.

Advertisements
This entry was posted in PHP. Bookmark the permalink.

7 Responses to PHP and PDO

  1. Tom says:

    PDO is nice.

    May I suggest that if you’re doing text searches that you don’t use ‘LIKE’. Try MySQL’s MATCH (cols) AGAINST (expr) syntax with a FULLTEXT index. Much faster, and easier to code too.

    http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

  2. christer says:

    I have tried to use that feature but the results were not too impressive. It’s been a while since I did the tests, so stuff might work better now. :)

    Thanks for the comment

  3. Adam Moro says:

    Thanks, this was very helpful…especially the first example. I’ve always used query() for pretty much everything but this seems to work much faster.

  4. Oh my God thank you SO much! I have been looking for HOURS for something like this!

    I am definitely bookmarking this site. I REALLY appreciate this article!

  5. ifliandry says:

    is PDO the easiest way to code in php ? . I have tried PDO but not sure enough to use it. eventhough, i admit it that PDO using in object oriented on php make code cleaner.

  6. Quynh Anh says:

    Oh my God! I greatly appreciate your article :). Thank you so much!

  7. marcelo066 says:

    ok, but i want to do this:
    if(!is_empty($_GET[‘city’])){
    $cidade = safe_string_escape($_GET[‘city’]);
    $cidadestr=”AND cidade LIKE ‘%$city'”;
    }
    else $citystr=””;

    if(!is_empty($_GET[‘dorms’])){
    $dormitorios = $_GET[‘dorms’];
    $dormsstr=”AND dorms= ‘$dorms'”;
    }
    else $dormsstr=””;
    $my_sql = “SELECT * FROM houses WHERE status = ‘Published’ $citystr $dormsstr”;

    i try to do this? using the same from above, just changing the query.
    $my_sql= “SELECT * FROM houses WHERE status = :status :citystr :dormsstr”;
    $srch_sql->bindValue(‘:status ‘, ‘Published’, PDO::PARAM_STR);
    $srch_sql->bindValue(‘:citystr’, $citystr, PDO::PARAM_STR);
    $srch_sql->bindValue(‘:dormsstr’, $dormsstr, PDO::PARAM_STR);
    $srch_sql->execute() or die(print_r($srch_sql->errorInfo()));

    but this does not works, how solve this one? can you elucidate me?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s