CODEWITHSUNDEEP
phpmysqlsqljoin
qwaz
qwaz

Reputation: 1305

Join query: How to return results from join table2 as an array in the table1

I need to show a list of articles with a full set of tags each article corresponds to. I tried to do this query:

SELECT articles.article_id, `title`, `text`, tags.tag_name 
FROM `articles` 
LEFT JOIN `tags` 
on articles.article_id = tags.article_id 
WHERE articles.author_id = 150342

However that query returns the same row from table articles for every tag as many times as there are tags.

Like this:

Array
(
  [0] => Array
  (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => tag1
  )
 [1] => Array
 (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => tag2
 )
 [2] => Array
 (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => tag3
 )
)

Is there a way to return tag_name in an array for every article? To look something like this:

Array
(
 [0] => Array
 (
    [article_id] => 1
    [title] => title1
    [text] => text1
    [tag_name] => array ([0] => tag1, [1] => tag2, [2] => tag3)
 )
)

Upvotes: 0

Views: 75

Answers (4)

Hardy
Hardy

Reputation: 5631

No, you should do two queries or handle/parse the data different way in php.

Like:

$articles = array();

foreach($query_result as $row) {

  if (!array_key_exists($row['article_id'], $articles))
     $articles[$row['article_id']] = array(
       "title" => $row["title"],
       "text" => $row["text"],
       // etc
       "tags" => array()
     );

  $articles[$row['article_id']]['tags'][] = $row["tag_name"];

}

Upvotes: 1

rakeshjain
rakeshjain

Reputation: 1761

There is no concept of array in mySQL, but you can have them as comma separated list of tag_names for each artical id

SELECT articles.article_id, `title`, `text`, GROUP_CONCAT(tags.tag_name) as tag_names
FROM `articles` LEFT JOIN `tags` 
on articles.article_id = tags.article_id 
WHERE articles.author_id = 150342 
GROUP BY articles.article_id

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

First get a result from query and explod 'tag_name' from string to Array:

 SELECT A.article_id, A.`title`, A.`text`, group_concat(T.tag_name) a 'tag_name'
    FROM `articles`  A
    LEFT JOIN `tags` T
    on A.article_id = T.article_id 
    WHERE A.author_id = 150342 
    group by A.article_id, A.`title`, A.`text`

Example :

<?php
// Example 1
$tag_name = "piece1 piece2 piece3 piece4 piece5 piece6";
$pieces = explode(" ", $pizza);
echo $pieces[0]; // piece1
echo $pieces[1]; // piece2
?>

Upvotes: 1

Rohit Awasthi
Rohit Awasthi

Reputation: 686

Not array, but you can get comma separated string,

SELECT articles.article_id, `title`, `text`, GROUP_CONCAT(tags.tag_name )as tg_name
FROM `articles` 
LEFT JOIN `tags` 
on articles.article_id = tags.article_id 
WHERE articles.author_id = 150342
GROUP BY tags.article_id

Upvotes: 2

Related Questions