Select Query by array values

Question

I have an array of ids, I have to select from table for each value of array , i can get it by one by one in for loop,

SELECT  point, privacy FROM `tableName` WHERE id='1403176452487620892'and status=1

but the problem is that array size is 100, i need a single query not 100.

Not that similar, but you're running it on the JVM so you're using the JDBC driver. That means you can bind the values no problem. — Alvin Thompson
– Alvin Thompson, Commented Jun 20, 2014 at 7:07

Alvin Thompson · Accepted Answer · 2014-06-20 06:16:34Z

2

Why can't you use:

SELECT  point, privacy FROM `tableName` WHERE status=1 and id in(?,?,?...)

Yes, it's ridiculously long, but if one query is what you need...

answered Jun 20, 2014 at 6:16

Alvin Thompson

5,4784 gold badges28 silver badges43 bronze badges

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

5 Comments

Govind Singh Over a year ago

Use a for loop for that as well your comment in that post, sorry i didn't get it

Alvin Thompson Over a year ago

@GovindSinghNagarkoti: but now you do?

Alvin Thompson Over a year ago

@GovindSinghNagarkoti: sorry, what did you mean by, "means its not a correct way to use IN my case"?

Govind Singh Over a year ago

did i have to use your query or not

Alvin Thompson Over a year ago

Yep. No alternative in JDBC.

Bindiya Patoliya · Accepted Answer · 2014-06-20 06:17:19Z

1

You can use like this :

   $ids = join(',',$ids);  
   $sql = "SELECT * FROM tableName WHERE id IN ($ids)";

answered Jun 20, 2014 at 6:17

Bindiya Patoliya

2,7821 gold badge18 silver badges16 bronze badges

11 Comments

Alvin Thompson Over a year ago

Is the array of IDs in the DB? MySQL can't do for loops, can it?

Govind Singh Over a year ago

$ids = join(',',$ids); not familiar with this syntax did it convert it to comma delimated string

Bindiya Patoliya Over a year ago

Yes,it convert it to comma delimated string.

Govind Singh Over a year ago

@AlvinThompson its not in db

Alvin Thompson Over a year ago

@GovindSinghNagarkoti: so use a for loop in whatever language it's coming from to construct that huge in statement with all those values, and bind the values from the array. Unless you're using Oracle, having 100 values in your in statement presents no problem.

|

suman halder · Accepted Answer · 2014-06-20 06:43:43Z

0

You can try like this

$array = array(1,2,3);
$str = implode(",", $array);
$sql = "SELECT  point, privacy FROM `tableName` WHERE id in ($str) and status=1";

answered Jun 20, 2014 at 6:43

suman halder

12 bronze badges

Collectives™ on Stack Overflow

Select Query by array values

3 Answers 3

5 Comments

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related