We help IT Professionals succeed at work.

PHP & MSSQL - Displaying new data, based on data from a previous table / query

Bergstr
Bergstr used Ask the Experts™
on
Im currently trying to query a table, and with the results from the first table, run a new query on the second table - the way im trying to do it is:

At the top of the page i have the following two queries:

 
$query1 = mssql_query("
SELECT	 StockCode
	 ,Description
	 ,QtyToMake
FROM MRP”);

$query2 = mssql_query("
SELECT  	 ProductID
	 ,ComponentID
	 ,PerAssyQty
FROM BOM
WHERE ProductID='$MRPDATA[StockCode]'");

Open in new window


and then further down the page i have the following to display the data:

 
<?php
while($MRPDATA = mssql_fetch_array($query1))
	{
	while($BOM = mssql_fetch_array($query2))
 		{

  			echo "<tr>";
  			echo "<td>" . $BOM['ProductID'] . "</td>";
 			echo "<td>" . $BOM['ComponentID'] . "</td>";
 			echo "<td>" . $BOM['PerAssyQty'] . "</td>";
 			echo "</tr>";
  		}
	}
 ?>

Open in new window


The problem i have is that query2 in the first section of code wont work with the '$MRPDATA[StockCode]' included - if i replace that with a static part number it works fine...

Anyone know how i can get the variable to work within the query?

Thanks :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You are probably better off just making a join, and have the DB do all the work instead of recursively quering the DB, so I'd recommend you do this


$query1 = mssql_query("
SELECT       m.StockCode
       ,m.Description
       ,m.QtyToMake
         ,b.ProductID
       ,b.ComponentID
       ,b.PerAssyQty
FROM MRP AS m
JOIN BOM AS b
WERE b.ProductID=m.StockCode");

However, if you really don't like that idea....


$query1 = mssql_query("
SELECT       StockCode
       ,Description
       ,QtyToMake
FROM MRP”);

$query2 = "
SELECT         ProductID
       ,ComponentID
       ,PerAssyQty
FROM BOM
WHERE ProductID=";


and your loop would look like this


<?php
while($MRPDATA = mssql_fetch_array($query1))
      {
      while($BOM = mssql_fetch_array(mssql_query($query2 . "'" . $MRPDATA['StockCode'] . "'"))
             {

                    echo "<tr>";
                    echo "<td>" . $BOM['ProductID'] . "</td>";
                   echo "<td>" . $BOM['ComponentID'] . "</td>";
                   echo "<td>" . $BOM['PerAssyQty'] . "</td>";
                   echo "</tr>";
              }
      }
 ?>
<?php

$query1 = mssql_query("
SELECT       StockCode
       ,Description
       ,QtyToMake
FROM MRP”);


$i=0;
$num = mssql_num_rows($query1);

while($i<=$num)
{
 $data = mssql_fetch_array($query1);
 if($i==0)
 {
  $StkCode = "'".$data['StockCode']."'";
  }
 else
 {
  $StkCode = $StkCode.","."'".$data['StockCode']."'";
  }
 $i++;
 }


$query2 = mssql_query("
SELECT         ProductID
       ,ComponentID
       ,PerAssyQty
FROM BOM
WHERE ProductID in $stkCode");



while($BOM = mssql_fetch_array($query2))
      {

                    echo "<tr>";
                    echo "<td>" . $BOM['ProductID'] . "</td>";
                   echo "<td>" . $BOM['ComponentID'] . "</td>";
                   echo "<td>" . $BOM['PerAssyQty'] . "</td>";
                   echo "</tr>";
            }
?>
$query = mssql_query("select b.ProductID,b.ComponentID,b.PerAssyQty from MRP a, BOM b
where a.StockCode=b.ProductID");

      while($BOM = mssql_fetch_array($query))
             {

                    echo "<tr>";
                    echo "<td>" . $BOM['ProductID'] . "</td>";
                   echo "<td>" . $BOM['ComponentID'] . "</td>";
                   echo "<td>" . $BOM['PerAssyQty'] . "</td>";
                   echo "</tr>";
              }