CODEWITHSUNDEEP
phpmysql
Ivan Nikolov
Ivan Nikolov

Reputation: 235

Dynamical column names depending on the single row of the query result

I need some help with dynamical column names in a sql query. First I will try to explain my database structure and then the problem.

Database structure:

admin_group table:

+--------+----------------+
| id     | language_code  |
+--------+----------------+
| 1      | en_UK          | 
| 2      | de_DE          | 
| 3      | es_ES          |
+--------+----------------+

constructions_meta table:

+--------+-----------------+----------+
| id     | admin_group_FK  | value    |
+--------+-----------------+----------+
| 1      | 1               | 0.13     |
| 2      | 2               | 0.12     |
| 3      | 3               | 0.10     |
+--------+-----------------+----------+

construction_lang table:

+--------+-----------------+----------------+----------------+
| id     | en_UK_name      | de_DE_name     |es_ES_name      |
+--------+-----------------+----------------+----------------+
| 1      | Construction 1  | Konstruktion 1 | Construcción 1 |       
| 2      | Construction 2  | Konstruktion 2 | Construcción 2 |  
| 3      | Construction 3  | Konstruktion 3 | Construcción 3 |  
+--------+-----------------+----------------+----------------+

Those are my tables in the database. What I need here is to get the names of the constructions regarding the language code for each construction. For example I want to list the constructions as following:

  1. Construction 1
  2. Konstruktion 2
  3. Construcción 3

Upvotes: 2

Views: 98

Answers (3)

diarmuid
diarmuid

Reputation: 138

select c.id , (select case c.admin_group_FK 
                when 1 then l.en_UK_name 
                when 2 then  l.de_DE_name 
                else l.es_ES_name 
          end from construction_lang l where id = c.id) as construction_name,
    c.value
from constructions_meta c;

Yes the solution is only a get-you-working-quickly one . And Rob is correct about improving the data model

Solution is :

you need to and drop the construction_lang table from your data model and replace it with this association table much like what Rob proposes

+------------------+--------------+----------------+
| construction_fk  | language_fk  |    name        |
+------------------+--------------+----------------+
| 1                | 1            | Construction 1 |
| 1                | 2            | Konstruktion 1 |
| 1                | 3            | Construcción 1 |
| 2                | 1            | Construction 2 |
| 2                | 2            | Konstruktion 2 |
| 2                | 3            | Construcción 2 |
| 3                | 1            | Construction 3 |
| 3                | 2            | Konstruktion 3 |
| 3                | 3            | Construcción 3 |   
+------------------+--------------+----------------+

then query you require is 

select c.id, l.language_code ,  a.name ,c.value
from constructions_meta c
join construction_lang_assoc a on a.construction_fk = c.id and a.language_fk = c.admin_group_FK 
join admin_group l on l.id = c.admin_group_FK;

Upvotes: 3

Rob Baillie
Rob Baillie

Reputation: 3470

It's probably easiest to do that in two passes. That'll give you the ability to expand the width of the construction_lang table without affecting the SQL statements significantly.

Firstly you issue a SQL statement to get the column names that you need, then you use that result set to build a second SQL statement that will get the names for you.

This is not the ideal solution, as it is working around the data-model.

Given a query that returns back the id and language from admin_group in line with:

array( array( 'id' => 1, 'language_code' => 'en_UK' ),
       array( 'id' => 2, 'language_code' => 'de_DE' ),
       array( 'id' => 3, 'language_code' => 'es_ES' ) )

You can build a statement with something along the lines of (using the other answer from diarmuid as an example)

$sql = "select c.id , (select case c.admin_group_FK\r\n";
foreach( $languages as $thisLanguage ) {
   $sql .= "when {$thisLanguage['id']} then l.${thisLanguage['language_code']}_name\r\n";
}
$sql .= ...

This is a round-about way of doing it, because of the data-model you have. Ideally you wouldn't need the "dynamic" SQL."

If you want to remove the round-about-ness, and build something more in line with standard relational database theory, then you can change the model so that the construction_lang table is more like this:

+------------------------+-----------------+----------------+
| constructions_meta_fk  | language        | name           |
+------------------------+-----------------+----------------+
| 1                      | en_UK           | Construction 1 |
| 1                      | de_DE           | Konstruktion 1 |
| 1                      | es_ES           | Construcción 1 |
| 2                      | en_UK           | Construction 2 |
| 2                      | de_DE           | Konstruktion 2 |
| 2                      | es_ES           | Construcción 2 |
| 3                      | en_UK           | Construction 3 |
| 3                      | de_DE           | Konstruktion 3 |
| 3                      | es_ES           | Construcción 3 |
+------------------------+-----------------+----------------+

Upvotes: 2

Vetrivel
Vetrivel

Reputation: 1149

you can use if condtion like `

if(admin_group_FK == 1)
{
fetch['en_UK_name'];
}
if(admin_group_FK == 2)
{
fetch['de_DE_name'];
}
if(admin_group_FK == 3)
{
fetch['es_ES_name'];
}

`

Upvotes: -2

Related Questions