Column count doesn't match value count at row 1

PHP
1

Hi,

I keep getting this error message when trying to execute a registration script I created. “MySQL Error: Column count doesn’t match value count at row 1”. I’m quite new to php programming, and I’ve done plenty of google searches on this, and came up with a few solutions, none of which worked for me. My original error was that it would return the final error - being that it could not complete my registration and to try again, so I included the or die which then came up with the column count error.

I’m using php 5, and mysql 5 and creating the tables through phpmyadmin through my host. The SQL (generated from phpmyadmin) is below:

CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL auto_increment,
email varchar(255) collate utf8_unicode_ci NOT NULL,
firstname varchar(50) collate utf8_unicode_ci NOT NULL,
lastname varchar(50) collate utf8_unicode_ci NOT NULL,
age int(2) NOT NULL,
password varchar(20) collate utf8_unicode_ci NOT NULL,
location varchar(50) collate utf8_unicode_ci NOT NULL,
gender varchar(11) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY email (email),
KEY lastname (lastname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=‘table of users’ AUTO_INCREMENT=1 ;

and the phpscript:

<?php

$dbhost = “xxxxx”; // name of the host
$dbname = “xxxxx”; // database name
$dbuser = “xxxxx”; // username for database
$dbpass = “xxxxxx”; // database password

mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());
mysql_select_db($dbname) or die("MySQL Error: " . mysql_error());

// check if email exists in the table

$checkemail = mysql_query(“SELECT * FROM users WHERE email = ‘$_POST[email]’”);

//if there is a row which matches the email print the email address exists

if(mysql_num_rows($checkemail) == 1)

{

echo "email adddress already exists in database!";

}

elseif(mysql_num_rows($checkemail) == 0) //if there is no matching row in the table

{

	$email = mysql_real_escape_string($_POST['email']);
	$Fname = mysql_real_escape_string($_POST['Fname']);
	$Lname = mysql_real_escape_string($_POST['Lname']);
	$password = md5(mysql_real_escape_string($_POST['password']));
	$location = mysql_real_escape_string($_POST['location']);
	$age = $_POST['age'];
	$gender = $_POST['gender'];

$register = mysql_query(“INSERT INTO users (id, email, firstname, lastname, age, password, location, gender) VALUES (‘’, ‘$email’, ‘$Fname’, ‘$Lname’, ‘$age’ ‘$password’, ‘$location’, ‘$gender’)”) or die("MySQL Error: " . mysql_error());
// run the query

}

if($register) //if the query turns out to be true then print success etc

{

	echo "&lt;h2&gt; Success! &lt;/h2&gt;";
	echo "&lt;p&gt;  Your account was successfully created!";
	
	}
	
	
else  // otherwise, return the error message

{
	
		echo "&lt;h2&gt; Error &lt;/h2&gt;";
		echo "&lt;p&gt; Sorry, your registration failed.  Please go back and try again. &lt;/p&gt;";
		
		
	}



?&gt;

I’d much appreciate any help you could give me, as I’m quite stuck. Also any tips/pointers would be much appreciated also.

Regards

2

On this line where you execute the INSERT query which is generating that error:

$register = mysql_query("INSERT INTO `users` (id, email, firstname, lastname, age, password, location, gender) VALUES ('', '$email', '$Fname', '$Lname', '$age' '$password', '$location', '$gender')") or die("MySQL Error: " . mysql_error());

The reason the column and value counts don’t match is that you’re missing a comma between ‘$age’ and ‘$password’.

Secondly, since “id” is an auto-increment column, you can omit it from that query entirely and it’ll be set automatically.

Thirdly, since “age” is numeric, you don’t need any quotes around its value. Here’s the updated query:

$register = mysql_query("INSERT INTO `users` (email, firstname, lastname, age, password, location, gender) VALUES ('$email', '$Fname', '$Lname', $age, '$password', '$location', '$gender')") or die("MySQL Error: " . mysql_error());
3

Thank you so much for the speedy reply! It works! I’ve been trying to figure that out for hours, honestly you’ve made my day. I almost gave up. Thanks so much!!

4