Passing an array to a query using a WHERE clause

Question

Given an array of ids  galleries   array 1 2 5  I want to have a SQL query that uses the values of the array in its WHERE clause like   SELECT   FROM galleries WHERE id      values of array  galleries    eg   1    2    5       How can I generate this query string to use with MySQL

User · Answer

More an example    galleryIds    1   2    Vitruvian Man     ids   array filter  galleryIds  function  n  return  is numeric  n        ids   implode        ids     sql    SELECT   FROM galleries WHERE id IN    ids        output   SELECT   FROM galleries WHERE id IN  1  2     statement    pdo- gt prepare  sql    statement- gt execute

User · Answer

Below is the method I have used  using PDO with named placeholders for other data  To overcome SQL injection I am filtering the array to accept only the values that are integers and rejecting all others    owner id   123   galleries   array 1 2 5  abc      good galleries   array filter  chapter arr   is numeric      sql    SELECT   FROM galleries WHERE owner  OWNER ID AND id IN   good galleries     stmt    dbh- gt prepare  sql    stmt- gt execute array       OWNER ID    gt   owner id        data    stmt- gt fetchAll PDO  FETCH ASSOC

User · Answer

ints    query    SELECT   FROM   table  WHERE   column  IN   implode      array         strings    query    SELECT   FROM   table  WHERE   column  IN    implode        array

User · Answer

Using PDO  1   in   join      array fill 0  count  ids           select    lt  lt  lt SQL     SELECT       FROM galleries     WHERE id IN   in   SQL   statement    pdo- gt prepare  select    statement- gt execute  ids    Using MySQLi  2   in   join      array fill 0  count  ids           select    lt  lt  lt SQL     SELECT       FROM galleries     WHERE id IN   in   SQL   statement    mysqli- gt prepare  select    statement- gt bind param str repeat  i   count  ids        ids    statement- gt execute     result    statement- gt get result      Explanation  Use the SQL IN   operator to check if a value exists in a given list  In general it looks like this  expr IN  value       We can build an expression to place inside the    from our array  Note that there must be at least one value inside the parenthesis or MySQL will return an error  this equates to making sure that our input array has at least one value  To help prevent against SQL injection attacks  first generate a   for each input item to create a parameterized query  Here I assume that the array containing your ids is called  ids   in   join      array fill 0  count  ids            select    lt  lt  lt SQL     SELECT       FROM galleries     WHERE id IN   in   SQL   Given an input array of three items  select will look like   SELECT   FROM galleries WHERE id IN            Again note that there is a   for each item in the input array  Then we ll use PDO or MySQLi to prepare and execute the query as noted above  Using the IN   operator with strings It is easy to change between strings and integers because of the bound parameters  For PDO there is no change required  for MySQLi change str repeat  i   to str repeat  s   if you need to check strings   1   I ve omitted some error checking for brevity  You need to check for the usual errors for each database method  or set your DB driver to throw exceptions    2   Requires PHP 5 6 or higher  Again I ve omitted some error checking for brevity

User · Answer

As Flavius Stef s answer  you can use intval   to make sure all id are int values    ids   join      array map  intval    galleries       sql    SELECT   FROM galleries WHERE id IN   ids

User · Answer

We should take care of SQL injection vulnerabilities and an empty condition  I am going to handle both as below   For a pure numeric array  use the appropriate type conversion viz intval or floatval or doubleval over each element  For string types mysqli real escape string   which may also be applied to numeric values if you wish  MySQL allows numbers as well as date variants as string   To appropriately escape the values before passing to the query  create a function similar to   function escape  string           Assuming  db is a link identifier returned by mysqli connect   or mysqli init       return mysqli real escape string  db   string       Such a function would most likely be already available to you in your application  or maybe you ve already created one   Sanitize the string array like    values   array map  escape    gallaries     A numeric array can be sanitized using intval or floatval or doubleval instead as suitable    values   array map  intval    gallaries     Then finally build the query condition   where    count  values      id         implode    OR  id         values          0    or   where    count  values      id  IN       implode          values           0    Since the array can also be empty sometimes  like  galleries   array    we should therefore note that IN    does not allow for an empty list  One can also use OR instead  but the problem remains  So the above check  count  values   is to ensure the same   And add it to the final query    query     SELECT   FROM  galleries  WHERE      where       TIP  If you want to show all records  no filtering  in case of an empty array instead of hiding all rows  simply replace 0 with 1 in the ternary s false part

User · Answer

