How to merge two SQL queries into one?

Question

MySQL

I have a query:

$db->query( "SELECT p.id, p.date, p.title, p.category, p.country, p.autor, p.alt_name, p.comm_num, p.approve, p.fixed, e.news_read, e.votes, e.user_id, e.need_pass FROM " . PREFIX . "_post p LEFT JOIN " . PREFIX . "_post_extras e ON (p.id=e.news_id) " . $where . " ORDER BY " . $order_by . " LIMIT {$start_from},{$news_per_page}" );

In p.country, country IDs are stored in this form:
1 news (1 , 2)
2 news (3)
3 news (1, 3)

There is another world_country table, where each 'ID' has the corresponding values ​​in the 'name' column.

How to substitute ID in the news so that the final result is 1 news (Russia, USA), 2 news (England), 3 news (Russia, England)?

Answer 1

In my opinion, it would be more correct to merge in PHP: the table of countries is obviously small, it can be unloaded and cached as an ID => NAME array. Then just substitute the name in PHP instead of the ID.
Of course, you can do it in sql, but it will be ugly and not very productive.

Answer 2

As usual - a crooked storage structure leads to processing problems.

SELECT p.*, GROUP_CONCAT(c.name) countries
FROM p
JOIN с ON FIND_IN_SET(c.id, p.country)
GROUP BY p.id

If the p.country field also contains stray spaces, they must be removed before linking.
A strong tip is to normalize the data. CSV is an extremely unfortunate solution.