Kann ich ein Array an eine IN()-Bedingung in einer PDO-Abfrage binden?

Question

Ich bin neugierig zu wissen, ob es möglich ist, ein Array von Werten an einen Platzhalter mit PDO zu binden. Der Anwendungsfall hier ist der Versuch, ein Array von Werten für die Verwendung mit einer IN() Zustand.

Ich würde gerne so etwas machen können:

<?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();
?>

Und lassen Sie PDO alle Werte im Array binden und zitieren.

Im Moment mache ich das:

<?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();
?>

Das erfüllt sicherlich seinen Zweck, aber ich frage mich, ob es eine integrierte Lösung gibt, die ich übersehe?

Answer 1

Wie ich weiß, gibt es keine Möglichkeit, ein Array in PDO-Anweisung zu binden.

Es gibt jedoch 2 gemeinsame Lösungen:

1. Positionale Platzhalter (?,?,?,?) oder benannte Platzhalter (:id1, :id2, :id3) verwenden

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

2. Zitatfeld früher

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

Beide Optionen sind gut und sicher. Ich bevorzuge die zweite, weil sie kürzer ist und ich var_dump-Parameter angeben kann, wenn ich sie brauche. Bei der Verwendung von Platzhaltern müssen Sie Werte binden und am Ende wird Ihr SQL-Code derselbe sein.

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

Und der letzte und für mich wichtige Punkt ist die Vermeidung des Fehlers "Anzahl der gebundenen Variablen stimmt nicht mit der Anzahl der Token überein".

Doctrine ist ein großartiges Beispiel für die Verwendung von Positionsplatzhaltern, weil es eine interne Kontrolle über die eingehenden Parameter hat.

Answer 2

Es ist nicht möglich, ein Array wie das in PDO zu verwenden.

Sie müssen z. B. für jeden Wert eine Zeichenkette mit einem Parameter erstellen (oder ? verwenden):

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

Hier ist ein Beispiel:

<?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);
}

Wenn Sie weiterhin Folgendes verwenden möchten bindParam können Sie dies stattdessen tun:

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

Wenn Sie Folgendes verwenden möchten ? Platzhalter, können Sie so vorgehen:

<?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);

Wenn Sie nicht wissen, ob $ids leer ist, sollten Sie es testen und diesen Fall entsprechend behandeln (ein leeres Array zurückgeben, oder ein Null-Objekt zurückgeben, oder eine Ausnahme auslösen, ...).

Answer 3

Nachdem ich das gleiche Problem hatte, bin ich zu einer einfacheren Lösung übergegangen (obwohl sie immer noch nicht so elegant ist wie eine PDO::PARAM_ARRAY wäre) :

angesichts des Arrays $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();

... und so weiter

Wenn Sie also ein Array mit gemischten Werten verwenden, benötigen Sie mehr Code, um Ihre Werte zu testen, bevor Sie den Typ param zuweisen:

// 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 ));

Aber ich habe das nicht getestet.

Answer 4

Hier ist meine Lösung. Ich habe auch die PDO-Klasse erweitert:

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);
        }
    }
}

Hier ist ein Beispiel für die Verwendung des obigen Codes:

$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'];
}

Lassen Sie mich wissen, was Sie denken

Answer 5

Mit MySQL und PDO können wir ein JSON-Array verwenden und JSON_CONTAINS() ( https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains ) zu suchen.

$ids = [123, 234, 345, 456]; // Array of users I search
$ids = json_encode($ids); // JSON conversion

$sql = <<<SQL
    SELECT ALL user_id, user_login
    FROM users
    -- Cast is mandatory beaucause JSON_CONTAINS() waits JSON doc candidate
    WHERE JSON_CONTAINS(:ids, CAST(user_id AS JSON))
    SQL;

$search = $pdo->prepare($sql);
$search->execute([':ids' => $ids]);
$users = $search->fetchAll();

Sie können auch verwenden JSON_TABLE() ( https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table ) für komplexere Fälle und JSON-Datenexploration:

$users = [
    ['id' => 123, 'bday' => ..., 'address' => ...],
    ['id' => 234, 'bday' => ..., 'address' => ...],
    ['id' => 345, 'bday' => ..., 'address' => ...],
]; // I'd like to know their login

$users = json_encode($users);

$sql = <<<SQL
    SELECT ALL user_id, user_login
    FROM users
    WHERE user_id IN (
        SELECT ALL user_id
        FROM JSON_TABLE(:users, '$[*]' COLUMNS (
            -- Data exploration...
            -- (if needed I can explore really deeply with NESTED kword)
            user_id INT PATH '$.id',
            -- I could skip these :
            user_bday DATE PATH '$.bday',
            user_address TINYTEXT PATH '$.address'
        )) AS _
    )
    SQL;

$search = $pdo->prepare($sql);
$search->execute([':users' => $users]);
...