Column count doesn't match value count at row 1

Question

i do have an additional column called "id" but it is a primary key with auto increment, if i put that in the values it adds the id but the the firstname data is always 0 and the firstname data enters the lastname and the lastname data enters the username and the username data enters the email field and the email data enters the password field and the password data enters the confirmpassword field and finally the confirmpassword data and country data enters the country field. Below are my codes ?>

<?php

 //connection to the database server
   $hostname="localhost";
   $user="root";
   $password="";
   $connection = mysql_connect($hostname, $user, $password) or die ("cannot connect to mysql database server");
   //selection of database      
   mysql_select_db("jewelgallery", $connection) or die ("cannot reach jewelgallery database");
   $firstname = $_POST['firstName'];
   $lastname = $_POST['lastName'];
   $username = $_POST['username'];
   $email = $_POST['email'];
   $password1 =$_POST['password'];
   $password2 =$_POST['confirmPassword'];
   $country =$_POST['country'];

   $sql2="select * from customer_account where username = '$username'";

   $results = mysql_query($sql2, $connection) or die(mysql_error());

    $numOfRecords1 = mysql_num_rows($results);

    $_SESSION["username"] = $username;
    if ($numOfRecords1 != 0)
    {
        echo "<h3>This Username ". $_SESSION["username"]." Has been chosen by another user</h3> <a href=registercustomer.html> Please Try Again </a>";
        header("Refresh:5;url=registercustomer.html");
        exit;

    }  

    $sql="insert into customer_account(firstname, lastname, username, email, password, confirmpassword, country)
    Values('$firstname', '$lastname', '$username', '$email', '$password1', '$password2' '$country')";

     mysql_query($sql, $connection) or die(mysql_error());


    mysql_close($connection);

    echo "Registration Successful. <a href=../index.html> Continue </a>";
    header("Refresh:5;url=../index.html");

?>

It is very possible that single-quotes in any of those input variables are causing the `VALUES ()` list to appear as though it has more columns than it actually does. This is another reason [taking steps to prevent SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) is so important - escaping those variables against injection would also escape quotes from breaking the SQL. To see what the query looks like before execution, check `echo $sql;` — Michael Berkowski, Sep 07 '14 at 14:31
At a minimum, if you use a pattern like `$country = mysql_real_escape_string($country);` on _all_ of those variables, you'll get some injection protection and the query may work out (please post the output of `echo $sql;` anyway...) but see also [Why shouldn't I use mysql_* functions in PHP](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), as new code should not be written using the deprecated `mysql_*()` extension. — Michael Berkowski, Sep 07 '14 at 14:35
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?lq=1 — Hanky Panky, Sep 07 '14 at 14:39

score 4 · Accepted Answer · answered Sep 07 '14 at 14:28

You're missing a comma:

 '$password2' '$country')";
           ^^^^^
           HERE

Corrected:

$sql="insert into customer_account(firstname, lastname, username, email, password, confirmpassword, country)
Values('$firstname', '$lastname', '$username', '$email', '$password1', '$password2', '$country')";

Column count doesn't match value count at row 1

1 Answers1