"SQLSTATE[HY093]: Invalid Parameter number: no Parameters Were bound"

Question

I have a simple class that runs an Insert on a Mysql database as follows:

$db = new db();
$db->exec2([0=>["Brasil","BRA","BR"]]);

In the db class I have the following list of queries :

$arrInitDefault["queries3"] = [
              0=>["INSERT INTO countries (name, iso3_code, iso2_code, created_at)","VALUES (?,?,?,NOW())"],
              1=>["INSERT INTO provinces (fk_country, name, uf, created_at)","VALUES (?,?,?,NOW())"],
              2=>["INSERT INTO cities (fk_province, name, geocodeBr,created_at)","VALUES (?,?,?,NOW())"],
        ];

This is the function that inserts the values:

public function exec2(array  $param = NULL) {

        try {            
                $k= key($param);
                $arrInsertValues = $param[$k];
                $arrSql = self::$queries3[$k];
                self::$sql = $arrSql[0];
                $arrPDO = $arrSql[1];
                xdebug_break();

                $stmt = self::$conn->prepare(self::$sql);

                //Funciona
                $stmt->bindValue(1,"Brasil",PDO::PARAM_STR);
                $stmt->bindValue(2,"BRA",PDO::PARAM_STR);
                $stmt->bindValue(3,"BR",PDO::PARAM_STR);

                //Nao Funciona
                foreach ($arrInsertValues as $k=>$v) {
                    $pdov = "PDO::".$arrPDO[$k+1];
                    $val=$arrInsertValues[$k];
                    $a=$k+1;
                    $b=$val;
                    $c=$pdov;
                    #$stmt->bindValue($k+1,$val,$pdov);
                    $stmt->bindValue($a,"$b",$c);
                }

                self::$conn->beginTransaction(); 

                if($stmt->execute()){                
                    $id= self::$conn->lastInsertId(); 
                    self::$conn->commit(); 
                    return $id;
                }
                else{
                    return FALSE;
                }
        }// End of try
        catch (PDOException $e) {//If had some error. The PDO object ($this->conn) could not be created. Throw an error. 
                self::$exceptionObjc = $e;  
                //self::saveLogMsgInDb(["exceptionObjc"=>self::$exceptionObjc,"sql"=>self::$sql]);
                self::$arrCatchConnResult = self::saveLogMsg(["exceptionObjc"=>self::$exceptionObjc,"sql"=>self::$sql]);
                self::$conn = null;
                if (self::$die) {
                    $msg = self::$arrCatchConnResult["displayMsgHTML"];
                    die($msg);
                }
        }


    }

The fact is that if I use it this way it will work:

//Funciona
$stmt->bindValue(1,"Brasil",PDO::PARAM_STR);
$stmt->bindValue(2,"BRA",PDO::PARAM_STR);
$stmt->bindValue(3,"BR",PDO::PARAM_STR);

But your use that way doesn’t work

//Nao Funciona
foreach ($arrInsertValues as $k=>$v) {
    $pdov = "PDO::".$arrPDO[$k+1];
    $val=$arrInsertValues[$k];
    $a=$k+1;
    $b=$val;
    $c=$pdov;
    #$stmt->bindValue($k+1,$val,$pdov);
    $stmt->bindValue($a,"$b",$c);
}

I check the values $a,$b,$c and are exactly the same as those used in the part that works (in case I enter the values directly)

The mistake I get is :"SQLSTATE[HY093]: Invalid parameter number: no parameters were bound".

Someone has to say because it doesn’t work using foreach?

Answer 1

Probably the problem is on the following line:

$pdov = "PDO::".$arrPDO[$k+1];

This is a string and method bindValue needs an integer as the 3rd parameter. The value can be obtained through a counting of PDO.

Your variable $pdov the constant of the PDO, instead you are just creating a string whose result will be the concatenation of the string PDO:: with the result of the operation $arrPDO[$k+1].

Inside the foreach try something like:

$stmt->bindValue($a,"$b",PDO::$arrPDO[$k+1]);

PHP allows invoking static and constant methods or properties using variables in this way (without quotation marks and without concatenation):

$a = "PARAM_INT";
$b = PDO::$a;