[PHP MySQL] Pivot table opzetten

[Goodfellas]

Ik wil een pivot table opzetten waarmee ik de data uit rijen kan omzetten naar kolommen. De data komt uit formulieren waaraan velden kunnen worden toegevoegd of worden weggehaald. Ik heb een view gemaakt met de volgende (versimpelde) data:

ID	Name	Value
1	Voornaam	Jan
1	Achternaam	Klaasen
1	Geslacht	Man
2	Voornaam	Piet
2	Achternaam	Janssen
2	Gelsacht	Man

Wat ik wil is dit:

ID	Voornaam	Achternaam	Geslacht
1	Jan	Klaasen	Man
2	Piet	Janssen	Man

Ik weet dat het mogelijk moet zijn met PHP maar kom er niet uit. Mijn idee is om de data in een array te zetten op basis van een query.

$result=mysql_query(SELECT * FROM tabelname);

while($row = mysql_fetch_array($result)){
echo $row["ID"] . $row["Name"] .$row["Value"];
}

Dit levert (uiteraard) het resultaat in rijen "id | veldnaam | veldwaarde", maar hoet krijg ik de veldnamen als kolomnaam? Thanks.

[Cartman!]

Als het nog mogelijk is om je datamodel aan te passen zou ik dat doen Anders is het een kwestie van array-gebruik om het om te zetten. Heb je al iets zelf geprobeerd?

[Janoz]

Je hebt 3 mogelijkheden:
1 - Dump het database in database anti-pattern en fatsoeneer je database
2 - Zorg dat je middels een 'order by' een gegarandeerde volgorde krijgt zodat je een fetch per kolom kunt doen
3 - Skip het idee dat je de boel moet printen terwijl je aan het itereren bent door je resultaten en stop alles eerst in een array (met de ID als index) en itereer door die array heen wanneer je klaar bent met fetchen

[Goodfellas]

De database is niet aan te passen, dit heeft te maken met het script dat gebruikt wordt voor het maken en onderhouden vanformulieren. Heb nu het volgende idee:

- De velden die worden ingevuld zijn voor alle gebruikers het zelfe. Ik hoef dus maar van een gebruiker de titels op te halen en deze te printen / in een array te stoppen.

- Daarna kan ik de waardes per id (per rij) er onder printen. De kolom value laat ik hier helemaal buiten beschouwing.

[zhar]

Ik zou de query aanpassen en daar de pivot in plaatsen, zoiets:

select ID,
MAX(Case when Name ='Voornaam' Then Value END CASE) as VoorNaam,
MAX(Case when Name ='Achternaam' Then Value END CASE) as AchterNaam,
MAX(Case when Name ='Geslacht' Then Value END CASE) as Geslacht
FROM tabelname
GROUP BY ID

Werkt natuurlijk enkel maar correct als je niet meerdere voornamen etc hebt per id.

[Wolfboy]

Je kan toch gewoon de tabel met zichzelf joinen en daarbij de extra kolommen ophalen? Dat iljkt mij iig de meest "redelijke" oplossing

[Goodfellas]

Bedankt voor jullie reacties tot zo ver. Nogmaals: het eav model is niet te normaliseren en het script kan niet op basis van een statische sql query worden gemaakt.

Ik wil de fieldvalues onder fieldnames krijgen, daarom zet ik eerst de fieldnames om in table headers. Daarna wil ik per ingevoerde set gegevens (dus per gebuiker) een rij met gegevens.

PHP:

$sql1="SELECT DISTINCT FieldName FROM overzicht WHERE FormId = '$FormId'";
$sql2="SELECT * FROM overzicht WHERE FormId = '$FormId'";

$result1=mysql_query($sql1);
$result2=mysql_query($sql2);
    
//Make arrays
$headers_arr = array();
$values_arr = array();

//Fill header array
while($row = mysql_fetch_array($result1)) {
    $headers_arr[] = $row["FieldName"];
}

//Fill values array and row counter
$num_rows=0;
while($row = mysql_fetch_array($result2)) {  
    $values_arr[] = array ($row["UserId"], $row["FieldValue"]);
    $num_rows++;
}
    
