Create PDO query with the values of an array

Question

Create PDO query with the values of an array

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I am trying to generate a MySQL query with the data I have captured in a array .

array(2) {
    ["area1"] => array(3) { 
        [0] => string(5) "item1"
        [1] => string(5) "item2" 
        [2] => string(5) "item3"
    }
    ["area2"] => array(2) { 
        [0] => string(5) "item1"
        [1]=> string(5) "item2"} 
    }
}

What is the process to create a query that is a type?:

"select nombre, tipo from tabla1 where area1 in(item1, item2, item3) and area2 in (item1, item2)"

What would be the handling of bind for these possible values?

Note: The number of areas and items can be n , of course, all this with the PDO format.

mysql php pdo mariadb

asked by DIANGA 04.04.2017 в 18:27

source

3 answers

how to select data between a range Problems in detecting object collisions

score 0 · Answer 1

The clause IN accepts a collection, so you could send it directly the values of Array separated by commas as a String , this is achieved by using the function implode (separator, array) , also enclosing all this in single quotes (concatenated)

$array = array("area1"=> array("item1","item2","item1") , "area2"=> array("item1","item2"));
$area1 = "'".implode("','", $array['area1'])."'";
/* Convertimos a String ,separado por comas*/
$area2 =  "'".implode("','", $array['area2'])."'";
$sentencia = $mbd->prepare("SELECT nombre,tipo from tabla1 where area1 in  ( $area1 ) 
                            and area2 in ($area2)"); /* Le pasamos la colección*/
$sentencia->execute(); /* Ejecutamos la Sentencia */
$fila = $sentencia->fetchAll(); 
print_r($fila);

score 0 · Answer 2

A prepared PDO query can be used, as in the following link

link

$paramsIn1 = [10, 203, 4, 6];
$paramsIn2 = [10, 203];

$strParams1 = implode(',', array_fill(0, count($paramsIn1), '?'));
$strParams2 = implode(',', array_fill(0, count($paramsIn2), '?'));

$sqlQuery = "select nombre,tipo from tabla1 where area1 in($strParams1) and area2 in ($strParams2)";

$sth      = $dbh->prepare($sqlQuery);
$sth->execute(
    array_merge(
        $paramsIn1, $paramsIn2
    )
);

To do this, first you have to prepare the string and then pass the arguments to the "execute" method.

The chain has to be prepared because depending on the number of parameters in the WHERE IN they will be the amount of "?" in the chain.

score 0 · Answer 3

Good afternoon, finish doing this:

public function procesarDestinatarios($array){
    $consulta = "select nombre,tipo from datosusuarios where ";        


    $aux1 = 1;
    $aux2 = 1;

    $count1 = count($array);
    $count2 = 0;

    foreach ($array as $key => $value) {
        $count2 = count($array[$key]);
        $consulta .= $key ." in ("; 

        foreach ($value as $valor) {
            if($aux2 < $count2){
                $consulta .= "'".$valor ."',";
                $aux2++;
            }else{
                $consulta .= "'".$valor."'";
                $aux2 = 1;
            }

        }

        if($aux1 < $count1){
            $consulta .= ") and ";
        }else{
            $consulta .= ")";
        }

        $aux1++;
    }

    return $consulta;

}

what I find in this:

 select nombre,tipo from datosusuarios where area1 in ('zona2','zona3') and grupo in ('a','c','d','h')

First, that was the query that I wanted to generate from the array, now I'm working with PDO recently and the bind is used for the data type, how would I handle this here, if I am generating that query dynamically by say it like that.