CODEWITHSUNDEEP
phpsql-server
David H
David H

Reputation: 71

SQL Statement within nested while loops

I'm writing up a stock take application for our IT department and I'm not confident the way I'm going is the best.

I have created a 'productgroup' table and a 'products' table that are linked with an ID (one productgroup to many products) product group for example LaserJet Pro 400 and products would be the individual consumables.. black, magenta, cyan etc.

So what is happening is I have a while loop for displayting the groups and then a nested while loop for displaying the products within that group.

What I was worried about is it being a lot of sql statements in rapid fire, there doesn't seem to be an issue with performance at this early stage but I'm unsure.. is this acceptable? is there a better way?

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>';

Upvotes: 0

Views: 301

Answers (1)

JNevill
JNevill

Reputation: 50273

While your data is small and the number of groups per location is small, you probably won't notice a difference, but as the number of groups per location changes you may want to consider switching to 1 query and a while loop:

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;

Would work well since it gets you a record set containing your unused GroupName and the Product ID and Produce Description that you are using in your table.

Furthermore unless the table ITStk.Locations is being joined in merely to limit records where a LocationID is actually in the Locations table, then there is no need for it to be joined here. You don't use any of the fields to limit result sets or SELECT.

Nearly any time you find yourself grabbing a recordset and while reading/looping the record set you are issuing more SQL, you can turn that into a single SQL statement.

To get your GroupName as a header over your tables, in your 1 while loop you could do something like the following (Forgive me, it's been a few years since I wrote 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>';

Upvotes: 1

Related Questions