"SQLSTATE [HY093]: Invalid parameter number: no parameters were bound"

Question

"SQLSTATE [HY093]: Invalid parameter number: no parameters were bound"

Navigation

#1 by (1 votes)

0

I have a simple class that runs an insert in a MySql database as follows:

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

In class db 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 using it that way does not 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 $a , $b , $c values and are exactly the same as those used in the part that works (in case I enter the values directly)

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

Does anyone know how to tell why it does not work using foreach?

php mysql pdo

asked by anonymous 23.09.2017 / 16:24

1 answer

Code with indeterminate entries showing the pairs in ascending order and odd in descending order Identifying the most efficient method of identifying the central element of an array

score 1 · Accepted Answer

Probably the problem is in the following line:

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

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

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

Inside the foreach try something like:

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

PHP allows you to invoke methods or static and constant properties using variables, in this way (without quotes and without concatenation):

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