Home / Questions / 422264
Accepted
Gabo
Asked: 2022-01-21 15:07:50 +0800 CST 2022-01-21 15:07:50 +0800 CST

Problems with bindParam() PHP | Dynamic MySQL Query

  • 772

I want to ask for your support to solve a problem with a dynamic query to count the records that meet one or more conditions.

This query is executed without any problem, but it does not give me the correct result, the WHERE conditions can vary from type 'NOT IN' or 'IN', example WHERE color NOT IN (:color0, :color1, :color2), I hope to explain myself ok, my code is as follows:

$classGeneral = new generalQuery(DATABASE_SERVER.ERP_BASE);
/* Contamos cuantos registros existen en total */
$whereCount = array(
              'color' => array('rojo, verde', 'NOT IN', 'string'),
              );
$totalRecords = $classGeneral->countRecords('tabla_Productos', $innerJoin, $whereCount);

// El método de mi clase
public function countRecords($table_Select, $innerJoin_Select = array(), $where_Select = array()){
    $this->tableSql = $table_Select;
    $this->whereSql = $where_Select;
    $this->valueSql;

    try {
        $this->statmentSql = 'SELECT count(*) as numRecords FROM '.$this->tableSql;

        #### Este es el código parcial de mi clase, exactamente donde tengo el problema, antes de esto solo construye la consulta, lo cual lo hace muy bien.

        $this->cnxServer->beginTransaction();
        $countSql = $this->cnxServer->prepare($this->statmentSql);


       /* Si pruebo creando estáticos los bindParam, así, me funciona muy bien, cuenta los registros con sus condiciones de forma correcta
       $a1 = 'rojo';
       $a2 = 'verde';
       $countSql->bindParam(':color0', $a1, PDO::PARAM_STR);
       $countSql->bindParam(':color1', $a2, PDO::PARAM_STR);*/


      // Creamos los bindParam dinámicos a través del array, que es aqui donde me interesa funcione
      foreach($this->whereSql as $key => $value) {
          if(strcmp(array_values($value)[1], 'IN') === 0 OR strcmp(array_values($value)[1], 'NOT IN') === 0){
               $valueIn = explode(',', array_values($value)[0]);
               $longitud = count($valueIn);
               $valBind = '';

               //AQUI ES DONDE HE DETECTADO EL PROBLEMA
               for($a = 0; $a < $longitud; $a++) {
                    $valBind .= trim($valueIn[$a]);
                    $countSql->bindParam(':'.$key.$a, $valBind, (strcmp(array_values($value)[2], 'string') === 0) ? PDO::PARAM_STR : PDO::PARAM_INT);
               }
          } elseif(strcmp(array_values($value)[1], 'WHERE') === 0 || strcmp(array_values($value)[1], 'NOT') === 0){
               $valBind = trim(array_values($value)[0]);
               $countSql->bindParam(':'.$key, $valBind, (strcmp(array_values($value)[2], 'string') === 0) ? PDO::PARAM_STR : PDO::PARAM_INT);
          }
     }       
        
     if(!$countSql->execute()){
          print_r($countSql->errorInfo());
     } else {
          $this->cnxServer->commit();
          $numCountItems = $countSql->fetch();
          $this->valueSql = $numCountItems['numRecords'];
     }

     return $this->valueSql;
      
     } catch (PDOException $e) {
         $this->cnxServer->rollBack();
         echo "Failed count records: " . $e->getMessage()."<br />";
     }
}

Any suggestion or correction?

php

1 Answers

  1. Best Answer

    I leave you the solution, I only had to change the bindParam for a bindValue in the for cycle, with this it binds the value that I insert without problem and gives me the correct result of my query

    for($a = 0; $a < $longitud; $a++) {
      $valBind .= trim($valueIn[$a]);
      $countSql->bindValue(':'.$key.$a, $valBind, (strcmp(array_values($value)[2], 'string') === 0) ? PDO::PARAM_STR : PDO::PARAM_INT);
           }
    • 0