eternal_noob Posted February 25, 2014 Share Posted February 25, 2014 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 | +------------+--------+-----------------------+ Link to comment https://forums.phpfreaks.com/topic/286516-mysql-query-where-variable-result-from-different-query/ Share on other sites More sharing options...
gristoi Posted February 26, 2014 Share Posted February 26, 2014 nested select or join: nested select: SELECT DISTINCT SysproCompanyJ.dbo.InvMaster.Description, SysproCompanyJ.dbo.InvMaster.LongDesc FROM SysproCompanyJ.dbo.InvMaster WHERE SysproCompanyJ.dbo.InvMaster.StockCode LIKE '%(SELECT substring(invW.StockCode, 1, (len(invW.StockCode) - 1)) as sStockCode, SUM(invW.QtyOnHand - invW.QtyAllocated) 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)%' Link to comment https://forums.phpfreaks.com/topic/286516-mysql-query-where-variable-result-from-different-query/#findComment-1470746 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.