How to fix " Invalid Query: Column Count Doesn't Match Value Count At Row 1 " error in MySQL

Every time we use MySQL, particularly with perl or php, here at HTMLfixIT.com (that means Franki and Don) we come to like it more. The power and flexibility provided allow some great opportunities for interactive programming. Unfortunately, anytime you do something, you find errors do creep up.
One common error in MySQL is this: Invalid Query: Column Count Doesn't Match Value Count At Row 1.
Fortunately this one is easy to fix!

Avoid False Assumptions.

When I first encountered this error, I made the false assumption that I had too few columns in the table in my database to receive the data being posted. In fact, the opposite was true. My query was posting too few items for the columns. A common mistake (especially for beginners, but for the deadline pressed expert as well) is to forget the unique id number, shown in red below, when posting the query. If you have an auto-incrementing id number for each row, you will do well to include an empty set of quotes to match in your query. So it might look like this:

$query = "INSERT INTO table (id, first_name, last_name)
          VALUES('','$_POST[first_name]','$_POST[last_name]')";

Don´t Add Data Columns.

Because phpMyAdmin, for example, makes it so easy to modify data tables on the fly in MySQL, many designers make the mistake of adding columns after the fact without thought or deleting the extra ones (Don does this all the time) while more careful designers carefully layout the project and required fields in advance, leaving a field or two extra for future adjustment (you guessed it ... Franki). If you add or subtract a field, then all queries posting a row of data may have to be modified to match.

Make Sure the Column Names Each Have a Matching Value and Vice Versa.

Make sure you don´t forget one or the other of the value or column name.

Good:

$query = "INSERT INTO table (id, first_name, last_name)
          VALUES('','$_POST[first_name]','$_POST[last_name]')";

Bad:

$query = "INSERT INTO table (first_name, last_name)
          VALUES('','$_POST[first_name]','$_POST[last_name]')";

Bad:

$query = "INSERT INTO table (id, first_name, last_name)
          VALUES('$_POST[first_name]','$_POST[last_name]')";

Because each of these bad examples is missing a corresponding part. If you check off the few things mentioned here, you will have your problems sorted in no time. Don´t give up, get mad and conquer.

Note: Taking the value of $_POST[first_name] (or any other user supplied variable) and dumping it directly into a MySQL Database is a really REALLY BAD idea. We used it just to show an easy to follow example, but NEVER trust anything given to you by a stanger, and that includes form data from strangers. The above example could be tricked into inserting other MySQL commands inside yours. Always validate user supplied data. ALWAYS!

To see the rest of the tutes, head Back to the Tutorial Index

This site is totally free to use, you have absolutely no moral or legal obligations to help us continue.
There are however, some costs involved in running the site.

<random humor>
Plus Franki is trying to keep his boat floating.
</random humor>

So if this site helped you find your way, perhaps you could consider contributing to our costs. Whatever amount you feel this site was worth to you would be just wonderful.
Use PayPal if you do decide to share and help us with the costs and in appreciation for our time and attention, or alternatively buy a book from our Bookstore..

  Time in Don's part of the world is:   May 9, 2008, 9:08 pm
  Time in Franki's part of the world is: May 10, 2008, 10:08 am
  Don't worry neither one sleeps very long!