PHP - Column count doesn't match value count.

Question

hello this my code.

print_r($_POST);
foreach ($_POST as $key => $value){
  echo $$key  =  $value;
}

echo $dpopName1= json_encode($_POST['dpopName']);
echo $dpopPosition1= json_encode($_POST['dpopPosition']);
echo $dpopLocation1= json_encode($_POST['dpopLocation']);

    if($sub_cat=='all') {
    $sub_cat='';    
    }

//$sql = mysql_query("INSERT INTO venders(vender_name, contact_personName,category, subcategory,email,contact_no,address,city,state,pin_code) VALUES ('$vendor_name', '$cont_personName','$cat_type','$sub_cat','$vendor_email', '$contact_no','$vendor_address','$state','$city','$vendor_pin')") or die(mysql_error());
  $insertQuery=mysql_query("INSERT IGNORE INTO `venders`(`vender_name`, `vendor_ddNo`, `vendor_ddDate`,     `vendor_regid`, `dateCorp`, `contact_personName`, `category`, `subcategory`, `email`, `contact_no`, `fax_no`,      `website`, `address`, `city`, `taluka`, `state`, `pin_code`, `vendor_Baddress`, `Bstate`, `Bcity`, `Bvendor_pin`,      `Btaluka`, `Bcontact_no`, `Bfax_no`, `Bvendor_email`, `Bwebsite`, `govtUndertake`, `stateUndertake`, `publiclCompany`,      `privatelCompany`, `copSociaty`, `partnerFirm`, `propritorship`, `anyOtherspecify`, `dpopName`, `dpopPosition`,       `dpopLocation`, `panDetails`, `panRequireFor`, `serviceTaxregDetails`, `serviceTaxrequiredFor`, `pfRegisterDetails`,       `pfRequireFor`, `excDutyreg`, `excRequireFor`, `labourLicensedetails`, `licencePanrequireFor`, `stvRegNo`,       `stvRequireFor`, `stvregDateDetails`, `stvregDateRequiredFor`, `orgStrngth`, `NameofCompany`, `NameofBank`,       
     `nameOfbankBranch`, `cityofMB`, `accountNo`, `accountType`, `bankBranchIfsccode`, `micodeofBankbranch`,       `otherBankerDetails`) 
     VALUES ('$vendor_name','$vendor_ddNo','$vendor_ddDate','$vendor_id','$dateCorp','$cont_personName','$vendor_address',      '$cat_type','$sub_cat','$vendor_email','$contact_no','$fax_no','$website','$vendor_address','$city','$taluka','$state','$vendor_pin',      '$vendor_Baddress','$Bstate','$Bcity','$Bvendor_pin','$Btaluka','$Bcontact_no','$Bfax_no','$Bvendor_email','$Bwebsite','$govtUndertake',      '$stateUndertake', '$publiclCompany',      '$privatelCompany', '$copSociaty', '$partnerFirm', '$propritorship', '$anyOtherspecify', '$dpopName1', '$dpopPosition1',       '$dpopLocation1','$panDetails', '$panRequireFor', '$serviceTaxregDetails', '$serviceTaxrequiredFor', '$pfRegisterDetails',       '$pfRequireFor', '$excDutyreg', '$excRequireFor', '$labourLicensedetails', '$licencePanrequireFor', '$stvRegNo',       
     '$stvRequireFor', '$stvregDateDetails', '$stvregDateRequiredFor', '$orgStrngth', '$NameofCompany', '$NameofBank',       '$nameOfbankBranch', '$cityofMB', '$accountNo', '$accountType', '$bankBranchIfsccode', '$micrCodeofBankbranch',       '$otherBankerDetails'
    )")  or die(mysql_error());

Array to string conversion error
Column count doesn't match value count at row 1.

I tried following to solve the prooblem:

for solving 1st error i use json encode to convert the array into json string then i place the json string variable into insert table.
for column count error i checked wheather my database id has a primary key or not and it has a primary key then also i count all the value and column and also i cross check all the syntax but i found nothing.

Count the number of columns and values. They are ***not*** the same. Also, there is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) to ensure the functionality of your project in the future. — Amal Murali, Apr 22 '14 at 06:57
@AmalMurali thanks for the replay but i have already count all the values and column and that is correct. — sach9949, Apr 22 '14 at 07:02
Okay, is there a secret command that can generate the above text provided by @Amal Murali. I swear I see that text word for word all across the cluster here. — Ryan, Apr 22 '14 at 07:11
try to check (and see error) on smaller query, no need to show here 'all code you got' =\ also you can recheck your query in mysql IDE (or pma) to get errors — user1954544, Apr 22 '14 at 07:58

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

A better way... to insert, when you're inserting alot of columns is... to use INSERT INTO SET, because then you have a better idea of which columns are missing.. than meticulously going through your mapped INSERT, and checking whether you have every column to every value.

