James Titcumb

It annoys me when I see tutorials for learning PHP with code like this:

$fd = @file("doesnotexist");

and this:

$md = mysql_connect("localhost", "user", "pass") or die("Could not connect!");

It’s bad practice in the real programming world and shouldn’t happen. The at symbol (@) is used to silence any errors caused by calling a function as shown above, when proper error handling should be implemented. Similarly, the practice of using “or die” to error check needs to stop being taught.

Using this language in PHP really needs to stop because there are much better ways to handle errors, such as using exceptions, or any sort of error handling except this really. My personal favourite is using an exception and catching it further down the stack. There’s loads of tutorials on error handling so I won’t go into it, but I just wanted to vent my hatred for this poor excuse for shoddy coding.

And while I’m at it, stop using mysql_* functions and start using PDO!

I didn’t even think you could do this:

UPDATE table SET something = REPLACE(something,'oldtext','newtext');

How handy! For replacing text in MySQL tables that is… not for anything else really.

I’m just writing a query at the moment that filters out test records from a database. These records have a foreign-keyed value that identifies test apps, e.g. a table with:

id  name
1   TEST
2   ANOTHER_VALUE
3   SOMETHING_ELSE

This is joined onto the main data table, sensibly with a query like this:

SELECT *
FROM the_data
LEFT JOIN the_values ON the_data.some_value = the_values.id
WHERE the_values.name != 'TEST'

The gotcha is however, that this doesn’t work as you’d expect. If a record in “the_data” table doesn’t have a corresponding entry in “the_values” e.g. if “the_data.some_value” is 0 (as in my instance, it doesn’t need to have a corresponding value), then these entries are completely excluded. The solution (simple once you understand how it works…) is to check for NULL values too:

SELECT *
FROM the_data
LEFT JOIN the_values ON the_data.some_value = the_values.id
WHERE (the_values.name IS NULL OR the_values.name != 'TEST')

A quick tip – you can import a CSV into a MySQL table pretty easily. The file must exist on the server and you must create the table first, but this command will import your CSV into a MySQL table easily:

LOAD DATA LOCAL INFILE '/tmp/data.csv'
INTO TABLE `data_table`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Say you have a table of information, and another table that has more information, there’s a clever way of getting data from, one, the other, or both. It’s nothing new really at all, more of a reference thing for me… Say you have two tables:

common_table:

uncommon_table:

Joining these is a standard operation, and if we wanted all records, that may or may not have a postcode, we’d:

SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id

And that would give us:

Note carefully the fact that the uncommon_table values in the second row are NULL. We can use this to distinguish which records have entries in the uncommon_table, and which don’t. Then we can form queries based on this, for example if you wanted a report of each user with their postcode, ignoring those without postcodes:

SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id WHERE u.id IS NOT NULL

And converseley, find those people who haven’t got a postcode entry:

SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id WHERE u.id IS NULL

Knowing this can be handy, and sometimes quite powerful.