PHP MySql: explode and JOIN Combination

Question

I put user id with separate comma in MySql TABLE for best user. Example : 1,2,3 Now i work with PHP explode() function for this result:

$bestuser = explode(',',$bestuser);

i have another MySql TABLE for list of user with this row : id/name/datejoin/birthday ....

now, i need to print name of best user with MySql JOIN Methods. actually my mean how to combination explode result with other MySql TABLE result.

NOTE: i know this design(1,2,3) is bad, But I have no choice.

Why on earth would **you** *put user id with separate comma* and have *no choice*?! Ay caramba — Kermit, Jan 18 '13 at 00:01
because, i work with any module of cms and this module work with this method. I'm forced to this. — BaTmaN, Jan 18 '13 at 00:05
See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Jan 18 '13 at 00:11
@eggyal: You right, i confess in my question.now, I'm looking for a solution for this. — BaTmaN, Jan 18 '13 at 07:51
As noted in the answer to which I linked, it is "*Hard to join the values to the lookup table they reference*" - which (if I've understood your question correctly) is what you're trying to do. Bill Karwin *literally* wrote the book on this subject, so I'd probably pay attention when he tries to discourage using a certain pattern. How is it that you *have no choice*? Perhaps I've just been lucky, but I don't think I've ever found myself in a situation where there is literally no other choice: in my experience, there's almost always room for negotiation. — eggyal, Jan 18 '13 at 09:11

score 1 · Answer 1 · answered Jan 18 '13 at 00:15

1

You could write an SQL query to do this:

SELECT id,name
FROM user
WHERE id IN (:yourListOfIds)

Be cautious of SQL injection if the list is at any way user supplied.

answered Jan 18 '13 at 00:15

Jim

22,354
6
52
80

Don't forget to mention what Database API you are using; furthermore, with the `:` dilema. don't forget to tell the OP about the binds, to accomplish this. – Daryl Gill Jan 18 '13 at 00:16
@Jim : what is it? `yourListOfIds`. how to put list of id? – BaTmaN Jan 18 '13 at 07:48

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

See this question, but if you look at the comments on the manual you'll find lots of people talking about exploding.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 18 '13 at 00:16

LuckySpoon

588
4
17

score 0 · Answer 3 · answered Jan 18 '13 at 09:26

One can use MySQL's FIND_IN_SET() function in the join criterion:

table_a JOIN table_b ON FIND_IN_SET(table_a.id_a, table_b.csv_a)

However (per the warnings in my comments above) this operation will be terribly inefficient, as MySQL must fully scan both tables.

A much better solution would be to create a table of relations:

CREATE TABLE relations (
  FOREIGN KEY (id_a) REFERENCES table_a (id_a),
  FOREIGN KEY (id_b) REFERENCES table_b (id_b)
) SELECT table_a.id_a, table_b.id_b
  FROM   table_a JOIN table_b
      ON FIND_IN_SET(table_a.id_a, table_b.csv_a);

ALTER TABLE table_b DROP csv_a;

Then one can query for required data by joining the tables as required:

SELECT   table_a.*
FROM     table_a JOIN relations USING (id_a)
WHERE    relations.id_b = ?

If so desired, one could even use MySQL's GROUP_CONCAT() function to obtain the original CSV:

SELECT   table_b.id_b, GROUP_CONCAT(relations.id_a) AS csv_a
FROM     table_b JOIN relations USING (id_b)
WHERE    ...
GROUP BY table_b.id_b

PHP MySql: explode and JOIN Combination

3 Answers3