PHP and SQL insert statement [duplicate]

Question

So far I have created the table and getting a successful message. However when I go to insert into the table, I get: Error inserting sample record into 'Users': Column count doesn't match value count at row 1.

This is the code for creating the table:

mysql_select_db($dbName, $dbConnection);
$sql = "CREATE TABLE ".$dbTable." (ID int(6) auto_increment NOT NULL, Username varchar(20) NOT NULL, Password varchar(20) NOT NULL, UserType int(1) NOT NULL, FirstName varchar(15) NOT NULL, LastName varchar(15) NOT NULL, DOB DATETIME NOT NULL, Phone varchar(15) NOT NULL, Department varchar(15),PRIMARY KEY(ID))";
if(mysql_query($sql, $dbConnection))
{
    echo("Table '".$dbTable."' created<br />");
}
else
{
    echo("Error creating table '".$dbTable."': ".mysql_error()."<br />");
}

And this is the code for inserting:

$sql = "INSERT INTO ".$dbTable." (Username, Password, UserType, FirstName, LastName, DOB, Department) VALUES (1, 'admin', 'admin', 1, 'Admin', 'Admin', '1900-01-01', '12345656', 'IT')";
if(mysql_query($sql, $dbConnection))
{
    echo("Added sample record to '".$dbTable."' table<br />");
}
else
{
    echo("Error inserting sample record into '".$dbTable."': ".mysql_error()."<br />");
}

You have 7 INSERTs and 9 VALUES - Plus, if the first value is for your AI, remove it. — Funk Forty Niner
– Funk Forty Niner, Commented Aug 27, 2014 at 12:34
@user3502333 If you would have googled the exact error message with site:stackoverflow.com behind it, you would have found several duplicates... google.com/… — Sumurai8
– Sumurai8, Commented Aug 27, 2014 at 12:38
Go on with mysql functions. I hope you are going to find right way when you will become a victim of sql injection — HddnTHA
– HddnTHA, Commented Aug 27, 2014 at 12:42

Rahul Tripathi · Accepted Answer · 2014-08-27 12:42:31Z

This line is the problem:

$sql = "INSERT INTO ".$dbTable." (Username,  //1
                                 Password,   //2
                                 UserType,  //3
                                 FirstName, //4
                                 LastName,  //5
                                 DOB,       //6
                                 Department) //7
        VALUES (1,             //1
                'admin',       //2
                'admin', //3
                 1, //4
                 'Admin',//5 
                 'Admin',   //6
                 '1900-01-01', //7
                 '12345656',  //8 You dont have column for this
                  'IT')"; //9  You dont have column for this

ie, the number of columns and the values are not matching. You have 7 columns and 9 values. So yuo need to add 2 more column names. Also do remember that you dont need to provide the value for auto incremented columns

The correct query may be like this:

$sql = "INSERT INTO ".$dbTable." (Username, Password, UserType, FirstName, LastName, DOB, Phone, Department) 
        VALUES ('admin', 'admin', 1, 'Admin', 'Admin', '1900-01-01', '12345656', 'IT')";

Note I have deleted ID as that is auto incremented

sridesmet · Accepted Answer · 2014-08-27 12:34:39Z

0

You forgot to addthe phone number column. The count of your insert values should match the count of the columns you specify in your insert query.

answered Aug 27, 2014 at 12:34

sridesmet

8959 silver badges20 bronze badges

Comments

Sean Missingham · Accepted Answer · 2014-08-27 12:35:42Z

0

You seem to be trying to insert 9 values into 7 columns. In your insert statement, you've left out your Phone and PRIMARY KEY(ID) columns

answered Aug 27, 2014 at 12:35

Sean Missingham

9481 gold badge7 silver badges19 bronze badges

Comments

user3934473 · Accepted Answer · 2014-08-27 12:36:43Z

0

Problem is in your INSERT statement...

Try this

$sql = "INSERT INTO ".$dbTable." (Username, Password, UserType, FirstName, LastName, DOB, Phone , Department) VALUES ('admin', 'admin', 1, 'Admin', 'Admin', '1900-01-01', '12345656', 'IT')";

As error itself says, You are not providing me exact number of values as many you have specified the columns

In short.

('col1', 'col2') VALUES ('val','val2')

Both MUST be same number of columns

In you case, You forgot to add the column name of the value 12345656

answered Aug 27, 2014 at 12:36

user3934473

Comments

Jens · Accepted Answer · 2014-08-27 12:37:16Z

0

You do not add the id in values.. and one phone is missing in the columns

INSERT INTO ".$dbTable." (Username, Password, UserType, FirstName, LastName,  DOB, phone, Department) VALUES ('admin', 'admin', 1, 'Admin', 'Admin', '1900-01-01', '12345656', 'IT')";

answered Aug 27, 2014 at 12:37

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

PHP and SQL insert statement [duplicate]

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Related