PHP PDO正确使用JOINS进行mySQL查询

Question

How would one correctly use a JOIN to add each row that matches the ID from a second table in as a column header of the first table.

Example.

THEME TABLE

ID     TITLE    DATE    TEXT
1     logo     23101    some sample text
2     back     23101    some sample text

THEME_META Table

refID     field     value
1         width     300
1         height    190
1         alt       some alternate text
2         bgcolor   #222

MySQL query would then be SELECT * FROM theme WHERE date = ?

I would then have foreached my results and have my theme item in a variable which I can use to echo the value for any column in the row.

eg. echo $theme['title'];

How would I also be able via a JOIN to be able to echo

$theme['width'] . $theme['height'] . $theme['alt']

Answer 1

What you are looking for is pivot the column field in the theme_meta table for each refID, unfortunatly, MySQL doesn't have the pivot table operator, but you can use the CASE expression to do so:

SELECT
  t.id,
  t.date,
  t.text,
  MAX(CASE WHEN m.field = 'width' THEN m.value ELSE NULL END) AS Width,
  MAX(CASE WHEN m.field = 'height' THEN m.value ELSE NULL END) AS height,
  MAX(CASE WHEN m.field = 'alt' THEN m.value ELSE NULL END) AS alt, 
  MAX(CASE WHEN m.field = 'bgcolor' THEN m.value ELSE NULL END) AS bgcolor,
FROM theme AS t
INNER JOIN theme_meta AS m ON t.id = m.refID
GROUP BY t.id,
  t.date,
  t.text;

---

Update: To do this dynamically instead of listing the values manually, you have to use dynamic sql, like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(m.field  = ''',
      m.field, ''', m.value, NULL)) AS ', '''', m.field  , '''')
  ) INTO @sql
FROM theme_meta AS m;

SET @sql = CONCAT('SELECT t.id,t.date,t.text, ', 
                  @sql , '
    FROM theme AS t
INNER JOIN theme_meta AS m ON t.id = m.refID
GROUP BY t.id,
  t.date,
  t.text;');

prepare stmt 
FROM @sql;

execute stmt;

• SQL Fiddle Demo