I am using prepared statement to insert data. I am getting the 'Invalid parameter number: number of bound variables does not match number of tokens.' I have tried dumping the prepared statement but I can't spot the mismatch. Is there a way to figure out the mismatch.
$i = 0 ; $j = 0 ; $k = 0 ; $l = 0 ; $str = "" ; $region_str = '' ;
$occasion_str = '' ; $flavor_str = '' ;
foreach($result->WINES->WINE as $w)
{
if($i == 0)
$str = "(:wineid$i,:acctid$i,:brand$i,:varietal$i,:descr$i,:prop$i,:color$i,:case$i,:url$i)" ;
else
$str .= ", (:wineid$i,:acctid$i,:brand$i,:varietal$i,:descr$i,:prop$i,:color$i,:case$i,:url$i)" ;
foreach($w->REGIONS->REGION as $region)
{
if($j == 0)
$region_str = "(:rwineid$i,:region$j)" ;
else
$region_str .= ", (:rwineid$i,:region$j)" ;
$j++ ;
}
foreach($w->OCCASIONS->OCCASION as $o)
{
if($k == 0)
$occasion_str = "(:owineid$i,:occasion$k)" ;
else
$occasion_str .= ", (:owineid$i,:occasion$k)" ;
$k++ ;
}
foreach($w->FLAVORS->FLAVOR as $f)
{
if($l == 0)
$flavor_str = "(:fwineid$l,:flavor$l)" ;
else
$flavor_str .= ", (:fwineid$l,:flavor$l)" ;
$l++ ;
}
$i++ ;
}
$sql = " Delete from wines ;
Insert into wines (wineid,acctid,brandname,varietal,description,propreitaryname,color,caseproduction,url)
values
$str ;
Delete from wine_regions ;
Insert into wine_regions (wineid,region) values $region_str ;
Delete from wine_occasions ;
Insert into wine_occasions (wineid,occasion) values $occasion_str ;
Delete from wine_flavors ;
Insert into wine_flavors (wineid,flavor) values $flavor_str ; " ;
if($str != "")
{
$command = Yii::app()->db->createCommand($sql) ;
// $command_2 = Yii::app()->db->createCommand($sql_2) ;
$i = 0 ; $j = 0 ; $k = 0 ; $l = 0 ;
foreach($result->WINES->WINE as $wine)
{
$command->bindValue(":wineid$i",$wine->WINEID,PDO::PARAM_STR) ;
// $command_2->bindValue(":wineid$i",$i,PDO::PARAM_STR) ;
$command->bindValue(":acctid$i",$wine->ACCTID,PDO::PARAM_STR) ;
$command->bindValue(":brand$i",$wine->BRANDNAME,PDO::PARAM_STR) ;
$command->bindValue(":varietal$i",$wine->VARIETAL,PDO::PARAM_STR) ;
$command->bindValue(":descr$i",$wine->DESCRIPTION,PDO::PARAM_STR) ;
$command->bindValue(":prop$i",$wine->PROPRIETARYNAME,PDO::PARAM_STR) ;
$command->bindValue(":color$i",$wine->COLOR,PDO::PARAM_STR) ;
$command->bindValue(":case$i",$wine->CASEPRODUCTION,PDO::PARAM_STR) ;
$command->bindValue(":url$i",$wine->URL,PDO::PARAM_STR) ;
foreach((array)$wine->REGIONS->REGION as $region)
{
$command->bindValue(":rwineid$j",$wine->WINEID,PDO::PARAM_STR) ;
$command->bindValue(":region$j",$region,PDO::PARAM_STR) ;
$j++ ;
}
foreach((array)$wine->OCCASIONS->OCCASION as $o)
{
$command->bindValue(":owineid$k",$wine->WINEID,PDO::PARAM_STR) ;
$command->bindValue(":occasion$k",$o,PDO::PARAM_STR) ;
$k++ ;
}
foreach((array)$wine->FLAVORS->FLAVOR as $f)
{
$command->bindValue(":fwineid$l",$wine->WINEID,PDO::PARAM_STR) ;
$command->bindValue(":flavor$l",$f,PDO::PARAM_STR) ;
$l++ ;
}
$i++ ;
}
var_dump($command) ;
$command->execute() ;
}
I don't check, if will be error - inform me in the comments. Try my code:
if(!empty($result->WINES->WINE)) { $i = 0 ; $j = 0 ; $k = 0 ; $l = 0 ; $arr = $region_arr = $occasion_arr = $flavor_arr = array(); $str = $region_str = $occasion_str = $flavor_str = array(); foreach($result->WINES->WINE as $w) { $t = array( ":wineid$i" => $w->WINEID, ":acctid$i" => $w->ACCTID, ":brand$i" => $w->BRANDNAME, ":varietal$i" => $w->VARIETAL, ":descr$i" => $w->DESCRIPTION, ":prop$i" => $w->PROPRIETARYNAME, ":color$i" => $w->COLOR, ":case$i" => $w->CASEPRODUCTION, ":url$i" => $w->URL, ); $arr = array_merge($arr, $t); $str[] = implode(',', array_keys($t)); foreach($w->REGIONS->REGION as $region) { $t = array( ":rwineid$j" => $w->WINEID, ":region$j" => $region, ); $region_arr = array_merge($region_arr, $t); $region_str[] = implode(',', array_keys($t)); $j++ ; } foreach($w->OCCASIONS->OCCASION as $o) { $t = array( ":owineid$k" => $w->WINEID, ":occasion$k" => $o, ); $occasion_arr = array_merge($occasion_arr, $t); $occasion_str[] = implode(',', array_keys($t)); $k++ ; } foreach($w->FLAVORS->FLAVOR as $f) { $t = array( ":fwineid$l" => $w->WINEID, ":flavor$l" => $f, ); $flavor_arr = array_merge($flavor_arr, $t); $flavor_str[] = implode(',', array_keys($t)); $l++ ; } $i++ ; } $sql = " Delete from wines ; Insert into wines (wineid,acctid,brandname,varietal,description,propreitaryname,color,caseproduction,url) values (".implode('), (', $str).") ; Delete from wine_regions ; Insert into wine_regions (wineid,region) values (".implode('), (', $region_str).") ; Delete from wine_occasions ; Insert into wine_occasions (wineid,occasion) values (".implode('), (', $occasion_str).") ; Delete from wine_flavors ; Insert into wine_flavors (wineid,flavor) values (".implode('), (', $flavor_str).") ; " ; $command = Yii::app()->db->createCommand($sql) ; $command->bindValues($arr); $command->bindValues($region_arr); $command->bindValues($occasion_arr); $command->bindValues($flavor_arr); $command->execute() ; }