php - query - ¿Puedo unir una matriz a una condición IN()?

php prepare update

Question

Tengo curiosidad por saber si es posible vincular una matriz de valores a un marcador de posición utilizando PDO. El caso de uso aquí es intentar pasar una matriz de valores para usar con una condición IN() .

Me gustaría poder hacer algo como esto:

<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    ''SELECT *
     FROM table
     WHERE id IN(:an_array)''
);
$stmt->bindParam(''an_array'',$ids);
$stmt->execute();
?>

Y tener PDO enlazar y citar todos los valores de la matriz.

En el momento que estoy haciendo:

<?php
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
foreach($ids as &$val)
    $val=$db->quote($val); //iterate through array and quote
$in = implode('','',$ids); //create comma separated list
$stmt = $db->prepare(
    ''SELECT *
     FROM table
     WHERE id IN(''.$in.'')''
);
$stmt->execute();
?>

Lo que ciertamente hace el trabajo, pero me pregunto si hay una solución integrada que estoy perdiendo.

Answer 1

¿Es tan importante utilizar la instrucción IN ? Intenta usar FIND_IN_SET op.

Por ejemplo, hay una consulta en PDO como esa

SELECT * FROM table WHERE FIND_IN_SET(id, :array)

Entonces solo necesitas unir una matriz de valores, implosionados con comas, como este

$ids_string = implode('','', $array_of_smth); // WITHOUT WHITESPACES BEFORE AND AFTER THE COMMA
$stmt->bindParam(''array'', $ids_string);

y esta hecho

UPD: Como algunas personas señalaron en los comentarios a esta respuesta, hay algunas cuestiones que deben plantearse explícitamente.

1. FIND_IN_SET no usa el índice en una tabla, y todavía no está implementado, vea este registro en el rastreador de errores MYSQL . Gracias a @BillKarwin por el aviso.
2. No puede utilizar una cadena con una coma dentro como valor de la matriz para la búsqueda. Es imposible analizar dicha cadena de la manera correcta después de implode ya que utiliza el símbolo de coma como separador. Gracias a @VaL por la nota.

En resumen, si no depende en gran medida de los índices y no utiliza cadenas con comas para la búsqueda, mi solución será mucho más sencilla, sencilla y rápida que las soluciones enumeradas anteriormente.

Answer 2

¿Qué base de datos estás utilizando? En PostgreSQL me gusta usar ANY (array). Así que para reutilizar tu ejemplo:

<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    ''SELECT *
     FROM table
     WHERE id = ANY (:an_array)''
);
$stmt->bindParam(''an_array'',$ids);
$stmt->execute();
?>

Desafortunadamente esto es bastante no portátil.

En otras bases de datos, deberá crear su propia magia, como han mencionado otros. Querrá poner esa lógica en una clase / función para hacerla reutilizable en todo su programa, por supuesto. Eche un vistazo a los comentarios en la página mysql_query en PHP.NET para obtener más información sobre el tema y ejemplos de este escenario.

Answer 3

Aquí está mi solución. También he extendido la clase DOP:

class Db extends PDO
{

    /**
     * SELECT ... WHERE fieldName IN (:paramName) workaround
     *
     * @param array  $array
     * @param string $prefix
     *
     * @return string
     */
    public function CreateArrayBindParamNames(array $array, $prefix = ''id_'')
    {
        $newparams = [];
        foreach ($array as $n => $val)
        {
            $newparams[] = ":".$prefix.$n;
        }
        return implode(", ", $newparams);
    }

    /**
     * Bind every array element to the proper named parameter
     *
     * @param PDOStatement $stmt
     * @param array        $array
     * @param string       $prefix
     */
    public function BindArrayParam(PDOStatement &$stmt, array $array, $prefix = ''id_'')
    {
        foreach($array as $n => $val)
        {
            $val = intval($val);
            $stmt -> bindParam(":".$prefix.$n, $val, PDO::PARAM_INT);
        }
    }
}

Aquí hay una muestra de uso para el código anterior:

