在MySQL上模拟多维数组

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?

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.

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

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.

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'];
}