MYSQL: Query two tables and join results from second table to an array

Question

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

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:

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

You will not get your nested array directly from your "flat" query - but you can transform it that way very easy. — DerVO
– DerVO, Commented May 9, 2016 at 7:14
how are you currently fetching the results from your SQL query ? — Vishnu Nair
– Vishnu Nair, Commented May 9, 2016 at 7:16

ScaisEdge · Accepted Answer · 2016-05-09 07:20:00Z

2

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)

answered May 9, 2016 at 7:20

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

1 Comment

Seb Over a year ago

Not 100% what I wanted - but about 98% and I figured it out the rest myself. ;-) Thanks a lot!

DD77 · Accepted Answer · 2016-05-09 07:20:07Z

-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.

answered May 9, 2016 at 7:20

DD77

8162 gold badges9 silver badges27 bronze badges

Collectives™ on Stack Overflow

MYSQL: Query two tables and join results from second table to an array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related