Col  Shrapnel s SafeMySQL library for PHP provides type-hinted placeholders in its parametrised queries  and includes a couple of convenient placeholders for working with arrays  The  a placeholder expands out an array to a comma-separated list of escaped strings    For example    someArray    1  2  5    galleries    db- gt getAll  SELECT   FROM galleries WHERE id IN   a     someArray       Note that since MySQL performs automatic type coercion  it doesn t matter that SafeMySQL will convert the ids above to strings - you ll still get the correct result

User · Answer

We can use this  WHERE id IN  clause if we filter the input array properly  Something like this    galleries   array     foreach    REQUEST  gallery id   as  key   gt   val         galleries  key    filter var  val  FILTER SANITIZE NUMBER INT       Like the example below    galleryIds   implode       galleries     I e  now you should safely use  query    SELECT   FROM galleries WHERE id IN    galleryIds

User · Answer

Basic methods to prevent SQL injection are    Use prepared statements and parameterized queries Escaping the special characters in your unsafe variable   Using prepared statements and parameterized queries query is considered the better practice  but if you choose the escaping characters method then you can try my example below   You can generate the queries by using array map to add a single quote to each of elements in the  galleries    galleries   array 1 2 5     galleries str   implode                            array map function  amp  item                                      return      mysql real escape string  item                                            galleries      sql    SELECT   FROM gallery WHERE id IN       galleries str           The generated  sql var will be   SELECT   FROM gallery WHERE id IN   1    2    5         Note  mysql real escape string  as described in its documentation here  was deprecated in PHP 5 5 0  and it was removed in PHP 7 0 0  Instead  the MySQLi or PDO MySQL extension should be used  See also MySQL  choosing an API guide and related FAQ for more information  Alternatives to this function include          mysqli real escape string     PDO  quote

User · Answer

Because the original question relates to an array of numbers and I am using an array of strings I couldn t make the given examples work   I found that each string needed to be encapsulated in single quotes to work with the IN   function     Here is my solution  foreach  status as  status a             status sql           status a                  status   implode      status sql     sql   mysql query  SELECT   FROM table WHERE id IN   status       As you can see the first function wraps each array variable in single quotes      and then implodes the array   NOTE   status does not have single quotes in the SQL statement   There is probably a nicer way to add the quotes but this works

User · Answer

BEWARE  This answer contains a severe SQL injection vulnerability  Do NOT use the code samples as presented here  without making sure that any external input is sanitized     ids   join        galleries       sql    SELECT   FROM galleries WHERE id IN    ids

User · Answer

Use   select id from galleries where id in  1  2  5     A simple for each loop will work   Flavius AvatarKava s way is better  but make sure that none of the array values contain commas

User · Answer

Safer    galleries   array 1 2 5   array walk  galleries    intval     ids   implode       galleries    sql    SELECT   FROM galleries WHERE id IN   ids

User · Answer

Besides using the IN query  you have two options to do so as in an IN query there is a risk of an SQL injection vulnerability  You can use looping to get the exact data you want or you can use the query with OR case  1  SELECT         FROM galleries WHERE id 1 or id 2 or id 5    2   ids   array 1  2  5      foreach   ids as  id           data     SELECT                       FROM galleries WHERE id   id

User · Answer

You may have table texts  T ID  int   T TEXT  text   and table test  id  int   var  varchar 255     In insert into test values  1   1 2 3     the following will output rows from table texts where T ID IN  1 2 3    SELECT   FROM  texts  WHERE  SELECT FIND IN SET  T ID    SELECT var FROM test WHERE id  1     AS tm   gt 0   This way you can manage a simple n2m database relation without an extra table and using only SQL without the need to use PHP or some other programming language

User · Answer

For MySQLi with an escape function    ids   array map function  a  use  mysqli         return is string  a         mysqli- gt real escape string  a         a        ids    ids   join       ids      result    mysqli- gt query  SELECT   FROM galleries WHERE id IN   ids       For PDO with prepared statement    qmarks   implode      array fill 0  count  ids           sth    dbh- gt prepare  SELECT   FROM galleries WHERE id IN   qmarks      sth- gt execute  ids

User · Answer

Safe way without PDO    ids   array filter array unique array map  intval    array  ids      if   ids         query    SELECT   FROM  galleries  WHERE  id  IN    implode       ids              array  ids Cast  ids variable to array array map Transform all array values into integers array unique Remove repeated values array filter Remove zero values implode Join all values to IN selection

User · Answer

Assuming you properly sanitize your inputs beforehand      matches   implode       galleries     Then just adjust your query   SELECT   FROM galleries WHERE id IN    matches      Quote values appropriately depending on your dataset

[php] Passing an array to a query using a WHERE clause

Examples related to php

Examples related to mysql

Examples related to arrays