尽管key不是字段名称，但是以密钥=>值格式获取MYSQL结果

Question

(I really just need some MYSQL magic, but I will explain a bit more of what I am doing)

I am building a translation system. I know one way would be to create a translations table for each of my content types. For example if I had a table for Posts then I would have a separate table for Post translations:

tbl posts
----------
id
title
content

tbl posts_translations
---------
id
title
content
locale

And then if I did a LEFT join and get the results with PHP I would get all data I want as key=>value pairs, and I can use in my application as $row['p_title']

Because I will have multiple content types to translate and rather than maintaining field names in this way, can I achieve the same result with one?

i.e.

tbl translations
--------------------
id
type      i.e 'posts'
type_id   i.e. posts.id
key       i.e. 'content', 'title'
value     i.e. 'this is a title'
locale

So to get all the translations keys for a single post, I would:

SELECT key, value FROM translations 
WHERE type = 'posts' 
AND type_id = 3

This would give me a result set like:

$row = array(
  'key' => 'title',
  'value' => 'This is a title'
)

But what I want is:

$row['title'] => 'This is the title'

Of course I could do a PHP loop to format the data, but is there something in MYSQL that will present this data for me without the overhead of looping each result?

NOTE

I would like the resulting array keys to be generated from the column/field value, not the column/field name.

Answer 1

There is no single step way to do it.

Write yourself a subroutine something like

// Given an array of arrays (not ASSOC), use the first column as "key"; second as "value"
function KeyValue($rows)
{
    $kv = array();
    foreach ($rows as $row)
    {
        $kv[$row[0]] = $row[1];
    }
    return $kv;
}