Dynamic pivot with mysql data.

[eternal_noob]

I'm struggling to implement the various pivoting methods I have found around the net with my specific query. I am completely new to database interations and almost as new with php. I was able to get some of the tutorials to work (using their database entries) when using the SQL query string in phpMyAdmin but am not sure how to turn that into php that I can execute in my browswer to get the same results.

 

My current code showing my currenty query string:

<?php
$ms = microtime(true);
$conn=odbc_connect('syspro','','');
if (!$conn)
  {exit("Connection Failed: " . odbc_error());}
$sql=
    "SELECT SysproCompanyJ.dbo.SorMaster.SalesOrder,
    SysproCompanyJ.dbo.SorMaster.OrderStatus,
    SysproCompanyJ.dbo.SorMaster.Branch,
    SysproCompanyJ.dbo.SorMaster.ReqShipDate,
    SysproCompanyJ.dbo.SorDetail.MStockCode,
    SysproCompanyJ.dbo.SorDetail.MBackOrderQty
    
    FROM SysproCompanyJ.dbo.SorMaster,
         SysproCompanyJ.dbo.SorDetail
    
    WHERE    SysproCompanyJ.dbo.SorMaster.SalesOrder = SysproCompanyJ.dbo.SorDetail.SalesOrder
        AND SysproCompanyJ.dbo.SorDetail.MStockCode LIKE 'CB%'
        AND SysproCompanyJ.dbo.SorMaster.CustomerPoNumber NOT LIKE 'RMA_'
        AND SysproCompanyJ.dbo.SorDetail.MBackOrderQty > '0'    
        AND (SysproCompanyJ.dbo.SorMaster.Branch = '60'
            OR SysproCompanyJ.dbo.SorMaster.Branch = '70')
        AND (SysproCompanyJ.dbo.SorMaster.OrderStatus = '1'
            OR SysproCompanyJ.dbo.SorMaster.OrderStatus = 'S'
            OR SysproCompanyJ.dbo.SorMaster.OrderStatus = 'F')        
        ";
    
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}

echo "<table class='sortable' border='1'>
<tr>
<th>Stock Code</th>
<th>Back Order Qty</th>
<th>Scheduled Ship Date</th>
</tr>";

while (odbc_fetch_row($rs))
  {
  $MStockCode=odbc_result($rs,"MStockCode");
  $MBackOrderQty=odbc_result($rs,"MBackOrderQty");
  $ReqShipDate=odbc_result($rs,"ReqShipDate");
    
  echo "<tr><td><center>$MStockCode</center></td>";
  echo "<td><center>".number_format($MBackOrderQty, 0)."</center></td>";
  echo "<td><center>".substr($ReqShipDate,0,10)."</center></td></tr>";

  /*
  echo "<tr><td><center>".odbc_result($rs,'SalesOrder')."</center></td>";
  echo "<td><center>".odbc_result($rs,'MStockCode')."</center></td>";
  echo "<td><center>".odbc_result($rs,'MStockDes')."</center></td>";
  echo "<td><center>".odbc_result($rs,'MBackOrderQty')."</center></td>";
  echo "<td><center>".substr(odbc_result($rs,"OrderDate"),0,10)."</center></td>";
  echo "<td><center>".substr(odbc_result($rs,"ReqShipDate"),0,10)."</center></td></tr>";
*/  

  }

odbc_close($conn);
echo "</table>";
$ms = microtime(true) - $ms;
echo $ms.' secs'; //seconds
echo "<br>";
echo ($ms * 1000).' millisecs'; //millseconds
echo "<br>";
echo "Memory Usage: " . (memory_get_usage()/1048576) . " MB \n";
?>

What I'm attempting to do is dynamicly pull the ReqShipDate for the column headings and then sum MBackOrderQty for each MStockCode at each change in ReqShipDate.

 

 

Example of current output:

+------------+----------------+---------------------+
| Stock Code | Back Order Qty | Scheduled Ship Date |
+------------+----------------+---------------------+
|   C-100    |       5        |       01/16/14      |
|   C-200    |       1        |       01/16/14      |
|   C-300    |       3        |       01/16/14      |
|   C-100    |       6        |       01/16/14      |
|   C-200    |       2        |       01/16/14      |
|   C-500    |       4        |       01/17/14      |
|   C-100    |       1        |       01/17/14      |
|   C-100    |       5        |       01/17/14      |
|   C-200    |       1        |       01/20/14      |
|   C-300    |       3        |       01/20/14      |
|   C-100    |       6        |       01/20/14      |
+------------+----------------+---------------------+

The desired output would be something like:

+-------+----------+----------+----------+
|       | 01/16/14 | 01/17/14 | 01/20/14 |
+-------+----------+----------+----------+
| C-100 |    11    |     6    |     6    |
| C-200 |     3    |          |     1    |
| C-300 |     3    |          |     3    |
| C-500 |          |     4    |          |
+-------+----------+----------+----------+

Any help is greatly appreciated or some more in depth tutotrials on the subject that implement it in php so I can view the full source is also a big help.

[Barand]

The output only requires the stock_code, ship-date and qty so those are the only fileds you need to select. You can then do a GROUP BY stock_code and date to get the totals.

SELECT ,
    SysproCompanyJ.dbo.SorMaster.ReqShipDate,
    SysproCompanyJ.dbo.SorDetail.MStockCode,
    SUM(SysproCompanyJ.dbo.SorDetail.MBackOrderQty) as total
FROM ....
WHERE ...
GROUP BY
    SysproCompanyJ.dbo.SorDetail.MStockCode,SysproCompanyJ.dbo.SorMaster.ReqShipDate

The method to produce the table ouput would be the same as this reply earlier

http://forums.phpfreaks.com/topic/285398-pivot-table-or-cross-tab-in-php-using-mysql-for-attendance/?do=findComment&comment=1465388

Edited January 16, 2014 by Barand

[eternal_noob]

The output only requires the stock_code, ship-date and qty so those are the only fileds you need to select. You can then do a GROUP BY stock_code and date to get the totals.

SELECT ,
    SysproCompanyJ.dbo.SorMaster.ReqShipDate,
    SysproCompanyJ.dbo.SorDetail.MStockCode,
    SUM(SysproCompanyJ.dbo.SorDetail.MBackOrderQty) as total
FROM ....
WHERE ...
GROUP BY
    SysproCompanyJ.dbo.SorDetail.MStockCode,SysproCompanyJ.dbo.SorMaster.ReqShipDate

The method to produce the table ouput would be the same as this reply earlier

http://forums.phpfreaks.com/topic/285398-pivot-table-or-cross-tab-in-php-using-mysql-for-attendance/?do=findComment&comment=1465388

 

Thanks for the heads up on there being no need to select some of the fields. For some reason I thought I needed to select them if I intended to use them in the Where portion of the query string. I "mispoke" in the tytle stating this was a mysql question when in facts I must connect to my database using odbc. Does this change your solution much? I can't seem to get very far when trying to implement the code from the link provided. Wonder if I need to repost with correct wording in title since I don't see where I can modify that?

[Barand]

 use the equivalent odbc functions (odbc_exec(), odbc_fetch_row() etc).

The first thing to do is get the dates that you want to show on the report. These have two purposes:

1. the headings for the report
2. keys for an array so you can store the totals for each stock code in the array
 

Loop through the query results
    Test for when the stock code changes and output the stored array for the last code.
    Reset the array and the previous code value.

    Store the total in the array for this stock code/date
end loop

Output the array that is still stored for the last code