//Create table
echo "<table border=\"1\"><tr>";
foreach ($headers_arr as $header) {
    echo "<th>" . $header . "</th>";
}
echo "</tr>";

//values
echo "<tr>";
$i=0;
while ($i <= $num_rows) {
    foreach($values_arr as $value) {        
        if ($value[0] == $i ) {
            echo "<td>" . $value[1] . "</td>";
        } else {
            echo "</tr><tr/>";
        }
    }
    $i++;
}

echo "</tr>";
//Close table
echo "</table>";

}

Het gaat dus om de laatste loop. Voor alle gegevens uit de array gaat hij kijken of het userid (values[0]) overeenkomt met de counter (i). Zo ja dan print hij alle waarden van die gebruikers op een rij in kolommen. Zo nee, dan gaat hij door en print hij een volgende rij. Dit levert - ontzettend - veel lege rijen op maar ik heb nog geen manier gevonden om het anders te doen.

Hoe krijg het zelfde voor elkaar zonder deze hoeveelheid witte rijen? Maw hoe moet ik deze loop aanpassen?

ter illusteratie het resultaat:

Voornaam	Achternaam	Geslacht
leeg	leeg	leeg
leeg	leeg	leeg
Jan	Klaasen	Man
leeg	leeg	leeg
leeg	leeg	leeg
Piet	Janssen	Man

[ Voor 12% gewijzigd door Goodfellas op 19-05-2010 13:22 ]

[Janoz]

Je doet veel te moeilijk. Bedenk dat een array in php eigenlijk geen array, maar een map is. Bedenk daarnaast dat je in een array element ook weer een array kunt zetten.

Kijk eens naar de volgende functie:

PHP:

function ($results , $id, $label, $value) {
  if (!isset($results[$id]) {
    $results[$id] = array();
  }
  $results[$id] [$label] = $value;
}

[Goodfellas]

Ik snap wat die functie doet maar hoe zou je dit dan gebruiken in bovenstaand voorbeeld?

[Janoz]

Gebruiken in dit stuk:

PHP:

//Fill values array and row counter
$num_rows=0;
while($row = mysql_fetch_array($result2)) { 
$values_arr[] = array ($row["UserId"], $row["FieldValue"]);
$num_rows++;
}

[Goodfellas]

Haha, je gaat het mijn niet makkelijk maken he?

De array ziet er nu zo uit:

PHP:

//Fill values array and row counter
    while($row = mysql_fetch_array($result2)) { 
        if (!isset ($values_arr[$row["UserId"]])) {
            $values_arr[$row["UserId"]] = array ($row["FieldName"], "<td>" . $row["FieldValue"] . "</td>"); 
        }
    }

De uitvoer doe ik nu zo:

PHP:

foreach ($values_arr as $value) {
        echo "<tr>" . $value[1] . "</tr>";
    }

De rijen gaan nu goed, nu krijg ik alleen maar een kolom. Wat doe ik fout?

Edit: ik zie al wat er fout gaat, alleen het eerste element wordt opgslagen. Daarna is de array met deze key al gezet. Moet dan de gegevens aan deze array met deze key toevoegen toch?

Resultaat:

Voornaam	Achternaam	Geslacht
Jan	leeg	leeg
Piet	leeg	leeg

[ Voor 30% gewijzigd door Goodfellas op 19-05-2010 13:30 ]

[Goodfellas]

Gelukt, Janoz erg bedankt voor je hulp!

PHP:

//Fill values array and row counter
while($row = mysql_fetch_array($result2)) { 
        if (!isset ($values_arr[$row["UserId"]])) {
            $values_arr[$row["UserId"]] = array ($row["FieldName"], "<td>" . $row["FieldValue"] . "</td>"); 
        } else {
            $values_arr[$row["UserId"]][1] = $values_arr[$row["UserId"]][1] . "<td>" . $row["FieldValue"] . "</td>";
            $values_arr[$row["UserId"]][0] = $values_arr[$row["UserId"]][0] . "<td>" . $row["FieldName"] . "</td>";
        }
    }

[ Voor 16% gewijzigd door Goodfellas op 19-05-2010 15:05 ]