A recent task to investigate and fix a bug within a PHP / MySQL website highlighted a simple but important rule when writing SQL which I’d like to share.
The website used PHP and MySQL in a very normal way, using MySQL to run queries and PHP to display the results. The bug was reported not long after some updates had been completed on another part of the website. After reviewing the logs it was obvious what the error was but the cause was not as simple to find.
The error message is very clear. This is caused when a query uses 2 or more tables with the same column names which have not been prefixed.
A simple example
A query to show the competitions with their events is very simple
Select id, competition_name, date, event_name from competition c left join event e on e.competition_id = c.id
This gives us
|13||Competition 1||2013-04-21||Event 1|
|15||Competition 2||2013-04-22||Event 2|
|16||Competition 3||2013-04-23||Event 3|
So the query works and everything is cool. Now imagine a few months or even years later an id field is added to the event table, seems OK right?
So the event table now looks like;
And BAAM, the original query which worked for months or years has now stopped working and has started filling up the error logs with;
Column id in where clause is ambiguous
This is because both tables now return id and MySQL doesn’t make the choice for you.
The solution isn’t to rename your columns, there’s nothing wrong with having 2 id columns. The solution is to prefix the column names with the table like so;
Select c.id, c.competition_name, c.date, e.event_name from competition c left join event e on e.competition_id = c.id
If the query was written like this from the beginning then this issue would never have happened. A very simple but effective method of future proofing your SQL.