mySQL query to dynamically convert column data to column name

Question

I am trying to create a 'run sheet' of doughnut orders from a ZenCart database. I read a dozen posted questions to get to [Jeff Lee's response to question]: SQL query to rebuild a table using its dynamic row data for column names. Since I am using MySQL, I think I need to generate a dynamic query because my 'ProductName' data changes.

I have written the following code which generates an HTML table similar in structure to the example database table but I do not know how to incorporate his solution into my code as I am not starting with a database table but rather a query result-set.

I am going to try creating a database table from my existing query in order to use Jeff's solution but I think the preferred method would be to incorporate his code into my query, I just don't know how.

I am using PHP/5.6.11 and MySQL/5.6.25

<?php 
    echo 'Attempt connection to the [donutsdb] database';
    print '<br />';
    require("includes/connect2donutsdb.php");
?>

<?php
//this script will query the 'orders' and 'orders_products' tables
//and combine the information based on the 'orders_id' field
//I added aliases to improve the query readability
try {
    echo '[donutsdb]';
    print '<br />';
    $query = "
    SELECT
        o.orders_id AS 'O_OID', o.customers_name AS 'O_CName',
        p.products_name AS 'P_PName', p.products_quantity AS 'P_PQuant'
    FROM
        orders AS o
    INNER JOIN orders_products AS p
    ON o.orders_id=p.orders_id
    ";
    //first pass just gets the column names
    print "<table> \n";
    $result = $dbh->query($query);
    //return only the first row (we only need field names)
    $row = $result->fetch(PDO::FETCH_ASSOC);
    print " <tr> \n";
    foreach ($row as $field => $value){
        print " <th>$field</th> \n";
    } // end foreach
    print " </tr> \n";
    //second query gets the data
    $data = $dbh->query($query);
    $data->setFetchMode(PDO::FETCH_ASSOC);
    foreach($data as $row){
        print " <tr> \n";
        foreach ($row as $name=>$value){
            print " <td>$value</td> \n";
        } // end field loop
        print " </tr> \n";
    } // end record loop
    print "</table> \n";
print '<br />';
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
} // end try
?>

My code generates an HTML table with the following structure:

 O_OID | O_CName | P_PName            | P_PQuant 
 1     |  mark   |  Glazed            |  12 
 2     |  John   |  Glazed            |  8 
 2     |  John   |  Bavarian Cream    |  4 
 3     |  mark   |  Chocolate Dipped  |  12 
 4     |  Kevin  |  Donut Holes       |  10 
 5     |  Kevin  |  Peanut Butter     |  1 
 5     |  Kevin  |  Blueberry         |  2

What I am trying to achieve is:

O_OID | O_CName | Glazed | Bavarian Cream | Chocolate Dipped | Donut Holes | Peanut Butter | Blueberry
1     |  mark   |  12    |  0             |  0               |  0          |  0            |  0
2     |  John   |  8     |  4             |  0               |  0          |  0            |  0
3     |  mark   |  0     |  0             |  12              |  0          |  0            |  0
4     |  Kevin  |  0     |  0             |  0               |  10         |  1            |  2

You need a crosstab query. Maybe this tutorial will help: http://www.databasejournal.com/features/mysql/article.php/3855376/All-About-the-Crosstab-Query.htm — , Dec 17 '15 at 23:48
@Jeff, Thank you for the feedback. I'm checking out the article now. However, I think I'm going to need to use his second article [MySQL Prepared Statements to Generate Crosstab SQL](http://www.databasejournal.com/features/mysql/article.php/3871556/MySQL-Prepared-Statements-to-Generate-Crosstab-SQL.htm) as the number of columns will need to be dynamic. — mrobbins, Dec 18 '15 at 22:39
better do this transformation in PHP instead of SQL. In simple words, fetch the rows needed and THEN use php to transform these rows into the format needed for your table , ie each row value transformed into a different column. It is way more efficient — Nikos M., Dec 19 '15 at 11:58
Nikos, I've written a query that returns the information I need to do the transformation but I'm not sure how to use PHP to transform the rows into column headers. I've been studying [the question](http://stackoverflow.com/questions/14834290/mysql-query-to-dynamically-convert-rows-to-columns) as his solution for multiple values seems to be what I'm looking for but I don't know how to get his query to output to HTML or how to use PHP on my query result-set to display the information on an HTML page. — mrobbins, Dec 31 '15 at 15:39

mySQL query to dynamically convert column data to column name

0 Answers0