CODEWITHSUNDEEP
phpmysqlmysqli
M. Ather Khan
M. Ather Khan

Reputation: 335

PHP mysqli SELECT * from my_table WHERE id = array_of_ids

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!

Upvotes: 1

Views: 1347

Answers (6)

Gufran Hasan
Gufran Hasan

Reputation: 9373

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 |
+------+-------+-----+-----+

Upvotes: 0

PrakashG
PrakashG

Reputation: 1642

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 )

Upvotes: 0

Kamal Paliwal
Kamal Paliwal

Reputation: 1301

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

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

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,

Upvotes: 1

Nigel Ren
Nigel Ren

Reputation: 57121

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

Upvotes: 3

Bhargav Chudasama
Bhargav Chudasama

Reputation: 7161

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

Upvotes: 1

Related Questions