Dave Rowe’s Blog

Recently I encountered an issue at work.  I had recently merged some code, and someone who tested from start to finish said there was an error right off the bat.  Crap.

Looking at the error, my blood began to boil:

PHP Fatal error: Column count doesn't match value count at row 1

So, I investigated where this was happening, and found it.  Effectively, when you write an INSERT like this:

INSERT INTO table VALUES ('1', '2', '3');

The DB will automatically match your values up with the columns.  Except, you’ve effectively broken the code because any change to the schema will change the number of columns.  Second, you cannot guarantee the column order on a table, so you just broke any chance of compatibility with another DB back-end.  Especially, in the case of multiple branches of a project being updated, you must be defensive about coding practices against the database.  You cannot assume anything about the mainline of development beyond your branch.

So, a small lesson.  Anytime you’re writing an INSERT, always, always, always, declare the column listing like so:

INSERT INTO table (col_1, col_2, col_3) VALUES ('1', '2', '3');

Now, you see that the DB doesn’t have to think about which columns you’re populating, you told it directly.  This also guards against the change in column order, as well as, the addition of new columns.  Remember, if you’re adding columns that are set to ‘NOT NULL’, be sure to set a sane acceptable default, and you shouldn’t have compatibility issues with your old inserts.

Closing side benefit, I can look at this new INSERT, and know what the columns are.  Given the first, I’d have to go check the DB, and verify the column order, etc.  Always put column names in.  Always.