MYSQL：查询两个表并将第二个表中的结果连接到一个数组

I'm trying to figure out how to query my MYSQL Database - unfortunately I'm not good at MYSQL at all...

我正在试图找出如何查询我的MYSQL数据库 - 遗憾的是我根本不擅长MYSQL ......

I have two tables:

我有两张桌子:

posts:

---------------------
| id | name | type  |
---------------------
| 1  | mike | type1 |
| 2  | john | type2 |
| 3  | bill | type1 |
---------------------

postmeta:

-------------------------------
| id | post_id | key  | value |
-------------------------------
| 1  |    1    | key1 | val1  |
| 2  |    1    | key2 | val2  |
| 3  |    3    | key3 | val3  |
-------------------------------

What I want is some logic like:

我想要的是一些逻辑:

When posts.type == type 1 
select m.id, m.key, m.value from postmeta m
where post_id === posts.id

And best would be, if I had a $result => $row like this:

最好的是,如果我有一个$ result => $ row这样:

array (
  id => post.id,
  name => post.name,
  options => array ( 
    key => value
     ...
  )
)

Here is my query right now. It get's close, but doesn't do the trick yet:

这是我现在的询问。它已经接近了,但还没有做到这一点:

$sql = "SELECT p.id, p.post_title, p.post_content, p.post_type, m.meta_key, m.post_id, m.meta_id, m.meta_value 
    FROM posts p 
    JOIN postmeta m ON m.post_id = p.id
    WHERE type='type1'
    GROUP BY p.id";

What am I missing?

我错过了什么?

Thanks for your help!

谢谢你的帮助!

Seb

2 个解决方案

#1

You can select using an inner join and condition

您可以使用内部联接和条件进行选择

 select m.id, m.key, m.value 
 from postmeta m
 inner join posts as p on( p.id = m.id and p.type = 1)

#2

-1

You can use GROUP_CONTACT() function in SELECT query but be careful as it is length-limited and will truncate anything that goes past this limit.

您可以在SELECT查询中使用GROUP_CONTACT()函数但要小心,因为它是长度限制的,并将截断超过此限制的任何内容。

#1