CODEWITHSUNDEEP
phppdo
RationalRabbit
RationalRabbit

Reputation: 1067

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens COUNT IS CORRECT

I have been puzzling over this and trying to troubleshoot for hours. I have read maybe 30 questions on StackOverflow on this same error. None seem to pertain. Field names and values are pulled from a long array. Previously, this was a MySQL query, that has worked well for the past 5 years, so nothing wrong with that array. As you can see, in troubleshooting, I added the variables $FCount, $VCount, and $PCount; to assure counts were the same, even though that's rather silly, as they are going to count as it goes through the loop, regardless. As a double-check, you can see in my exception code that I added the counts and strings as a second assurance, and counted them several times. I even exempted fields with blank values. I am fairly new to PDO, and have no idea what is wrong here.

<?php
    $DSN = "mysql:host=$HOST;dbname=$DBName;charset=utf8";
    $Options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false,);
    try{$pdo = new PDO($DSN, $USER, $PASSWORD, $Options);}
    catch (PDOException $e)
    {
        $LogData = "\n".date('Y-m-d H:i').' '.$_SESSION[PageName].' '.$e->getMessage().' '.(int)$e->getCode();
        error_log($LogData, 3, "error.log");
        exit('<h2 style="width:90%; border:2px solid #FF0000; padding:15px;">Server Connect Error! [1]</h2>');
    }
    $FCount = 0;
    $VCount = 0;
    $PCount = 0;
    foreach($RateVars as $key => $value)
    {
        if(!empty($value))
        {
            $FieldString .= trim($key) . ','; $FCount++;
            $VarString .= '"' . addslashes(trim($value)) . '",'; $VCount++;
            $PrepString .= '?, '; $PCount++;
        }
    }
    $FieldString    .= 'Server';
    $VarString      .= '"'.$Node.'"';
    $PrepString     .= '?';
    $sql = 'INSERT INTO Archive ('.$FieldString.') VALUES ('.$PrepString.')';
    $stmt = $pdo->prepare($sql);
    try {$stmt->execute(array($VarString));}
    catch (PDOException $e)
    {
        $ErrorMsg = "DataBase Error in Save to Archive"; $Status=1;
        $LogData = "\n".date('Y-m-d H:i').' '.$_SESSION[PageName].' '.$ErrorMsg.' '.$e->getMessage().' '.(int)$e->getCode();
        $LogData .= "\n".'F: '.$FCount.' V: '.$VCount.' P: '.$PCount;
        $LogData .= "\n".'F: '.$FieldString."\n".' V: '.$VarString."\n".' P: '.$PrepString."\n".$sql;
        error_log($LogData, 3, "error.log");
    }

?>

Added note Short example (there are 72 variables here) from my error log where I print out $sql: INSERT INTO RateArchive (EstType,hszip,hczip,wswxds,etc...) VALUES ("W","58102","58652","000050000000000000","0500000000000000",etc.) In answer to a comment below, the value of $Node is "DEV" and that is exactly how it comes out at the end of the value string.

Upvotes: 0

Views: 4199

Answers (1)

Barmar
Barmar

Reputation: 780724

Each parameter needs to be a separate element in the array passed to $stmt->execute(), it shouldn't be a single comma-separated string.

<?php
$DSN = "mysql:host=$HOST;dbname=$DBName;charset=utf8";
$Options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false,);
try{$pdo = new PDO($DSN, $USER, $PASSWORD, $Options);}
catch (PDOException $e)
{
    $LogData = "\n".date('Y-m-d H:i').' '.$_SESSION[PageName].' '.$e->getMessage().' '.(int)$e->getCode();
    error_log($LogData, 3, "error.log");
    exit('<h2 style="width:90%; border:2px solid #FF0000; padding:15px;">Server Connect Error! [1]</h2>');
}
$FCount = 0;
$VCount = 0;
$PCount = 0;

$PrepString = str_repeat("?, ", count($RateVars));
$FieldString = implode(',', array_keys($RateVars));
$PrepString .= '?';
$FieldString .= ', Server';
$ValArray = array_values($RateVars);
$ValArray[] = $Node;
$sql = 'INSERT INTO Archive ('.$FieldString.') VALUES ('.$PrepString.')';
$stmt = $pdo->prepare($sql);
try {
    $stmt->execute($ValArray);
} catch (PDOException $e)
{
    $ErrorMsg = "DataBase Error in Save to Archive"; $Status=1;
    $LogData = "\n".date('Y-m-d H:i').' '.$_SESSION[PageName].' '.$ErrorMsg.' '.$e->getMessage().' '.(int)$e->getCode();
    $LogData .= "\n".'F: '.$FCount.' V: '.$VCount.' P: '.$PCount;
    $LogData .= "\n".'F: '.$FieldString."\n".' V: '.implode(',', $ValArray)."\n".' P: '.$PrepString."\n".$sql;
    error_log($LogData, 3, "error.log");
}

?>

Upvotes: 2

Related Questions