use an array of unknown length in where clause of mysql [duplicate]

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
– Jens, Commented Jan 23, 2020 at 12:59
WARNING: Your code is at risk from SQL Injection attacks and needs to be re-written using PHP Prepared Statements — Mm-Art-In
– Mm-Art-In, Commented Jan 23, 2020 at 13:16

Akina · Accepted Answer · 2020-01-23 13:33:36Z

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, 2020 at 13:33

answered Jan 23, 2020 at 13:07

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mm-Art-In Over a year ago

You should be using PDO or MySQLi prepared statements

Umar Ghaffar Over a year ago

i m using mysqli

Akina Over a year ago

@Martin i use OP's method.

Mm-Art-In Over a year ago

@Akina better to show the OP the problems with their current method. It's fundamentally insecure.

Umar Ghaffar Over a year ago

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

Collectives™ on Stack Overflow

use an array of unknown length in where clause of mysql [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related