eternal_noob

Table: SysproCompanyJ.dbo.InvWarehouse +--------------+-------------+----------------+ | Stock Code | QtyOnHand | QtyAllocated | +--------------+-------------+----------------+ | C-100-01A | 100 | 200 | | C-100-01B | 0 | 100 | | C-200-00A | 45 | 165 | | C-300-02C | 60 | 15 | | C-300-02D | 17 | 20 | +--------------+-------------+----------------+ Table: SysproCompanyJ.dbo.InvMaster +--------------+---------------+------------+ | Stock Code | Description | LongDesc | +--------------+---------------+------------+ | C-100-01A | Widget | Bright Red | | C-100-01B | Widget, Red | | | C-200-00A | Shiny Widget | Blue | | C-300-02C | Fancy Widget | Silver | | C-300-02D | Fancy Widget | Silver | +--------------+-------------+--------------+ Current Code: <?php $StockCodeA = 'C-0300-02'; $conn=odbc_connect('syspro','',''); if (!$conn) {exit("Connection Failed: " . odbc_error());} $sql= "SELECT substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) as sStockCode, SUM(invW.QtyOnHand - invW.QtyAllocated) AS Value FROM SysproCompanyJ.dbo.InvWarehouse as invW WHERE Warehouse = 'SW' GROUP BY substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) HAVING SUM(invW.QtyOnHand - invW.QtyAllocated) > 0"; $sql2= "SELECT DISTINCT SysproCompanyJ.dbo.InvMaster.Description, SysproCompanyJ.dbo.InvMaster.LongDesc FROM SysproCompanyJ.dbo.InvMaster WHERE SysproCompanyJ.dbo.InvMaster.StockCode LIKE '%{$StockCodeA}%' "; $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} $rs2=odbc_exec($conn,$sql2); if (!$rs2) {exit("Error in SQL");} echo "<table class='sortable' border='1'> <tr> <th>PN</th> <th>QTY</th> <th>DESCRIPTION</th> </tr>"; while ($row = odbc_fetch_array($rs)) { $Description=odbc_result($rs2,"Description"); $LongDesc = odbc_result($rs2,"LongDesc"); echo "<tr> <td><center>{$row['sStockCode']}</center></td> <td><center>".number_format($row['Value'], 0)."</center></td> <td><center>{$Description}{$LongDesc}</center></td> </tr> "; } odbc_close($conn); echo "</table>"; ?> I received help yesterday from this forum to define $sql. In doing so some previous functionality no longer works.I need to output the description as part of the table and I'm not entirely sure on the way to do this. I essentially need to execute what's currently in $sql, use sStockCode as the WHERE in $sql2 to then pull the first description it comes to in the table. I'm not sure how I set the variable to then be able to use in the second query or if I can somehow do it all within $sql. Desired Output: +------------+--------+-----------------------+ | PN | QTY | DESCRIPTION | +------------+--------+-----------------------+ | C-100-01 | -200 | Widget Bright Red | | C-200-00 | -120 | Shiny Widget Blue | | C-300-02 | 42 | Fancy Widget Silver | +------------+--------+-----------------------+

Current Query: $sql= "SELECT SysproCompanyJ.dbo.InvWarehouse.StockCode, SUM(QtyOnHand - QtyAllocated) AS 'Value' FROM SysproCompanyJ.dbo.InvWarehouse WHERE SysproCompanyJ.dbo.InvWarehouse.QtyOnHand > '0' And Warehouse = 'SW' GROUP BY StockCode HAVING SUM(QtyOnHand - QtyAllocated) > 0"; Example of current output (If I were to exclude the Having Sum condition): +--------------+-------+ | Stock Code | Value | +--------------+-------+ | C-100-01A | 100 | | C-100-01B | -90 | | C-200-00A | 145 | | C-300-02C | -14 | | C-300-02D | 17 | +--------------+-------+ What I need (cut off the last character of the stock code and sum like stock codes) : +-------------+-------+ | Stock Code | Value | +-------------+-------+ | C-100-01 | 10 | | C-200-00 | 145 | | C-300-02 | 3 | +-------------+-------+

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?

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.