Insert value list does not match column list: 1136 Column count doesn't match value count

Question

I have this PHP-MySQL insert code:

$sqlTeeth = "INSERT INTO teeth (id_logged, patient_id, one, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve, thirteen, fourteen, fifteen, sixteen, seventeen, eightteen, nineteen, twenty, twone, twtwo, twthree, twfour, twfive, twsix, twseven, tweight, twnine, thirty, thone, thtwo, date_now) VALUES (:id_logged, :patient_id, :one, :two, :three, :four, :five, :six, :seven, :eight, :nine, :ten, :eleven, :twelve, :thirteen,
            :fourteen, :fifteen, :sixteen, :lone, :ltwo, :lthree, :lfour, :lfive, :lsix, :lseven, :leight, :lnine, :lten, :leleven, :ltwelve, :lthirteen,
            :lfourteen, :lfifteen, :lsixteen)";
        $sqlTeethStmt = $conn->prepare($sqlTeeth);
        $sqlTeethStmt->bindValue(":id_logged", $id_logged);
        $sqlTeethStmt->bindValue(":patient_id", $patient_id);
        $sqlTeethStmt->bindValue(":one", $one);
        $sqlTeethStmt->bindValue(":two", $two);
        $sqlTeethStmt->bindValue(":three", $three);
        $sqlTeethStmt->bindValue(":four", $four);
        $sqlTeethStmt->bindValue(":five", $five);
        $sqlTeethStmt->bindValue(":six", $six);
        $sqlTeethStmt->bindValue(":seven", $seven);
        $sqlTeethStmt->bindValue(":eight", $eight);
        $sqlTeethStmt->bindValue(":nine", $nine);
        $sqlTeethStmt->bindValue(":ten", $ten);
        $sqlTeethStmt->bindValue(":eleven", $eleven);
        $sqlTeethStmt->bindValue(":twelve", $twelve);
        $sqlTeethStmt->bindValue(":thirteen", $thirteen);
        $sqlTeethStmt->bindValue(":fourteen", $fourteen);
        $sqlTeethStmt->bindValue(":fifteen", $fifteen);
        $sqlTeethStmt->bindValue(":sixteen", $sixteen);

        $sqlTeethStmt->bindValue(":lone", $lone);
        $sqlTeethStmt->bindValue(":ltwo", $ltwo);
        $sqlTeethStmt->bindValue(":lthree", $lthree);
        $sqlTeethStmt->bindValue(":lfour", $lfour);
        $sqlTeethStmt->bindValue(":lfive", $lfive);
        $sqlTeethStmt->bindValue(":lsix", $lsix);
        $sqlTeethStmt->bindValue(":lseven", $lseven);
        $sqlTeethStmt->bindValue(":leight", $leight);
        $sqlTeethStmt->bindValue(":lnine", $lnine);
        $sqlTeethStmt->bindValue(":lten", $lten);
        $sqlTeethStmt->bindValue(":leleven", $leleven);
        $sqlTeethStmt->bindValue(":ltwelve", $ltwelve);
        $sqlTeethStmt->bindValue(":lthirteen", $lthirteen);
        $sqlTeethStmt->bindValue(":lfourteen", $lfourteen);
        $sqlTeethStmt->bindValue(":lfifteen", $lfifteen);
        $sqlTeethStmt->bindValue(":lsixteen", $lsixteen);

        $sqlTeethStmt->execute();

When I add something to database, I got this error:

SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

Any Help is appreciated.

Remove

What is not clear? The message is plain and simple: Column count doesn't match value count – Hesitancy

It's more than a little worrying that your column list is getting this big. What's going on in there? Is that properly normalized? If this is becoming routine you should investigate using an ORM to encapsulate this for you. – Shelf

Check the bindValue(':foo:, $value); if it's field number coincides with your SQL fields – Nobell

I would restructure your database, or are there never (i mean, really never) more columns needed as used in your code? – Cithara

@Shelf this table as you see, is for teeth. Every patient have 32 teeth, and dr update the info about them all. And sometimes a dr need to see what he make on a specific tooth. How would do it other than this ? – Remove

@Shelf I am really waiting your response. How would you do it ? – Remove

@Remove I'd use a development framework like Laravel to help organize this better. They usually have a database layer that allows you to do things like $record->save() instead of this gigantic sprawling amount of code you have here. – Shelf

Answer 1

Your database table has 35 columns

id_logged, patient_id, one, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve, thirteen, fourteen, fifteen, sixteen, seventeen, eightteen, nineteen, twenty, twone, twtwo, twthree, twfour, twfive, twsix, twseven, tweight, twnine, thirty, thone, thtwo, date_now

Where as the values you are passing are 34 columns

VALUES (:id_logged, :patient_id, :one, :two, :three, :four, :five, :six, :seven, :eight, :nine, :ten, :eleven, :twelve, :thirteen,
        :fourteen, :fifteen, :sixteen, :lone, :ltwo, :lthree, :lfour, :lfive, :lsix, :lseven, :leight, :lnine, :lten, :leleven, :ltwelve, :lthirteen, :lfourteen, :lfifteen, :lsixteen)

This mismatch of columns is giving you the error.

You forgot to pass the value for date_now column. once you pass it error will be resolved

Underhill

Answer 2

SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

The previous answer absolutely explains the underlying problem but maybe it is helpful to understand the error report as well. It is in two parts:

Insert value list does not match column list: the SQL statement consists of first a list of columns then a list of values, these two do not match.

1136 Column count doesn't match value count at row 1 the problem is that the number of listed values is not the same as the number of listed columns.

THE SQL error codes are rather tersely written and can seem obscure, but once you break them down the meaning is usually quite straightforward.

Honeyman

Answer 3

another 'usercase', I had triggers in place to store table changes ... adding a new column gave this error (although the new field/value count was correct) ... I had to delete the triggers to get the new field accepted ...

Pugging

Thank you so so much. I wasted precious time going over my code and couldn't spot anything! Jeeez. – Wiretap

Answer 4

Branching off @webMan's brilliant answer here above.

If you have an insert trigger that executes before/after an insert in your table. Make sure your trigger's query follows the format:

INSERT INTO table_name (col1, col2) VALUES (value1, value2)

The error would be coming from the trigger query.

Wiretap

Answer 5

in my case, this error occurred when I used Seeder and forgot to remove one field that removed before from all other items in Seeder

Declamatory