Checking value in an array inside one SQL query with WHERE clause

Question

I want to know is this practically possible in sql(using php as server-side), where in you have an array of values(numbers), and you try to retrieve data based on values inside that array..

I have a sql table:

posts(
    id int(11) primary key,
    posts varchar(140),
    user_id int(11) foreign key
);

I write a query now to retrieve 'posts':

$query="SELECT * FROM posts WHERE user_id=(**Array of select user_id's**)";

Is there any sql in-built function to check values inside an array? or should I be using PHP for this?

I am actually trying to implement the twitter model of showing posts of people whom we follow.

SELECT * FROM posts WHERE user_id IN (1,2,3);

Devart
– Devart

2011-11-25 13:59:34 +00:00
Commented Nov 25, 2011 at 13:59 — Devart
– Devart, Commented Nov 25, 2011 at 13:59

liquorvicar · Accepted Answer · 2011-11-25 14:01:17Z

46

Yes, this is easily possible. You need to look at MySQL's IN function

Your query would be something like

SELECT * FROM posts WHERE user_id IN (1,2,3,4,5,6)

You can build the bit in between the parentheses in PHP using implode()

answered Nov 25, 2011 at 14:01

liquorvicar

6,1462 gold badges19 silver badges21 bronze badges

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

Comments

Hikaru-Shindo · Accepted Answer · 2017-04-10 15:27:25Z

21

SQL can't parse PHP arrays. Try this:

$query="SELECT * FROM posts WHERE user_id IN ({implode(',', $userIDarray)})";

edited Apr 10, 2017 at 15:27

answered Nov 25, 2011 at 13:58

Hikaru-Shindo

1,90112 silver badges22 bronze badges

5 Comments

FtDRbwLXw6 Over a year ago

Just be careful that the array values have been sanitized for SQL injection.

Hikaru-Shindo Over a year ago

Of course... I for myself would use prepared statements, but that's not the question ;)

Robbie Dc Over a year ago

wat sanitary functions needed to sanitise an array?

Hikaru-Shindo Over a year ago

in this case only integer values are permitted. So you need to check for integer values in each array element. If you don't generate this array from user inputs (or the values of the arrays do not contain any) you can skip this.

Wouter Over a year ago

you forgot an ) after the variable

Bas Slagter · Accepted Answer · 2011-11-25 13:59:30Z

3

Take a look at this page : WHERE ... IN. You can use the IN operator to check if a certain value exists within an list of values.

answered Nov 25, 2011 at 13:59

Bas Slagter

9,9479 gold badges50 silver badges81 bronze badges

Comments

Andrew Barber · Accepted Answer · 2012-12-05 07:52:48Z

-1

Yea you will have to you use following syntax

SELECT 'value'
 IN (array)

edited Dec 5, 2012 at 7:52

Andrew Barber

40.3k20 gold badges97 silver badges124 bronze badges

answered Dec 4, 2012 at 22:56

Emy

352 bronze badges

1 Comment

Andrew Barber Over a year ago

stop linking to your website like this.

Collectives™ on Stack Overflow

Checking value in an array inside one SQL query with WHERE clause

4 Answers 4

Comments

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related