INSERT INTO sometable
   SET field1 = 'value1',
       field2 = 'value2',
       field3 = 'value3'

(Milov, 2004).

It also helps, to escape every single value that you are inserting (But keep in mind... it is recommended to use PDO over MySQL Real Escape).

edited May 23 '17 at 12:11

Community

1
1

answered Apr 22 '14 at 07:14

classicjonesynz

4,012
5
38
78

+1 ... wow, I'm glad I woke up this morning to be able to learn this. – nl-x Apr 22 '14 at 07:41

rubo77 · Accepted Answer · 2014-04-22T08:20:59.537

If any of your variables would contain a ' that would destroy your query.

Sure it would be best to use DBO, but since I guess you are trying to implement something here into existing code, you should try to debug this more:

print_r($_POST);
foreach ($_POST as $key => $value){
  echo $$key  =  mysql_real_escape_string($value);
}

echo $dpopName1= mysql_real_escape_string(json_encode($_POST['dpopName']));
echo $dpopPosition1= mysql_real_escape_string(json_encode($_POST['dpopPosition']));
echo $dpopLocation1= mysql_real_escape_string(json_encode($_POST['dpopLocation']));

debug with:

$query="INSERT IGNORE INTO `venders`(`vender_name`, `vendor_ddNo`, `vendor_ddDate`, `vendor_regid`, `dateCorp`, `contact_personName`, `category`, `subcategory`, `email`, `contact_no`, `fax_no`, `website`, `address`, `city`, `taluka`, `state`, `pin_code`, `vendor_Baddress`, `Bstate`, `Bcity`, `Bvendor_pin`, `Btaluka`, `Bcontact_no`, `Bfax_no`, `Bvendor_email`, `Bwebsite`, `govtUndertake`, `stateUndertake`, `publiclCompany`, `privatelCompany`, `copSociaty`, `partnerFirm`, `propritorship`, `anyOtherspecify`, `dpopName`, `dpopPosition`, `dpopLocation`, `panDetails`, `panRequireFor`, `serviceTaxregDetails`, `serviceTaxrequiredFor`, `pfRegisterDetails`, `pfRequireFor`, `excDutyreg`, `excRequireFor`, `labourLicensedetails`, `licencePanrequireFor`, `stvRegNo`, `stvRequireFor`, `stvregDateDetails`, `stvregDateRequiredFor`, `orgStrngth`, `NameofCompany`, `NameofBank`, `nameOfbankBranch`, `cityofMB`, `accountNo`, `accountType`, `bankBranchIfsccode`, `micrCodeofBankbranch`, `otherBankerDetails`) VALUES ('$vendor_name','$vendor_ddNo','$vendor_ddDate','$vendor_id','$dateCorp','$cont_personName','$vendor_address', '$cat_type','$sub_cat','$vendor_email','$contact_no','$fax_no','$website','$vendor_address','$city','$taluka','$state','$vendor_pin', '$vendor_Baddress','$Bstate','$Bcity','$Bvendor_pin','$Btaluka','$Bcontact_no','$Bfax_no','$Bvendor_email','$Bwebsite','$govtUndertake', '$stateUndertake', '$publiclCompany', '$privatelCompany', '$copSociaty', '$partnerFirm', '$propritorship', '$anyOtherspecify', '$dpopName1', '$dpopPosition1', '$dpopLocation1','$panDetails', '$panRequireFor', '$serviceTaxregDetails', '$serviceTaxrequiredFor', '$pfRegisterDetails', '$pfRequireFor', '$excDutyreg', '$excRequireFor', '$labourLicensedetails', '$licencePanrequireFor', '$stvRegNo', '$stvRequireFor', '$stvregDateDetails', '$stvregDateRequiredFor', '$orgStrngth', '$NameofCompany', '$NameofBank', '$nameOfbankBranch', '$cityofMB', '$accountNo', '$accountType', '$bankBranchIfsccode', '$micrCodeofBankbranch', '$otherBankerDetails')";

if (!$insertQuery=mysql_query($query)){
  echo $query;
  die(mysql_error());
}

thanks for the replay. i have checked the syntax if find any then please tell me. — sach9949, Apr 22 '14 at 07:07
sorry i am not getting what you are saying. would you please put the code or clarify please thank you — sach9949, Apr 22 '14 at 07:13
so what's the outcome of echo query? can you post it on pastebin? — rubo77, Apr 22 '14 at 07:31

score -3 · Answer 3 · answered Apr 22 '14 at 07:19

-3

I notice this small mistake: You use

`

instead of

'

which is totally wrong.

answered Apr 22 '14 at 07:19

Kamal AL-Hinai

104
15

PHP - Column count doesn't match value count.

3 Answers3