I recently imported a database which included a selection of French postcodes. Because of a bug with the original export (received in a spreadhseet) some of the postcodes were missing the leading zeros, and some of them actually had more leading zero’s than were required.
Unfortunately the problem wasn’t raised until the data was imported, and a fresh import was going to take me hours. Fortunately all French postcodes are 5 characters long so it was possible to fix this problem programmatically.
When performing a task like this, there’s nearly always two options. Write an SQL query to do it:
UPDATE customers SET postcode = CONCAT('0',postcode) WHERE country = 'france' AND CHAR_LENGTH(postcode) = 4;
or
UPDATE customers SET postcode = SUBSTRING(postcode,-5) WHERE country = 'france' AND CHAR_LENGTH(postcode) = 6;
Or do exactly the same thing using a short PHP script:
$sql = "SELECT id,postcode FROM customers WHERE country = 'france'";
$rs = mysql_query($sql);
while($row = mysql_fetch_assoc($rs)) {
$replacement_postcode = "";
if(strlen($row['postcode']) == 6) {
$replacement_postcode = substr($row['postcode'], -5);
} elseif(strlen($row['postcode']) == 4) { {
$replacement_postcode = "0" . $row['postcode'];
}
if($replacement_postcode != "") {
mysql_query("UPDATE customers SET
postcode = '" . mysql_real_escape_string($replacement_postcode) . "'
WHERE id = '" . intval($row['id]) . "');
}
}
Although the SQL query is obviously far shorter, neater and more efficient the PHP code offers the benefits of much easier debugging and modification.
I have to admit that 9 times out of 10 I’ll write a PHP script. It may seem like a lot more characters to type, but I value the ease of debugging and find that I tend to write PHP as quickly as I can think it.
Whether to perform quick tasks like this with an SQL query or by writing a short PHP script is a decision which sounds insignificant, but if making the right decision saves me half an hour over the course of a week it can be the difference between a relaxed Friday afternoon game of Quake in the office, or working my socks off right up until 5:30.
