insert values fails

MMitchell

I am getting the following error:

   Column count doesn't match value count at row 1

when I try to run this:

 insert into onmylist_person values('value1', 'value2')

My table looks like this:

I know it is because I am only inserting 2 values and the table has 3, but I thought the auto_increment creted an incrementing number automatically.

I want the ID to just be a "counter" incrementing on its own, so i only have to insert the 2 fields.

How do I do this?

laserlight

Since you arent inserting with all the columns, you need to specify the columns:

INSERT INTO onmylist_person (username, person) VALUES ('value1', 'value2');

zac1333

Normally, when I have this scenario, I do it this way:

$query = "INSERT INTO tablename VALUES ('','$value1','$value2')";

The first '' allows MySQL to autoincrement the ID field.

bradgrafelman

But you still have to know how many fields there are, the order they are listed in SQL, etc.

Specifying the columns as laserlight showed is the easiest, safest, and best way to do inserts. What if you have 20 columns, and you only needed to insert a row with data in two of them? You'd have to know where in the list of 20 columns are the 2 that you need, and try to fill in everything else. Or, you can just specify the two columns you want to add data to and let MySQL take care of the other columns using their default values.

MMitchell

Thanks to all, that was the trick!

bradgrafelman

Don't forget to mark the thread resolved!

laserlight

The first '' allows MySQL to autoincrement the ID field.

The ID field is an int, not a string type. Your use of an empty string is incorrect, though MySQL may permit it. One could use NULL instead, but the best option is simply to leave out the ID field and let the autoincrement be performed automatically.