PDO most aggravating error with variables not matching tokens

Question

I have been searching the web for possible solutions to resolve this code issue and had no luck.

I am getting this error from the code:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens.

See code below:

<?php
        // vars
        $categories = array('0'=>12,'1'=>15,'2'=>17);
        $values = ""; $in = "";

        // re-index
        if (is_array($categories)) 
        {
            $i=0;
            foreach ($categories as $k => $item)
            {
                $i++;

                $key = ':catId'.$i;
                $in .= "$key,";

                // collecting values into key-value array
                $in_params[$key] = $item; 
            }

            // create the IN ( :catId0,:catId1,:catId2 ) param.
            // remove the last comma from the list
            $in = rtrim($in,",");
            $values .= " IN (" . $in . ")";
        }


        // Results from the above code:
        // $values = IN (:catId1,:catId2,:catId3)
        // $in_params = array ( [:catId1] => 12 [:catId2] => 15 [:catId3] => 17 ) 


        $sql = "SELECT * FROM products
                WHERE cat_id {$values}"; 

        try
        {
            $_stmt = $this->_dbConn->prepare($sql);

            foreach($in_params as $k => $val) 
            {
                if(is_int($val)) {
                    $_stmt->bindValue($k,$val, PDO::PARAM_INT);
                }else{
                    $_stmt->bindValue($k,$val);
                }
            }

            $_stmt->execute();

            foreach ($_stmt as $row)  
            {
                // do something
            }
        }
        catch(PDOException $e)
        {
            return $e->getMessage();
        }

?>

I need a second pair of eyes to review code. Most of the solutions provided online are using the `$stmt->execute()` to process the array and are not using the PDO `BindValue`. — Carl Barrett, Dec 13 '17 at 11:24
Come on, are you sure you need bindValue here? Note that question asking a second pair of eyes are off topic. — Your Common Sense, Dec 13 '17 at 11:38
I understand the using bindValue or bindParam is much safer than pushing the values through the `execute()`. Is that true? — Carl Barrett, Dec 13 '17 at 11:47

PDO most aggravating error with variables not matching tokens

0 Answers0