$idList = [1, 2, 3, 4];
$stmt = $this -> db -> prepare("
  SELECT
    `Name`
  FROM
    `User`
  WHERE
    (`ID` IN (".$this -> db -> CreateArrayBindParamNames($idList)."))");
$this -> db -> BindArrayParam($stmt, $idList);
$stmt -> execute();
foreach($stmt as $row)
{
    echo $row[''Name''];
}

Déjame saber lo que piensas

Answer 4

Aquí está mi solución:

$total_items = count($array_of_items);
$question_marks = array_fill(0, $total_items, ''?'');
$sql = ''SELECT * FROM foo WHERE bar IN ('' . implode('','', $question_marks ). '')'';

$stmt = $dbh->prepare($sql);
$stmt->execute(array_values($array_of_items));

Tenga en cuenta el uso de valores de matriz. Esto puede solucionar problemas de ordenamiento clave.

Estaba fusionando matrices de identificadores y luego eliminando elementos duplicados. Tuve algo como:

$ids = array(0 => 23, 1 => 47, 3 => 17);

Y eso estaba fallando.

Answer 5

Como hago muchas consultas dinámicas, esta es una función de ayuda súper simple que realicé.

public static function bindParamArray($prefix, $values, &$bindArray)
{
    $str = "";
    foreach($values as $index => $value){
        $str .= ":".$prefix.$index.",";
        $bindArray[$prefix.$index] = $value;
    }
    return rtrim($str,",");     
}

Úsalo así:

$bindString = helper::bindParamArray("id", $_GET[''ids''], $bindArray);
$userConditions .= " AND users.id IN($bindString)";

Devuelve una cadena :id1,:id2,:id3 y también actualiza su $bindArray de enlaces que necesitará cuando sea el momento de ejecutar su consulta. ¡Fácil!

Answer 6

Como sé, no hay ninguna posibilidad de vincular una matriz a una declaración PDO.

Pero existen 2 soluciones comunes:

1. Use marcadores de posición posicionales (?,?,?,?) O marcadores de posición con nombre (: id1,: id2,: id3)

   $ whereIn = implode ('','', array_fill (0, count ($ ids), ''?''));

2. Citar la matriz anterior

   $ whereIn = array_map (array ($ db, ''quote''), $ ids);

Ambas opciones son buenas y seguras. Prefiero el segundo porque es más corto y puedo var_dump parámetros si lo necesito. Usando marcadores de posición debe vincular valores y al final su código SQL será el mismo.

$sql = "SELECT * FROM table WHERE id IN ($whereIn)";

Y lo último e importante para mí es evitar el error "el número de variables enlazadas no coincide con el número de tokens"

Doctrine es un gran ejemplo del uso de marcadores de posición posicionales, solo porque tiene control interno sobre los parámetros entrantes.

Answer 7

Creo que el alma está en lo cierto. Tendrás que construir la cadena de consulta.

<?php
$ids     = array(1, 2, 3, 7, 8, 9);
$inQuery = implode('','', array_fill(0, count($ids), ''?''));

$db = new PDO(...);
$stmt = $db->prepare(
    ''SELECT *
     FROM table
     WHERE id IN('' . $inQuery . '')''
);

// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();
?>

arreglo: dan, tenias razon arreglado el código (aunque no lo probé)

edit: tanto chris (comentarios) como alguien son increibles sugieren que el bucle foreach ...

(...)
// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();

... podría ser redundante, por lo que el bucle foreach y $stmt->execute podrían reemplazarse por solo ...

<?php 
  (...)
  $stmt->execute($ids);
?>

(de nuevo, no lo probé)

Answer 8

Cuando tengas otro parámetro, puedes hacer esto:

$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
$query = ''SELECT *
            FROM table
           WHERE X = :x
             AND id IN('';
$comma = '''';
for($i=0; $i<count($ids); $i++){
  $query .= $comma.'':p''.$i;       // :p0, :p1, ...
  $comma = '','';
}
$query .= '')'';

$stmt = $db->prepare($query);
$stmt->bindValue('':x'', 123);  // some value
for($i=0; $i<count($ids); $i++){
  $stmt->bindValue('':p''.$i, $ids[$i]);
}
$stmt->execute();

Answer 9

Después de pasar por el mismo problema, PDO::PARAM_ARRAY una solución más simple (aunque todavía no es tan elegante como PDO::PARAM_ARRAY sería una PDO::PARAM_ARRAY ):

dada la matriz $ids = array(2, 4, 32) :

$newparams = array();
foreach ($ids as $n => $val){ $newparams[] = ":id_$n"; }

try {
    $stmt = $conn->prepare("DELETE FROM $table WHERE ($table.id IN (" . implode(", ",$newparams). "))");
    foreach ($ids as $n => $val){
        $stmt->bindParam(":id_$n", intval($val), PDO::PARAM_INT);
    }
    $stmt->execute();

... y así

Entonces, si está utilizando una matriz de valores mixtos, necesitará más código para probar sus valores antes de asignar el tipo param:

// inside second foreach..

$valuevar = (is_float($val) ? floatval($val) : is_int($val) ? intval($val) :  is_string($val) ? strval($val) : $val );
$stmt->bindParam(":id_$n", $valuevar, (is_int($val) ? PDO::PARAM_INT :  is_string($val) ? PDO::PARAM_STR : NULL ));

Pero no he probado este.

Answer 10

En cuanto a la DOP: Constantes predefinidas, no hay DOP :: PARAM_ARRAY que necesitaría, tal como aparece en la lista de PDOStatement->bindParam

bool PDOStatement :: bindParam (parámetro mixto $, mixed & $ variable [, int $ data_type [, int $ length [, mixed $ driver_options]])

Así que no creo que sea alcanzable.

Answer 11

Extendí la DOP para hacer algo similar a lo que sugieren los fondos, y fue más fácil para mí a largo plazo:

class Array_Capable_PDO extends PDO {
    /**
     * Both prepare a statement and bind array values to it
     * @param string $statement mysql query with colon-prefixed tokens
     * @param array $arrays associatve array with string tokens as keys and integer-indexed data arrays as values 
     * @param array $driver_options see php documention
     * @return PDOStatement with given array values already bound 
     */
    public function prepare_with_arrays($statement, array $arrays, $driver_options = array()) {

        $replace_strings = array();
        $x = 0;
        foreach($arrays as $token => $data) {
            // just for testing...
            //// tokens should be legit
            //assert(''is_string($token)'');
            //assert(''$token !== ""'');
            //// a given token shouldn''t appear more than once in the query
            //assert(''substr_count($statement, $token) === 1'');
            //// there should be an array of values for each token
            //assert(''is_array($data)'');
            //// empty data arrays aren''t okay, they''re a SQL syntax error
            //assert(''count($data) > 0'');

            // replace array tokens with a list of value tokens
            $replace_string_pieces = array();
            foreach($data as $y => $value) {
                //// the data arrays have to be integer-indexed
                //assert(''is_int($y)'');
                $replace_string_pieces[] = ":{$x}_{$y}";
            }
            $replace_strings[] = ''(''.implode('', '', $replace_string_pieces).'')'';
            $x++;
        }
        $statement = str_replace(array_keys($arrays), $replace_strings, $statement);
        $prepared_statement = $this->prepare($statement, $driver_options);

        // bind values to the value tokens
        $x = 0;
        foreach($arrays as $token => $data) {
            foreach($data as $y => $value) {
                $prepared_statement->bindValue(":{$x}_{$y}", $value);
            }
            $x++;
        }

        return $prepared_statement;
    }
}

Puedes usarlo así:

$db_link = new Array_Capable_PDO($dsn, $username, $password);

$query = ''
    SELECT     *
    FROM       test
    WHERE      field1 IN :array1
     OR        field2 IN :array2
     OR        field3 = :value
'';

$pdo_query = $db_link->prepare_with_arrays(
    $query,
    array(
        '':array1'' => array(1,2,3),
        '':array2'' => array(7,8,9)
    )
);

$pdo_query->bindValue('':value'', ''10'');

$pdo_query->execute();

Answer 12

La solución de EvilRygy no funcionó para mí. En Postgres puedes hacer otra solución:

$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    ''SELECT *
     FROM table
     WHERE id = ANY (string_to_array(:an_array, '',''))''
);
$stmt->bindParam('':an_array'', implode('','', $ids));
$stmt->execute();

Answer 13

Me llevé un poco más lejos para acercarme más a la pregunta original de usar marcadores de posición para enlazar los parámetros.

Esta respuesta deberá realizar dos bucles a través de la matriz que se utilizará en la consulta. Pero resuelve el problema de tener otros marcadores de posición de columna para consultas más selectivas.

//builds placeholders to insert in IN()
foreach($array as $key=>$value) {
    $in_query = $in_query . '' :val_'' . $key . '', '';
}

//gets rid of trailing comma and space
$in_query = substr($in_query, 0, -2);

$stmt = $db->prepare(
    "SELECT *
     WHERE id IN($in_query)";

//pind params for your placeholders.
foreach ($array as $key=>$value) {
    $stmt->bindParam(":val_" . $key, $array[$key])
}

$stmt->execute();

Answer 14

No es posible usar una matriz como esa en PDO.

Necesita construir una cadena con un parámetro (o usar?) Para cada valor, por ejemplo:

:an_array_0, :an_array_1, :an_array_2, :an_array_3, :an_array_4, :an_array_5

Aquí hay un ejemplo:

<?php
$ids = array(1,2,3,7,8,9);
$sqlAnArray = join(
    '', '',
    array_map(
        function($index) {
            return ":an_array_$index";
        },
        array_keys($ids)
    )
);
$db = new PDO(
    ''mysql:dbname=mydb;host=localhost'',
    ''user'',
    ''passwd''
);
$stmt = $db->prepare(
    ''SELECT *
     FROM table
     WHERE id IN(''.$sqlAnArray.'')''
);
foreach ($ids as $index => $id) {
    $stmt->bindValue("an_array_$index", $id);
}

Si desea seguir utilizando bindParam , puede hacer esto en su lugar:

foreach ($ids as $index => $id) {
    $stmt->bindParam("an_array_$index", $ids[$id]);
}

Si quieres usar ? marcadores de posición, puede hacerlo así:

<?php
$ids = array(1,2,3,7,8,9);
$sqlAnArray = ''?'' . str_repeat('', ?'', count($ids)-1);
$db = new PDO(
    ''mysql:dbname=dbname;host=localhost'',
    ''user'',
    ''passwd''
);
$stmt = $db->prepare(
    ''SELECT *
     FROM phone_number_lookup
     WHERE country_code IN(''.$sqlAnArray.'')''
);
$stmt->execute($ids);

Si no sabe si $ids está vacío, debe probarlo y manejar ese caso en consecuencia (devolver una matriz vacía, devolver un objeto nulo o lanzar una excepción, ...).

Answer 15

Para algo rápido:

//$db = new PDO(...);
//$ids = array(...);

$qMarks = str_repeat(''?,'', count($ids) - 1) . ''?'';
$sth = $db->prepare("SELECT * FROM myTable WHERE id IN ($qMarks)");
$sth->execute($ids);

Answer 16

Para mí, la solución más atractiva es construir una matriz asociativa dinámica y usarla

// a dirty array sent by user
$dirtyArray = [''Cecile'', ''Gilles'', ''Andre'', ''Claude''];

// we construct an associative array like this
// [ '':name_0'' => ''Cecile'', ... , '':name_3'' => ''Claude'' ]
$params = array_combine(
    array_map(
        // construct param name according to array index
        function ($v) {return ":name_{$v}";},
        // get values of users
        array_keys($dirtyArray)
    ),
    $dirtyArray
);

// construct the query like `.. WHERE name IN ( :name_1, .. , :name_3 )`
$query = "SELECT * FROM user WHERE name IN( " . implode(",", array_keys($params)) . " )";
// here we go
$stmt  = $db->prepare($query);
$stmt->execute($params);

Answer 17

Si la columna solo puede contener enteros, probablemente podría hacer esto sin marcadores de posición y simplemente colocar los identificadores en la consulta directamente. Solo tienes que convertir todos los valores de la matriz en enteros. Me gusta esto:

$listOfIds = implode('','',array_map(''intval'', $ids));
$stmt = $db->prepare(
    "SELECT *
     FROM table
     WHERE id IN($listOfIds)"
);
$stmt->execute();

Esto no debería ser vulnerable a cualquier inyección de SQL.

Answer 18

También me doy cuenta de que este hilo es antiguo, pero tuve un problema único en el que, mientras convertía el controlador mysql obsoleto en un controlador PDO, tuve que hacer una función que pudiera construir, dinámicamente, tanto los parámetros normales como los IN del mismo matriz de parámetros Así que rápidamente construí esto:

/**
 * mysql::pdo_query(''SELECT * FROM TBL_WHOOP WHERE type_of_whoop IN :param AND siz_of_whoop = :size'', array('':param'' => array(1,2,3), '':size'' => 3))
 *
 * @param $query
 * @param $params
 */
function pdo_query($query, $params = array()){

    if(!$query)
        trigger_error(''Could not query nothing'');

    // Lets get our IN fields first
    $in_fields = array();
    foreach($params as $field => $value){
        if(is_array($value)){
            for($i=0,$size=sizeof($value);$i<$size;$i++)
                $in_array[] = $field.$i;

            $query = str_replace($field, "(".implode('','', $in_array).")", $query); // Lets replace the position in the query string with the full version
            $in_fields[$field] = $value; // Lets add this field to an array for use later
            unset($params[$field]); // Lets unset so we don''t bind the param later down the line
        }
    }

    $query_obj = $this->pdo_link->prepare($query);
    $query_obj->setFetchMode(PDO::FETCH_ASSOC);

    // Now lets bind normal params.
    foreach($params as $field => $value) $query_obj->bindValue($field, $value);

    // Now lets bind the IN params
    foreach($in_fields as $field => $value){
        for($i=0,$size=sizeof($value);$i<$size;$i++)
            $query_obj->bindValue($field.$i, $value[$i]); // Both the named param index and this index are based off the array index which has not changed...hopefully
    }

    $query_obj->execute();

    if($query_obj->rowCount() <= 0)
        return null;

    return $query_obj;
}

Todavía no está probado, sin embargo, la lógica parece estar allí.

Espero que ayude a alguien en la misma posición,

Edit: Después de algunas pruebas me enteré:

• DOP no le gusta ''.'' en sus nombres (lo cual es un poco estúpido si me preguntas)
• bindParam es la función incorrecta, bindValue es la función correcta.

Código editado a versión de trabajo.

Answer 19

Una forma muy limpia para postgres es usar la matriz postgres ("{}"):

$ids = array(1,4,7,9,45);
$param = "{".implode('', '',$ids)."}";
$cmd = $db->prepare("SELECT * FROM table WHERE id = ANY (?)");
$result = $cmd->execute(array($param));

Answer 20

Una pequeña edición sobre el código de Schnalle.

<?php
$ids     = array(1, 2, 3, 7, 8, 9);
$inQuery = implode('','', array_fill(0, count($ids)-1, ''?''));

$db   = new PDO(...);
$stmt = $db->prepare(
    ''SELECT *
     FROM table
     WHERE id IN('' . $inQuery . '')''
);

foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();
?>

//implode('','', array_fill(0, count($ids)-1), ''?'')); 
//''?'' this should be inside the array_fill
//$stmt->bindValue(($k+1), $in); 
// instead of $in, it should be $id

Answer 21

Usted primero establece el número de "?" en la consulta y luego por un "para" enviar parámetros como este:

require ''dbConnect.php'';
$db=new dbConnect();
$array=[];
array_push($array,''value1'');
array_push($array,''value2'');
$query="SELECT * FROM sites WHERE kind IN (";

foreach ($array as $field){
    $query.="?,";
}
$query=substr($query,0,strlen($query)-1);
$query.=")";
$tbl=$db->connection->prepare($query);
for($i=1;$i<=count($array);$i++)
    $tbl->bindParam($i,$array[$i-1],PDO::PARAM_STR);
$tbl->execute();
$row=$tbl->fetchAll(PDO::FETCH_OBJ);
var_dump($row);