I am trying to achieve something in MySQL but even with all the answers here or on other sites, I still cannot accomplish the goal.
I have two tables, in one-to-many relationship.
TABLE Files
COLUMNS id, title, description, uploaded, size, extension, etc.
TABLE Files_Meta
COLUMNS id, parent_id, key, value
Obviously each file has multiple meta data represented as many rows in the Files_Meta table. For example File1 has meta Author, Place, Time, Tags, Camera -- if it's a photo.
I'm trying to select all rows from Files table including meta data.
Standard result
stdClass Object
(
[id] => 10
[title] => Hello world
[size] => 745198
[extension] => jpg
[user_id] => 0
[category_id] => 0
[date_uploaded] => 2012-06-08 13:37:55
[description] =>
[downloaded] => 0
[viewed] => 8
)
stdClass Object
(
[id] => 90
[parent_id] => 10
[key] => place
[value] => New York
)
What I want
stdClass Object
(
[id] => 10
[title] => Hello world
[size] => 745198
[extension] => jpg
[user_id] => 0
[category_id] => 0
[date_uploaded] => 2012-06-08 13:37:55
[description] =>
[downloaded] => 0
[viewed] => 8
[meta] => Array (
place => New York
author => John Doe
time => March 2001
camera => Canon EOS
etc.
)
)
Is it possible to achieve this in MySQL? Doesn't have to look like this, without that array.
stdClass Object
(
[id] => 10
.
.
[place] => New York
[author] => John Doe
[time] => March 2001
[camera] => Canon EOS
)
Thanks in advance for replies or tips.