This is the result for Those Two table i got By using this query:
SELECT t1.*, GROUP_CONCAT(t2.content_id)
FROM news_categories t1
LEFT JOIN news_content t2
ON t2.cat_ids = t1.cat_id
GROUP BY t1.cat_id
How can I get the information in table2 from the result(table) GROUP_CONCAT(t2.content_id) related to the content_id?
and Retrive those data from table2 Using PHP
Well, you got a one-to-many relationship here. Meaning one row in table1 can be related to 0-n rows in table2.
If you you group by the primary key of table1 you limit the result to one result row for each row in table one, by using GROUP_CONCAT(table2_col) you concatenate all matching rows of the table2_column with a separator which is comma by default.
Play with the following queries:
SELECT
t1.*,
GROUP_CONCAT(t2.content_id) AS content_ids,
GROUP_CONCAT(t2.title) AS content_titles
FROM
news_categories t1
LEFT JOIN news_content t2 ON t2.cat_ids = t1.cat_id
GROUP BY t1.cat_id
Or
SELECT
t1.*,
t2.*
FROM
news_categories t1
LEFT JOIN news_content t2 ON t2.cat_ids = t1.cat_id
I recommend to write out the columns you want, instead of using .*
EDIT
To separate the concatenated values in php have a look at explode
You fetch the data as usual e.g.
$result = mysqli_query( $query );
while ( $row = mysqli_fetch_assoc( $result ) ) {
print_r( $row );
}
Have a look at this examples
GROUP_CONCAT(t2.content_id), why notGROUP_CONCAT(t2.title)?