Simulate multidimensional array on MySQL

Question

I have two tables with these structure:

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

How can I select all articles AND all associated tags with one query? As I know, MySQL can only return a two-dimensional array, so I can one solution would be to concatenate all tags into a column of an articles select result. But how?

Unreason · Accepted Answer · 2011-03-21 15:19:21Z

3

Relational data is organised in tuples. Tuples have n dimensions and can easily describe functional mappings from m-dimensional vectors to scalar or k-dimensional values with a single relation (table). (n = m + k)

If what you need/suggest is for presentation layer only then you can do what Eric Hogue suggests, but if this is somewhere mid process or at the start of another process then Fabrik's suggestion is the proper way to go.

GROUP_CONCAT makes data non relational and hence much harder to use as basis for subsequent processing with SQL. So, the purpose of this command is to make output pertty if your client layer can not do it for you. Do not try to model your core relations according to presentation issues.

edited Mar 21, 2011 at 15:19

answered Mar 21, 2011 at 13:24

Unreason

12.7k2 gold badges39 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zorrocaesar Over a year ago

I'm not sure I understand. If I use @Fabrik's suggestion I will get multiple rows which contain the same article information.

Unreason Over a year ago

Yes, and then you can use application server/client side to reformat. Please try to get the concepts of multi-tier architecture before going ahead en.wikipedia.org/wiki/Multitier_architecture. For example you question on how to include ids of the tags as well is indicative that you are mixing presentation and logic. Also talking about extracting data (from the presentation layer).

Unreason Over a year ago

In nutshell: you should not store or process formatted data (presentation layer) - simple example: don't store or process dates as formatted strings, or in you example don't store or process relational data as comma separated values (as it leads to unnecessary complexities). That is a general principle - keeping data relational is better. It might be possible that in stable and very well isolated use case you can deviate from this principle with no ill effects, but your further questions makes me worried if that is really your case.

Eric Hogue · Accepted Answer · 2011-03-21 14:07:03Z

1

Take a look at the Group_Concat MySQL 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

As Unreason mention in his answer, this should only be used if you want to display the data and I suggested it because you mention concatenating the tags in the questions. If you need to work with the date, use a simple join as fabrik suggested.

edited Mar 21, 2011 at 14:07

answered Mar 21, 2011 at 13:02

Eric Hogue

8,9504 gold badges28 silver badges21 bronze badges

2 Comments

Zorrocaesar Over a year ago

This is exactly was I asked for. I now have one row for each article and I can also extract all the tag names. But how about if I also needed the ids of the tags?

Eric Hogue Over a year ago

You can probably do something like GROUP_CONCAT(CONCAT(B.article_ID, ' - ', B.Text)) but I have never try anything like this. You should probably extract it like fabrik suggested and format your data in your php code. Check Unreason's comments.

fabrik · Accepted Answer · 2011-03-21 13:02:58Z

1

SELECT * FROM articles LEFT JOIN tags ON article.article_id = tags.article_id

answered Mar 21, 2011 at 13:02

fabrik

14.4k8 gold badges58 silver badges71 bronze badges

Comments

symcbean · Accepted Answer · 2011-03-21 14:32:09Z

0

If you sort by article, then you create a representation of the data by skipping article attributes associated with a repeating article id - or just overwrite 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'];
}

answered Mar 21, 2011 at 14:32

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

Collectives™ on Stack Overflow

Simulate multidimensional array on MySQL

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related