PHP 1st while loop's output result as 2nd while loop's variable for query

Question

Question 1: Is it possible to feed 1st while loop's output result as 2nd while loop's variable for query?

I have mySQL table "reorder_in_process" below:

(`id`, `item`, `to_order`, `cat_no`, `supplier`) VALUES
(48, 'Petri Dish', 62, 1006, 'Progressive'),
(47, 'Beaker', 46, 1005, 'Progressive'),
(46, 'Tissue', 17, 1008, 'Needpoint'),
(45, 'Pipet', 77, 1004, 'Kumpulan');

And my php below:

<?PHP
include ('db.php');
$sql4 = "SELECT DISTINCT supplier FROM reorder_in_process";
$result4 = mysqli_query($con,$sql4);
$supplier4 = $row4['supplier'];
$i=0;

while($row4 = mysqli_fetch_array($result4)){
echo "<h4>".$row4['supplier']."</h4>";

echo  "<div><table><tr>
                        <th>No</th>
                        <th>Item</th>
                        <th>Cat. No</th>                                            
                        <th>Buy QTY</th>
                        <th>Supplier</th>                   
                    </tr>";
$sql5 = "SELECT * FROM reorder_in_process WHERE supplier=$supplier4";
$result5 = mysqli_query($con,$sql5);
while($row5 = mysqli_fetch_array($result5)) {
$i++;
    echo "<tr>
            <td>".$i. "</td>
           <td>".$row5['item']."</td>
           <td>".$row5['cat_no']."</td>
           <td>".$row5['to_order']."</td>
           <td>".$row5['supplier']."</td>      
     </tr>";
}
echo "</table></div>";
}
mysqli_close($con);
?>

I doesn't work...I want the output result like below:

Progressive
No  Item    Cat. No Buy QTY Supplier
1   Petri Dish  1006    62  Progressive
2   Beaker      1005    46  Progressive

Needpoint
No  Item    Cat. No Buy QTY Supplier
1   Tissue  1008    17  Needpoint

Kumpulan
No  Item    Cat. No Buy QTY Supplier
1   Pipet   1004    77  Kumpulan

Pls help to find my mistake and solution to it. Thanks.

string should be enclosed by single quotes "...supplier='$supplier4'" — JYoThI
– JYoThI, Commented Jun 8, 2017 at 11:26
you need to pass the each supplier change the query like this "...supplier='$row4['supplier']'" @p. Lau — JYoThI
– JYoThI, Commented Jun 8, 2017 at 11:41

Qirel · Accepted Answer · 2017-06-08 11:59:32Z

1

You don't need to run two queries like that.

Instead of performing one query, just to query the same table again inside the loop, modify your structure such that you just query the table once. Just ORDER BY supplier and check if the current rows supplier differs from the last rows supplier. (Disclosure: I haven't fully tested this logic, it might need some tweaking).

$result = mysqli_query($con, "SELECT * FROM reorder_in_process ORDER BY supplier");
$first_iteration = true;
$current_supplier = null;

while($row = mysqli_fetch_assoc($result)){
    // If a new supplier is encountered, close the first table (if its no the first iteration) and add new header + start new table 
    if ($row['supplier'] != $current_supplier) {
        if ($first_iteration == false)
            echo "</table></div>";

        $i = 1;
        $first_iteration = false;
        $current_supplier = $row['supplier'];
        echo "<h4>".$row['supplier']."</h4>";
        echo  "<div><table><tr>
                    <th>No</th>
                    <th>Item</th>
                    <th>Cat. No</th>                                            
                    <th>Buy QTY</th>
                    <th>Supplier</th>                   
                </tr>";
    }

    echo "<tr>
                <td>".$i. "</td>
               <td>".$row['item']."</td>
               <td>".$row['cat_no']."</td>
               <td>".$row['to_order']."</td>
               <td>".$row['supplier']."</td>      
         </tr>";
    $i++;
}
echo "</table></div>";

answered Jun 8, 2017 at 11:59

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

P. Lau Over a year ago

My table may grow big in future, will tis code run faster than my double query loop?

Qirel Over a year ago

Yes, this is a better approach - because running a query inside another query like that is bad practice. Instead of having 1 query, you end up with 4++ queries (and that can get to 10 queries if you have 9 different suppliers). You should avoid query inside another query when you can, and with a simple adjustment, you can easily avoid it, like shown above.

P. Lau Over a year ago

Ya it works 2! Thanks man! I am quite new to PHP so the only way I know is to loop within loop. Now I have more choice. I will use this when table grows.

Jelmergu Over a year ago

if you think that this is a better answer(I certainly do), change the accepted answer

P. Lau Over a year ago

I agree the answer from @Qirel is better approach and I will surely use his approach, but the 1st suggestion is answering correctly to my question : Is it possible to feed 1st while loop's output result as 2nd while loop's variable for query? So I leave it tat way.

JYoThI · Accepted Answer · 2017-06-08 12:00:09Z

0

First of all the below line is undefined because it's outside of the while loop

 $supplier4 = $row4['supplier'];

Push the above code into inside first while loop and string should be enclosed by single quotes like this

 "...supplier='$supplier4'"

you need to reset the $i=1 for each new supplier

Update 1:

<?PHP
include ('db.php');
$sql4 = "SELECT DISTINCT supplier FROM reorder_in_process";
$result4 = mysqli_query($con,$sql4);

while($row4 = mysqli_fetch_array($result4)){

    $i=1;
    $supplier4 = $row4['supplier'];

    echo "<h4>".$row4['supplier']."</h4>";

    echo  "<div><table><tr>
                            <th>No</th>
                            <th>Item</th>
                            <th>Cat. No</th>                                            
                            <th>Buy QTY</th>
                            <th>Supplier</th>                   
                       </tr>";

    $sql5 = "SELECT * FROM reorder_in_process WHERE supplier='$supplier4'";
    $result5 = mysqli_query($con,$sql5);
    while($row5 = mysqli_fetch_array($result5)) {

        echo "<tr>
                <td>".$i. "</td>
               <td>".$row5['item']."</td>
               <td>".$row5['cat_no']."</td>
               <td>".$row5['to_order']."</td>
               <td>".$row5['supplier']."</td>      
         </tr>";

         $i++;
    }
    echo "</table></div>";
}
mysqli_close($con);
?>

edited Jun 8, 2017 at 12:00

answered Jun 8, 2017 at 11:44

JYoThI

12.1k1 gold badge13 silver badges26 bronze badges

8 Comments

P. Lau Over a year ago

Yes! Tat works, but.....the number(No) is 1,2,3,4. It has to be 1,2,1,1.

JYoThI Over a year ago

yes you need to reset the $i=1 for each new supplier . check my update1 @P.Lau

P. Lau Over a year ago

I solve it myself oredi! Yes!! Thanks guys...u save my day and push me forward!

JYoThI Over a year ago

if my answer is useful mark it with green tick it's useful for future user reference @P.Lau

P. Lau Over a year ago

I solve that numbering (No) by putting $i=0 below $sql5 = "SELECT * FROM reorder_in_process WHERE supplier='$supplier4'";

|

Collectives™ on Stack Overflow

PHP 1st while loop's output result as 2nd while loop's variable for query

2 Answers 2

5 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related