Geek asks and answers

Simulate a multidimensional array in MySQL

I have two tables with this structure:

articles: article_id, text tags: tag_id, article_id, text

How can I select all articles and all related tags with one query? As I know, MySQL can only return a two-dimensional array, so I can solve one of the following methods: merge all tags into a column of the result of selecting articles. But how?

+3
source share
4 answers

Take a look at the MySQL Group_Concat function . You can write something like

Select A.article_id, A.text GROUP_CONCAT(B.Text) As Tags 
From articles A 
Left Outer Join tags B Using (article_id) 
Group By A.article_id, A.Text

Unreason , , , , . , , fabrik.

+1

. n m- k- (). (n = m + k)

/ , , , - , Fabrik - .

GROUP_CONCAT , , SQL. , - , . .

+3
SELECT * FROM articles LEFT JOIN tags ON article.article_id = tags.article_id
+1
source

If you are sorting by article, you create a data view by skipping the article attributes associated with the duplicate article identifier, or simply rewrite them:

// select a.article_id, a.text, b.tag_id, b.text as tag_text
// from articles a, tags b
// where a.article_id=b.article_id
$articles=array();
while ($r=mysql_fetch_assoc($result)) {
  $article[$r['article_id']=array(
       'text'=>$r['text'];
       );
  @if (!is_array($article[$r['article_id']]['tags'])) {
       $article[$r['article_id']]['tags']=array();
  }
  $article[$r['article_id']]['tags'][$r['tag_id']]=$r['tag_text'];
}
0
source

More articles:


All Articles