PHP mysqli SELECT * from my_table WHERE id = array_of_ids

Question

I have been using this site for years now and this is the first time I'm asking a question here, so kinda scared right now :D

Here's what my problem is, I have got two tables. In table_a I got three columns and in table_b I got 5. So the setup right now looks something like this:

       table_a

| r_id | foo   | bar |
+------+-------+-----+
| 1    | dude  | 5   |
+------+-------+-----+
| 2    | homie | 6   |
+------+-------+-----+
| 3    | bro   | 7   |
+------+-------+-----+

       table_b

| id | ada   | rea | lm   | cor  |
+----+-------+-----+------+------+
| 5  | ching | ink | jk   | 32.4 |
+----+-------+-----+------+------+
| 1  | momo  | pal | lmao | 95.5 |
+----+-------+-----+------+------+
| 6  | mama  | pen | lol  | 26.9 |
+----+-------+-----+------+------+
| 4  | chac  | pin | fun  | 91.2 |
+----+-------+-----+------+------+
| 7  | chim  | lap | funk | 82.4 |
+----+-------+-----+------+------+
| 9  | cho   | kil | fin  | 38.1 |
+----+-------+-----+------+------+

Now what I'm trying to do is to get all the data from table_a and then only get lm from table_b. I'm getting all the data from table_a like this:

SELECT r_id, foo, bar from table_a

I need to use the ids I get from bar column to get lm from table_b. So is there a way I can pass an array to only get the data based on the ids in an array? If not, then what would be the most efficient way to get those?

The output I'm expecting is jk, lol, funk. Would appreciate any help, thanks!

If you want to stick to using an array and an in clause - then it's a duplicate of https://stackoverflow.com/questions/907806/passing-an-array-to-a-query-using-a-where-clause — Nigel Ren, Nov 13 '18 at 07:25

score 3 · Accepted Answer · answered Nov 13 '18 at 07:16

3

You should be looking at using a JOIN to link the two tables together in 1 query...

SELECT  r_id, foo, bar, lm
    FROM table_a 
    JOIN table_b on bar = id

answered Nov 13 '18 at 07:16

Nigel Ren

56,122
11
43
55

Although you can do almost anything in PHP - SQL can make your life so much easier so worth taking some time to have a play with it. – Nigel Ren Nov 13 '18 at 07:27

Kamal Paliwal · Answer 2 · 2018-11-13T07:21:21.037

1

For that you can try WHERE IN feature of SQL.

SELECT lm from table_b WHERE id IN(ARRAY_OF_IDS)

Or you can also use join to achieve this

Select tale_a.*, tale_b.lm from tale_a inner join table_b ON tale_a.bar=tale_b.id

edited Nov 13 '18 at 07:21

answered Nov 13 '18 at 07:13

Kamal Paliwal

1,251
8
16

Thanks! that was pretty quick. So how would I do it using PHP. $bar_ids = array(5,6,7) $query = 'SELECT lm from table_b WHERE id IN($bar_ids)' and then run the query ? – M. Ather Khan Nov 13 '18 at 07:14
Yes, you can use this variable in it. $query = 'SELECT lm from table_b WHERE id IN($bar_ids)'. Or if bar is related with table_b id column then you can use joins too. – Kamal Paliwal Nov 13 '18 at 07:17
Thanks!! I tried Nigel's solution and it worked as expected. However, I'm going to keep this array thing in mind for future stuff. Thanks once again! – M. Ather Khan Nov 13 '18 at 07:28

score 1 · Answer 3 · answered Nov 13 '18 at 07:15

1

try inner join

SELECT a.r_id, a.foo, a.bar, b.lm  from table_a as a inner join table_b as b on b.id=a.bar

answered Nov 13 '18 at 07:15

Bhargav Chudasama

6,928
5
21
39

score 1 · Answer 4 · answered Nov 13 '18 at 07:16

1

Why not join?

select group_concat(lm) as lm_list
from table_b b
inner join table_a a on b.id = a.bar

You can use the GROUP_CONCAT() function, with this you would get jk, lol, funk otherwise you would get 3 rows each of one lm value,

answered Nov 13 '18 at 07:16

Paul Maxwell

33,002
3
32
51

Gufran Hasan · Answer 5 · 2018-11-13T07:45:53.627

You can use INNER JOIN

 SELECT tale_a.r_id, tale_a.foo, tale_a.bar ,table_b.lm 
 FROM tale_a
 INNER JOIN table_b
 ON tale_a.bar=table_b.id

Note: It returns all columns from table_a and only one column from table_b

Resultant Output:

| r_id | foo   | bar | lm  |
+------+-------+-----+-----+
| 1    | dude  | 5   | jk  |
+------+-------+-----+-----+
| 2    | homie | 6   |lol  |
+------+-------+-----+-----+
| 3    | bro   | 7   |funk |
+------+-------+-----+-----+

score 0 · Answer 6 · answered Nov 13 '18 at 07:22

I assume that you have array of IDs having IDs. So first make a comma separated string of that array of IDs like this:

ids_str = implode("," $ARRAY_OF_IDS);

and then use that ids_str in IN os mysql query like below:

SELECT lm from table_b WHERE id IN( ids_str )

PHP mysqli SELECT * from my_table WHERE id = array_of_ids

6 Answers6