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.
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.
: dilema. don't forget to tell the OP about the binds, to accomplish this.
yourListOfIds. how to put list of id?See this question, but if you look at the comments on the manual you'll find lots of people talking about exploding.
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
