How do I mysql select with aliases from another table?

I'm working with a CMS system where I cannot control database column names. And I've got two related tables:

Table: content

+------------+----------+----------+----------+----------+
| content_id | column_1 | column_2 | column_3 | column_4 |
+------------+----------+----------+----------+----------+
|          1 |    stuff |     junk |     text |     info |
|          2 |    trash |     blah |     what |      bio |
+------------+----------+----------+----------+----------+

Table: column_names

+------------+-------------+
| column_id  | column_name |
+------------+-------------+
|          1 |   good_text |
|          2 |    bad_text |开发者_JS百科
|          3 |   blue_text |
|          4 |    red_text |
+------------+-------------+

What I'd like to do here is select from the first table, but select the columns AS the column_name from the second table. So my result would look like:

+------------+-----------+----------+-----------+----------+
| content_id | good_text | bad_text | blue_text | red_text |
+------------+-----------+----------+-----------+----------+
|          1 |     stuff |     junk |      text |     info |
|          2 |     trash |     blah |      what |      bio |
+------------+-----------+----------+-----------+----------+

---

There is no way to do this, but as long as your columns won't change too often, a VIEW on that table with hard-coded aliases could be sufficient.

---

To my knowledge you can't use subselects as the target of an AS in SQL. If I were you, I'd load the data from column_names into an array (I'd also cache it in an in-memory DB since it isn't going to change all that often), then use that array to build my SQL.

A very rough example (in PHP):

$column_relations = array();

$sql = "SELECT * FROM column_names;";
$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res))
{
  $column_relations[$row['column_name']] = 'column_' . $row['column_id'];
}

$sql = sprintf("SELECT content_id, '%s' AS good_text, '%s' AS bad_text, '%s' AS blue_text, '%s' AS red_text FROM content;", $column_relations['good_text'], $column_relations['bad_text'], $column_relations['blue_text'], $column_relations['red_text']);

---

SELECT
cn. column_id id,
MAX(IF(content_id=1,column_1,null)) as good_text,
MAX(IF(content_id=2,column_2,null)) as bad_text,
MAX(IF(content_id=3,column_3,null)) as blue_text,
MAX(IF(content_id=4,column_4,null)) as red_text
FROM content ct 
INNER JOIN column_names cn  ON (cn. column_id  = ct.content_id)
GROUP BY cn. column_id

Sorry, it should be LEFT JOIN, not INNER.

You cannot have a dynamic number of columns, so this solution works only if you know in advance how many groups you might have.