0

I have an sql table looking like this

---------------------------------
| id | di  | field  |  content  |
---------------------------------
| 1  | kh  |  dvh   | lorem ips |
---------------------------------
| 2  | kh  |  kkf   | hor ameti |
---------------------------------
| 3  | db  |  hgd   | usyytutt  |
---------------------------------

What I would like is a way to select from sql and return the variables where field becomes the column name; like so: 

$sql = $conn->prepare('SELECT * FROM table WHERE `di` = "kh"'); // And some more fancy code
$sql = execute();
$row = $sql->fetch(PDO::FETCH_ASSOC);

echo $row['dvh'];

// outputs 'lorem ips'

Currently I am using php to convert the returned array by using:

$result = $sql->fetchAll(PDO::FETCH_ASSOC);

and then running them through a foreach loop:

$data = array();

foreach ($result as $k => $val) {
    $data[$val['field']] = $data['content'];
}

I really want SQL to do this for me.

Corfitz.
  • 1,864
  • 3
  • 31
  • 53
  • 2
    This is called a dynamic pivot. Google "MySQL dynamic pivot". – Gordon Linoff Jul 12 '14 at 13:43
  • @PhilipJensBramsted Why? :-( – Strawberry Jul 12 '14 at 13:51
  • @Strawberry I am listing some products in a table but instead of having a column for each possible input field or information, I am using this method to store multiple values for an id. This also ensures that I don't end up with a lot of empty fields, when only a few products require a specific information that none of the others require. – Corfitz. Jul 12 '14 at 14:11
  • @DaveSalomon I would say that your reference actually looks like what I need, but I didn't know how to search my way into that answer.. i will take a look at it thanks.. – Corfitz. Jul 12 '14 at 14:12
  • @GordonLinoff It doesn't seem like MySQL has a pivot function.. I should go for the GROUP_CONCAT() function – Corfitz. Jul 12 '14 at 14:12
  • 1
    Just stick with post-processing the result in PHP. It's way more scalable AND flexible! – Strawberry Jul 12 '14 at 14:19

0 Answers0