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

Brainstormpc
Brainstormpc used Ask the Experts™
on
This querry in MySQL won't work, it causes the error  message

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

INSERT INTO leads (id, first_name, last_name, email, phone, address, city, state, zip, question, date) VALUES ('NULL', 'Frank', 'Furter','me@me.com' '555-555-5555', '13 Cherry Tree Ln.', 'Grants Pass', 'OR', '97567', 'This is a test', '2010-07-05')

this is the query taken out of the php code it was in.
I don't see any disparity between the columns and the values.  the database is the following

table name: leads

fields:
id                  ( int , autoincrement, key
first_name    (varchar)
last_name     (varchar)
email             (varchar)
phone             (varchar)
address       (varchar)
city             (varchar)
state          (varchar)
zip             (varchar)
question    (mediumtext)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Remove the quotation marks around NULL

Commented:
Autoincrement fields can take NULL or an INT. In this case you are giving it a string, and MySQL is smart enough to assume that you are trying to set first_name = 'NULL'. You could leave out the 'id' and 'NULL' parts and it will sort itself out
Most Valuable Expert 2011
Top Expert 2016

Commented:
If you have an auto_increment key, it is a good idea (as a matter of design policy) to omit the ID when you do inserts.  That is why it is set up as auto_increment - so the data base will choose the value for you.  You can retrieve the id from the data base with this:

http://us.php.net/manual/en/function.mysql-insert-id.php

Best, ~Ray
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thanks for your speedy responses, I tried all three suggestions one at time, and none of them fixed the problem.  This site is on a Plesk server, running php and mysql.  However I have never dealt with plesk before could these problems have something to do with either the plesk server or the hosts php or mysql settings?  
Most Valuable Expert 2011
Top Expert 2016

Commented:
Are any of the column names "reserved words" - I have run into this sometimes and the error messages are pretty obscure.
Most Valuable Expert 2011
Top Expert 2016

Commented:
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

You might want to try quoting the column names with backticks.  If that still fails, please post the CREATE TABLE script along with an example of the new, but still failing, INSERT query.  I will install it on my server and test.

I do not think Plesk has anything to do with this issue.

Best regards, ~Ray

Author

Commented:
Thanks Ray,

Your advice has been a huge help,  quoting with backticks removed the error, there is no error message, however it still doesn't upload the data and almost acts like the full php wasn't executed, but now messages appear to help me figure out the error in my code, ( as side point do you recommend any books or courses, I really want to get better at this and finding good resources has been hard. )  I am posting my php file contents below.  Any help you can give or comments on best practices will be welcome.  I want to learn and improve.  Thanks

 $first_name = $_POST["first_name"];
 
 $last_name = $_POST["last_name"];
 
 $email = $_POST["email"];
 
 $phone = $_POST["phone"];
 
 $address = $_POST["address"];
 
 $city = $_POST["city"];
 
 $state = $_POST["state"];
 
 $zip = $_POST["zip"];
 
 $question = $_POST["question"];
 
 $date = date("Y-m-d");


include ("db_connect1.php");
db_include1();


if (!$first_name || !$last_name || !$email){

     echo 'You have not entered all the required details.<br />'

         .'Please go back and try again.';

     exit;

 }

$query = "INSERT INTO leads ('first_name', 'last_name', 'email', 'phone', 'address', 'city', 'state', 'zi'p, 'question', 'date') VALUES ('$first_name', '$last_name','$email' '$phone', '$address', '$city', '$state', '$zip', '$question', '$date')";


  if (!mysql_query($query))  {

        die('Error: ' . mysql_error());

        }else{

      echo '<br /><br /><h2>Your Information Request<br /> Has Been Succesfully Processed</h2>';
     
     

}
Most Valuable Expert 2011
Top Expert 2016

Commented:
I recommend this book without reservation!

http://www.sitepoint.com/books/phpmysql4/
Most Valuable Expert 2011
Top Expert 2016
Commented:
Here is a teaching example script that shows how to do many of the basics in PHP and MySQL.  Please read it over - code, comments and especially the man pages.  It should help you get a grip on the things you are trying to do there.  Hint: error checking and data visualization are a big part of successful programming!

Best, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
   echo "<br/>QUERY FOUND NO DATA: ";
   echo "<br/>$sql <br/>";
}
else
{
   echo "<br/>QUERY FOUND $num ROWS OF DATA ";
   echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
   var_dump($row); // MAN PAGE: http://us2.php.net/manual/en/function.var-dump.php
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
// MAN PAGE: http://us.php.net/mysql_fetch_array
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES (\"$safe_username\")";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-insert-id.php
$id  = mysql_insert_id($db_connection);

Open in new window

Author

Commented:
Thanks Ray!

I just bought the book and have been reading it, it is excellent just what I was needing.  Thanks for the example I will study it.  I know it is an error I have created, so I really wanted help to know how to figure it out myself you have helped me do that.  Your help and guidance has been invaluable.  Thanks

Author

Commented:
Very knowledgeable and a good instructor as well.  I can't say thanks enough.  
Most Valuable Expert 2011
Top Expert 2016

Commented:
Thanks for the points and for your kind words.  Glad I could help, ~Ray

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial