SQL query fails to insert with same column count, works with fewer columns inserted

In the example here, NULL values are normally other variables I pass through. I tried to pass NULL to see if that was the problem as those are the only values that can be NULL, everything else is required.

mysql_query("INSERT INTO `imstillr_crm`.`customerinfo` 
            (`id`, `companyname`, `primarycontact`, `primaryemail`, 
             `prefphone`, `secondarycontact`,开发者_开发技巧 `secondaryemail`, `optionalphone`, 
             `department`, `website`) 
           VALUES 
             (NULL, '".$company."', '".$primarycontact."', '".$primaryemail."', 
              '".$prefphone."', 'NULL', 'NULL', 'NULL, 
              '".$department."', '".$website."')");

I just tried this with fewer variables and it worked. I'm kinda confused. I even inserted a record and copied the SQL string and added my variables.

I think you've made a mistake here:

'NULL, '".$department.

Presumably you intended

'NULL', ".$department."

BUT: this looks as though you are attempting to insert the literal text "NULL" into your database which I assume is not what you intend. NULL is a keyword so you can just use the bare word NULL wherever you are inserting a null value (so no quotes required around it).

There's more though, at the risk of distracting you from your current issue, you really should not execute SQL statements by composing a string as you are doing. This is how SQL injection attacks succeed. A better way is to use PDO with prepared statements. Here's a quick description of how to use it:

http://www.php.net/manual/en/pdo.prepared-statements.php

The main advantages are:

1. You remove the explicit dependency on Mysql created by the mysql_* functions
2. You can avoid SQL injection attacks.
3. Your code should be easier to read without the complex string composition

Just for completeness, here's how your code would look with PDO

$stmt = $dbh->prepare("INSERT INTO imstillr_crm.customerinfo (id, companyname, primarycontact, primaryemail, prefphone, secondarycontact, secondaryemail, optionalphone, department, website) VALUES (:id, :companyname, :primarycontact, :primaryemail, :prefphone, :secondarycontact, :secondaryemail, :optionalphone, :department, :website)");
$stmt->bindParam(":id", NULL);
$stmt->bindParam(":companyname", $company);
$stmt->bindParam(":primarycontact", $primarycontact);

...

$stmt->execute();

Notice how you don't need to put quotes around the values. They might contain various characters so as quotes, semi colons etc. and these won't cause any problems in your database (they may cause problems elsewhere in your app but that's another discussion).

I don't think you can put NULL in quotes. I think you need to remove the quotes around NULL otherwise MySQL (I'm guessing MySQL) will treat them as strings.

A ' is missing after the last NULL Corrected code:

mysql_query("INSERT INTO `imstillr_crm`.`customerinfo` (`id`, `companyname`, `primarycontact`, `primaryemail`, `prefphone`, `secondarycontact`, `secondaryemail`, `optionalphone`, `department`, `website`) VALUES (NULL, '".$company."', '".$primarycontact."', '".$primaryemail."', '".$prefphone."', 'NULL', 'NULL', 'NULL', '".$department."', '".$website."')");

You have mixed up the ' and ,:

'NULL, '".$department."'

Should be:

'NULL', '".$department."'

Right before $department you have 'NULL, '" you probably want to move the quote in before the comma.