MYSQL result formatting

Question

Say if we had:

tbl `posts`

id | post_type | post_id | post_key | post_value                      | Locale 
------------------------------------------------------------------------------
1  | news      | 1       | title    | The climate in Mongolia         | pl_pl
2  | news      | 1       | content  | Mongolia has strange weather... | pl_pl
3  | news      | 1       | date     | 2015-9-24                       | pl_pl

In order to get data for a post I would

SELECT post_key, post_value 
FROM posts 
WHERE post_id = 1 && post_type = 'news'
AND locale = 'pl_pl'

Then loop through the result set and mysqli_fetch_assoc() each row to give me an associative array with field names as the array keys;

$row['post_key'] == 'title'; $row['post_value'] == 'The climate in Mongolia';
$row['post_key'] == 'content'; $row['post_value'] == 'Mongolia has strange weather...';

What I would like is the actual values of post_key to be the array keys, keeping the value as post_value. So:

$row['title'] == 'The climate in Mongolia'; 
$row['content'] == 'Mongolia has strange weather...';

Yes I could format it like this with php and another loop, like:

$data = [];

foreach($rows as $r) {
    $data[$r['post_key']] = $r['post_value'];
}

But I want to know if there is some mysql magic that could save the additional processing required in PHP?

Further info about this app: As Gal rightly pointed out, it seems strange that I wouldn't just use the field names as per the post_key's / conventional table design. But this is a translations table and I am exploring the possibility of having one table to cater for translations of many other content tables, and that I can use for new content types without too much faffing around with creating multiple additional tables and maintaining new fields across them. Reasons that I am not to worried about performance at this stage: 1. being the only developer on this startup project and where decisions are being made on the fly, I believe this will save time and tick all boxes for now. 2, number of entries will be low compared to a couple of years time, where we would have time then to look a solution to any performance issue that arises.

I don't think PHP has anything like this. You can use array_map or array_walk but best is foreach... — Bik, Sep 24 '15 at 08:30
The structure of your table is whole wrong and would cause performance issues when you will start adding more posts. instead of `post_key` you should have created a new columns for `title`,`content` and `date` as there wont be a post without them,right? — Gal Sisso, Sep 24 '15 at 08:52
@Gal I should have explained my app better, this is for a translations table and I am doing this over having multiple, separate other tables. — digout, Sep 24 '15 at 09:08

score 2 · Answer 1 · edited May 23 '17 at 12:22

You can use a select like this to get all the post_key->post_value in a single row:

SELECT
  title,
  MAX(content) AS content,
  MAX(date) AS date
FROM (
  SELECT
    post_type,
    post_id,
    CASE
      WHEN post_key = 'title' THEN
        post_value
    END AS title,
    CASE
      WHEN post_key = 'content' THEN
        post_value
    END AS content,
    CASE
      WHEN post_key = 'date' THEN
        post_value
    END AS date
  FROM
    posts
  WHERE
    post_type = 'news' AND
    post_id = 1
) AS a
GROUP BY
  post_id

http://sqlfiddle.com/#!9/04684/1

UPDATE:

You can do something like i exposed but using the column value as a column name like this questions:

mysql select dynamic row values as column names, another column as value

MySQL select specific row values as column names

Then call a procedure like this:

How to call a MySQL stored procedure from within PHP code?

Hope it helps you!

+1 as this theoretically answers my question, but the whole point in having a table like this is to be versatile and save the need to maintain other tables and field names. So the time here to maintain individual queries specific to the post_type is not desirable. — digout, Sep 24 '15 at 09:41

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

You could. But as Gal stated, it's probably not the most efficient. If you value efficiency, I'd advice to just make meaningful columns.

edited May 23 '17 at 12:06

Community

1
1

answered Sep 24 '15 at 09:30

happybacon

26
3

MYSQL result formatting

2 Answers2