use an array of unknown length in where clause of mysql

Question

I am writing an SQL query. I have an array of unknown length and I want to select the data fromMySQL by using that array in the WHERE clause of the query. This is my query right now and it is working fine

$sql = "SELECT DISTINCT messagesutou.SenderID from messagesutou where (messagesutou.SenderID !='$items[1]' AND messagesutou.SenderID !='$items[0]' AND messagesutou.SenderID !='$items[2]') AND messagesutou.RecieverID='$uid'";

But in this I know the length of array ( 3) and I just used the array name with index for testing purpose. Now i want to know if array length is unknown then how would I write this query?

Where the array comes from? I think you look for `where messagesutou.SenderID not in (...)` — Jens, Jan 23 '20 at 12:59
WARNING: Your code is at risk from SQL Injection attacks and needs to be re-written using PHP Prepared Statements — Martin, Jan 23 '20 at 13:16

Akina · Accepted Answer · 2020-01-23T13:33:36.903

3

$list = implode(',', $items);

and

SELECT DISTINCT SenderID 
FROM messagesutou 
WHERE 0 = FIND_IN_SET(SenderID, '$list')
  AND RecieverID='$uid'

or (taken from Jens's answer which was deleted by him)

SELECT DISTINCT SenderID 
FROM messagesutou 
WHERE SenderID NOT IN ($list)
  AND RecieverID='$uid'

The difference - both variants are applicable when SenderID and $items values have a numeric type, only the former when they have string type, none when they have string type and contain commas or ticks.

But the latter may be adapted:

$list = '\''.implode('\',\'', $items).'\'';

and

SELECT DISTINCT SenderID 
FROM messagesutou 
WHERE SenderID NOT IN ($list)
  AND RecieverID='$uid'

It now acccepts any datatype and allows commas (but not ticks - they must be quoted before imploding).

edited Jan 23 '20 at 13:33

answered Jan 23 '20 at 13:07

Akina

31,909
5
12
21

1

You should be using PDO or MySQLi prepared statements – Martin Jan 23 '20 at 13:12
i m using mysqli – Umar Ghaffar Jan 23 '20 at 13:12
@Martin i use OP's method. – Akina Jan 23 '20 at 13:13
3

@Akina better to show the OP the problems with their current method. It's fundamentally insecure. – Martin Jan 23 '20 at 13:14
i got an error actually when i write only $items it gives output Array but when i write $item[n] it gives corresponding value at n index – Umar Ghaffar Jan 23 '20 at 13:17

use an array of unknown length in where clause of mysql

1 Answers1