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?
