Error: Column count doesn't match value count at row 1

I am getting this error here is my code

if(isset($_POST['submit']))
    {
     $projTit=mysql_escape_string($_POST['projecttitle']);
     $projCat=mysql_escape_string($_POST['projectcategory']);
     $budget=intval(mysql_escape_string($_POST['budget']));
     $description=mysql_escape_string($_POST['editor1']);
     $query=sprintf("insert into projects value('%s','%s','%s',%d)",
            $projTit,$description,$projCat,$budget);
     if (!mysql_query($query)){
         die('Error: ' . mysql_error());
         }
        echo '<p class="record">Your Record has been Added<p>';
    }

    ?>

I have tried to write %d in '' but still not working.

php
mysql

2012-04-04 06:47
by Sunny Mark

What is your table structure? If you don't specify a column set you must provide values for all columns - Interrobang 2012-04-04 06:51

@Interrobang sorry i didn't understand what is a column set let me show my table structur - Sunny Mark 2012-04-04 06:54

Sorry, I meant if you don't specify which columns you are inserting into (using syntax like INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)) then you must specify values for all columns : - Interrobang 2012-04-04 06:55

@Interrobang i have edit and showed my table i am providing values for all column - Sunny Mark 2012-04-04 06:59

Your table has five columns. However, you are not providing a value for the Project_Id column. This gives you the error you mentioned.

I understand that you're not providing a value since it's likely a PRIMARY KEY that autoincrements. To tell MySQL that you are intentionally not passing a value for that column, you should add NULL as the first value.

value(NULL, '%s','%s','%s',%d)

However, you really should specifically name which columns you are inserting into in case you add a new column at a future date.

INSERT INTO projects (col1, col2, col3, col4) value ('%s','%s','%s',%d)

2012-04-04 07:04
by Interrobang

Thanks for help it is working - Sunny Mark 2012-04-04 07:10

You should really specify the column names or your app will break if you add a column later, even if it has a useable default value:

INSERT INTO projects
    (Project_Title, Project_Description, Project_Category, Project_Budget)
VALUES
    (...)

Especially since you have an auto_increment ID column it makes sense; otherwise you'd always have to specify it as NULL in your query.

The easiest (but also ugliest) fix would thus be:

$query=sprintf("insert into projects value (null, '%s','%s','%s',%d)",
        $projTit,$description,$projCat,$budget);

The good fix would be this:

$query = sprintf("
    INSERT INTO projects
        (Project_Title, Project_Description, Project_Category, Project_Budget)
    VALUES
        ('%s', '%s', '%s', %d)
    ", $projTit,$description,$projCat,$budget);

By the way, consider using PDO - then you can use something similar to a format string but don't have to deal with escaping.

2012-04-04 06:59
by ThiefMaster

Wrong. "VALUE is a synonym for VALUES in this context. Neither implies anything about the number of values lists, and either may be used whether there is a single values list or multiple lists." http://dev.mysql.com/doc/refman/5.5/en/insert.htm - Interrobang 2012-04-04 07:01

@Interrobang: Updated - ThiefMaster 2012-04-04 07:02

by not specifying column name and changing value to values is still not working - Sunny Mark 2012-04-04 07:03

try naming the columns you want to fill like

insert into projects (col1, col2, col3) values (...)

2012-04-04 06:49
by juergen d

yes naming the columns i am able to add values thanks for help - Sunny Mark 2012-04-04 07:07