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!
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]')";
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 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