嵌套while循环中的SQL语句

Question

我正在为我们的IT部门写一份盘点申请，我不确定我要走的路是最好的。

我已经创建了一个' productgroup‘表和一个' products’表，这两个表与一个ID (一个产品组对应多个产品)产品组相链接，例如LaserJet Pro400，products将是单独的耗材。黑色、品红色、青色等。

因此，我有一个用于显示组的while循环，然后是一个用于显示该组中的产品的嵌套while循环。

我担心的是有很多sql语句在快速运行，在早期阶段似乎没有性能问题，但我不确定……这可以接受吗？有没有更好的方法？

foreach ($_POST['BeginLocation'] as $key => $value) {$LocationID = $key;}

echo '<div class="nmform"><form name="StockTake" action="index.php" method="post" enctype="multipart/form-data">';

include "../DBCon/RDPNearMisses.php";

$GetProductGroups = sqlsrv_query($NMDB, "select distinct PD.ProductGroupID, PG.GroupName
                                        from [JobObservations].[dbo].[ITStk.Products] as PD
                                        inner join [JobObservations].[dbo].[ITStk.ProductGroups] as PG on PD.ProductGroupID = PG.ProductGroupID
                                        where PD.LocationID = $LocationID");

                       if( $GetProductGroups === false ) {
                           if( ($errors = sqlsrv_errors() ) != null) {
                               foreach( $errors as $error ) {
                                   echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
                                   echo "code: ".$error[ 'code']."<br />";
                                   echo "message: ".$error[ 'message']."<br />";
                               }
                           }
                       }

                       while ($row = sqlsrv_fetch_array($GetProductGroups)) {echo '<h4>'.$row['GroupName'].'</h4>';
                                                $ProductGroupID = $row['ProductGroupID'];

                                                $GetProducts = sqlsrv_query($NMDB, "select PD.ProductID, PD.ProductGroupID, PD.ProductCode, PD.ProductDescription
                                                from [JobObservations].[dbo].[ITStk.Products] as PD
                                                inner join [JobObservations].[dbo].[ITStk.ProductGroups] as PG on PD.ProductGroupID = PG.ProductGroupID
                                                inner join [JobObservations].[dbo].[ITStk.Locations] as LC on PD.LocationID = LC.LocationID
                                                where PD.LocationID = $LocationID
                                                and PD.ProductGroupID = $ProductGroupID
                                                order by LC.LocationDescription asc, PG.GroupName asc, PD.ProductCode asc");

                                                if( $GetProducts === false ) {
                                                    if( ($errors = sqlsrv_errors() ) != null) {
                                                        foreach( $errors as $error ) {
                                                            echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
                                                            echo "code: ".$error[ 'code']."<br />";
                                                            echo "message: ".$error[ 'message']."<br />";
                                                        }
                                                    }
                                                }
                                                echo '<table><th>Code</th><th>Description</th><th>Qty</th>';
                                                while ($row1= sqlsrv_fetch_array($GetProducts)) {echo '<tr><td>'.$row1['ProductCode'].'</td><td>'.$row1['ProductDescription'].'</td><td><select name="'.$row1['ProductID'].'"><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option></select></td></tr>';}
                                                echo '</table>';


                       }
                       echo '<input type="Submit" name="SubmitStock"></form>';
                       sqlsrv_close($NMDB);
         echo '</div>';

Answer 1

虽然您的数据很小，每个位置的组数量也很少，但您可能不会注意到差异，但随着每个位置的组数量的变化，您可能会考虑切换到1个查询和一个While循环：

SELECT DISTINCT
    PD.ProductID,
    PG.GroupName,
    PD.ProductGroupID,  
    PD.ProductCode,
    PD.ProductDescription
FROM [JobObservations].[dbo].[ITStk.Products] AS PD
INNER JOIN [JobObservations].[dbo].[ITStk.ProductGroups] AS PG ON PD.ProductGroupID = PG.ProductGroupID
INNER JOIN [JobObservations].[dbo].[ITStk.Locations] AS LC ON PD.LocationID = LC.LocationID
WHERE PD.LocationID = $LOCATIONID;

可以很好地工作，因为它会得到一个记录集，其中包含未使用的GroupName以及表中正在使用的Product ID和Product。

此外，除非只是为了限制LocationID实际在Locations表中的记录而联接表ITStk.Locations，否则不需要在这里联接它。您不需要使用任何字段来限制结果集或SELECT。

几乎任何时候，当您发现自己抓取了一个记录集，并且在读取/循环记录集时发出了更多的SQL，您都可以将其转换为一条SQL语句。

为了让你的GroupName作为你的表的头，在你的1while循环中，你可以这样做(原谅我，我写PHP已经有几年了)：

$GetProductGroups = sqlsrv_query($NMDB, "SELECT DISTINCT
                                        PD.ProductID,
                                        PG.GroupName,
                                        PD.ProductGroupID,  
                                        PD.ProductCode,
                                        PD.ProductDescription
                                    FROM [JobObservations].[dbo].[ITStk.Products] AS PD
                                    INNER JOIN [JobObservations].[dbo].[ITStk.ProductGroups] AS PG ON PD.ProductGroupID = PG.ProductGroupID
                                    INNER JOIN [JobObservations].[dbo].[ITStk.Locations] AS LC ON PD.LocationID = LC.LocationID
                                    WHERE PD.LocationID = $LOCATIONID
                                    ORDER BY GroupName;");

if( $GetProductGroups === false ) {
   if( ($errors = sqlsrv_errors() ) != null) {
       foreach( $errors as $error ) {
           echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
           echo "code: ".$error[ 'code']."<br />";
           echo "message: ".$error[ 'message']."<br />";
       }
   }
}

while ($row = sqlsrv_fetch_array($GetProductGroups)) {

    /*If the groupname of the current record is different then the last record's group name (notice order by on query) then echo out the header and start a new table*/
    if ( $groupname != $row['GroupName']) {
        echo '<h4>'.$row['GroupName'].'</h4>';
        echo '<table><th>Code</th><th>Description</th><th>Qty</th>';
    }

    /*echo out the row into the table*/
    echo '<tr><td>'.$row['ProductCode'].'</td><td>'.$row['ProductDescription'].'</td><td><select name="'.$row1['ProductID'].'"><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option><option>0</option></select></td></tr>';


    /*again if we started a new table because the groupname is new in the recordset, then close the table*/
    if ( $groupname != $row['GroupName']) {         
        echo '</table>';
    }
    /*Capture group name for future iterations*/    
    $groupname = $row['GroupName'];

}

echo '<input type="Submit" name="SubmitStock"></form>';
sqlsrv_close($NMDB);
echo '</div>';