Almost there!!

There is only one room left to paint, and my father will start tomorrow, so in a couple of days I will start moving my things from where I live now to the new place. I am really looking forward to moving now. The SDSL line will be up next monday hopefully, so there will be some days without any internet…

Machine Head album preview!

Check out the preview of the new Machine Head album over at their site! I for one am really looking forward to the new album, and hopefully another show at Rockefeller in Oslo! :)

Painting sucks!!11

Blargh! I am sick of painting, and I have soooo much left to do in the new apartment!! I will paint the ceiling in the room I will use as an office tomorrow, and the day after I have to paint it again. Then I can start at the walls… Nice way to spend the summer. :)

Finished the sleeping room today though. :) At least one room is ready!

Got myself a new apartment!

Today I got myself a new apartment here in Fredrikstad. I have been renting the same place for almost 6 years and now I have finally bought my own apartment. :) Bought it from a friend of mine and I will be moving sometime in July. I will have to paint the place and do some small fixing here and there first though. I will post some pictures once the place is ready.

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.