while loop within while loop for fetching data from database

Question

I am trying to fetch data from multiple SQL tables (if they have matching IMEI) and display them in html table. If the IMEI doesn't match then I want it to display '-' in that box.

For this I have display data from table A(import) then tried to match the IMEI. While doing this I used if else to define the condition, and the main problem occurs in that if and else. while executing code it will execute the if section following the else section it doesn't follow the condition.

for example:

while ($row = mysqli_fetch_assoc($result)){
...
...
*****************this while shows error
while($row1 = mysqli_fetch_assoc($result1)){
********here after executing if it will execute the else also. it does not follow the condition
if(import_imei == sales_imei){
<td>imei</td>
}else{
<td> - </td>
}
}
...
************the same condition work here perfectly
while($row2 = mysqli_fetch_assoc($result2)){
if(import_imei == ret_imei){
<td>name</td>
}else{
<td> - </td>
}
}

}

Here below is the code. I had written 'error' in code where the error occurs

 $sql ="SELECT model,importDate,IMEI1,IMEI2 FROM mainreport ORDER BY id DESC LIMIT $start_from, $limit";
    $result = mysqli_query($con,$sql);
    while ($cgimport = mysqli_fetch_assoc($result)) {
      $cgimport_modelName = $cgimport['model'];
      $cgimport_importDate = $cgimport['importDate'];
      $cgimport_IMEI1 = $cgimport['IMEI1'];
      $cgimport_IMEI2 = $cgimport['IMEI2'];
      ?>
      <tr>




        <!-- **************   DISPLAYING DATA IN TABLE FROM mainreport **************** -->
        <td style="text-align: center;"><?php echo $cgimport_modelName ;?></td>
        <td style="text-align: center;"><?php echo $cgimport_importDate ;?></td>
        <td style="text-align: center;"><?php echo $cgimport_IMEI1 ;?></td>


        <!-- ************** FETCHING SALES DATA MADE BY CG TO DISTRIBUTOR **************** -->
        <?php

        $sql1 = "SELECT billMiti,invoiceDate,billno,price,nameOfParty,IMEINo FROM salesreport ";
        $result1 = mysqli_query($con, $sql1);
        while ($cgSalesReport = mysqli_fetch_assoc($result1)) {
          $cgSalesReport_BillMiti = $cgSalesReport['billMiti'];
          $cgSalesReport_invoiceDate = $cgSalesReport['invoiceDate'];
          $cgSalesReport_billno = $cgSalesReport['billno'];
          $cgSalesReport_price = $cgSalesReport['price'];
          $cgSalesReport_distName = $cgSalesReport['nameOfParty'];
          $cgSalesReport_IMEINo = $cgSalesReport['IMEINo'];

          // echo $cgimport_IMEI1;

          //******************************************
          //ERROR IN THIS IF 
          //******************************************
          if ( ($cgSalesReport_IMEINo == $cgimport['IMEI1']) || ($cgSalesReport_IMEINo == $cgimport['IMEI2']) ) {

            ?>


            <!-- ************* DISPLAYING TABLE FROM salesreport *********************** -->
            <td style="text-align: center;"><?php echo $cgSalesReport_BillMiti; ?></td>
            <td style="text-align: center;"><?php echo $cgSalesReport_invoiceDate; ?></td>
            <td style="text-align: center;"><?php echo $cgSalesReport_billno; ?></td>
            <td style="text-align: center;"><?php echo $cgSalesReport_price; ?></td>
            <?php


            // *****************  FETCHING DATA FROM cdistributor ****************************
            $sql2 = "SELECT rdCode,name FROM cdistributor ";
            $result2 = mysqli_query($con, $sql2);
            while ($cgDistributor = mysqli_fetch_assoc($result2)) {
              $cgDistributor_rdCode = $cgDistributor['rdCode'];
              $cgDistributor_name = $cgDistributor['name'];



              if ($cgDistributor_name == $cgSalesReport_distName) {
                ?>
                <!-- **************** DISPLAYING TABLE FROM cdistributor ***************** -->
                <td align='center'><?php echo $cgDistributor_rdCode ; ?></td>
                <td align='center'><?php echo $cgDistributor_name ; ?></td>
                <?php
              }
              else{
                 ?>
                <td style="text-align: center;">-</td>
                <td style="text-align: center;">-</td>
                <?php                   
              }
            }
            // ************ END FETCHING cdistributor *******************
          }
          // *************************
          // ERROR IN THIS ELSE
           //*************************
          else{
              ?>
                 <td style="text-align: center;">-</td> 
                 <td style="text-align: center;">-</td> 
              <?php  
          }
        }
        // *************** END FETCHING salesreport *****************            

        ?>

        <!-- *******************  FETCHING DISTRIBUTOR SALES DATA MADE BY DISTRIBUTOR TO RETAILER ****************** -->
        <?php

        $sql3 = "SELECT rinvoiceDate,rbillno,rPrice,rIMEINo,rnameOfParty,distname FROM rrdsalesreport";
        $result3 = mysqli_query($con, $sql3);
        while ($cgDistributorSales = mysqli_fetch_assoc($result3)) {
          $cgDistributorSales_rinvoiceDate = $cgDistributorSales['rinvoiceDate'];
          $cgDistributorSales_rbillno = $cgDistributorSales['rbillno'];
          $cgDistributorSales_rPrice = $cgDistributorSales['rPrice'];
          $cgDistributorSales_rIMEINo = $cgDistributorSales['rIMEINo'];
          $cgDistributorSales_rnameOfParty = $cgDistributorSales['rnameOfParty'];
          $cgDistributorSales_distname = $cgDistributorSales['distname'];

          if ($cgimport_IMEI1 == $cgDistributorSales_rIMEINo || $cgimport_IMEI2 == $cgDistributorSales_rIMEINo) {

            ?>


            <!-- ************* DISPLAYING TABLE FROM rrdsalesreport *********************** -->
            <td style="text-align: center;"><?php echo $cgDistributorSales_rbillno; ?></td>
            <td style="text-align: center;"><?php echo $cgDistributorSales_rinvoiceDate; ?></td>
            <td style="text-align: center;"><?php echo $cgDistributorSales_rPrice; ?></td>
            <?php


            // *****************  FETCHING DATA FROM retailer ****************************
            $sql4 = "SELECT rCode,rname,rd FROM retailer ";
            $result4 = mysqli_query($con, $sql4);
            while ($cgRetailer = mysqli_fetch_assoc($result4)) {
              $cgRetailer_rCode = $cgRetailer['rCode'];
              $cgRetailer_rname = $cgRetailer['rname'];
              $cgRetailer_rd = $cgRetailer['rd'];



              if ($cgRetailer_rname == $cgDistributorSales_rnameOfParty) {
                ?>
                <!-- **************** DISPLAYING TABLE FROM retailer ***************** -->
                <td align='center'><?php echo $cgRetailer_rCode ; ?></td>
                <td align='center'><?php echo $cgRetailer_rname ; ?></td>
                <?php
              }
              else{
                 ?>
                <td style="text-align: center;">-</td>
                <td style="text-align: center;">-</td>
                <?php                   
              }
            }
            // ************ END FETCHING retailer *******************
          }else{
            ?>
                <td style="text-align: center;">-</td>
                <td style="text-align: center;">-</td>
                <td style="text-align: center;">-</td>
                <td style="text-align: center;">-</td>
                <td style="text-align: center;">-</td>
            <?php
          }
        }
        // *************** END FETCHING rrdsalesreport *****************
        ?>

        <!-- ***************** FETCHING DATA FROM activereport ******************** -->
        <?php
        $sql5 = "SELECT aIMEI,dateOfActivation FROM activereport";
        $result5 = mysqli_query($con, $sql5);
        while ($cgActivereport = mysqli_fetch_assoc($result5)) {
          $cgActivereport_aIMEI = $cgActivereport['aIMEI'];
          $cgActivereport_dateOfActivation = $cgActivereport['dateOfActivation'];
          if ($cgimport_IMEI1 == $cgActivereport_aIMEI || $cgimport_IMEI2 == $cgActivereport_aIMEI) {
            ?>
            <td align='center'><?php echo $cgActivereport_dateOfActivation ; ?></td>
            <?php
          }
        }

        // *************  END OF FETCHING activereport ***************

        ?>

      </tr>

      <?php
    } //************ END FETCHING MAINREPORT***********************

    ?>

Use one of the [`JOIN`](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) variants instead of having multiple loops. — Script47, Sep 25 '17 at 09:14
I cannot use JOIN, because in some case the data may not be present in that table and I had to show the rest of data too. — Sushank Pokharel, Sep 25 '17 at 09:16
you can use a table1 LEFT JOIN table 2 to get all the results from table 1 and only the matching results from table2. If you have no data in table 2 for a specific record in table 1 you will get "null" for that row. — Lelio Faieta, Sep 25 '17 at 09:18
@LelioFaieta Left join actually works, but for only small data. Now the problem that occured with the left join is It takes a long time for processing. I have got millions of data in database and it wont be displayed even after 1 hour. I want to display it faster. And I have used pagination and limit of 25 only for a single page . — Sushank Pokharel, Nov 17 '17 at 05:26
This is another issue and is related to database optimization. You have to create the correct indexes to optimize the join. — Lelio Faieta, Nov 17 '17 at 17:54
@LelioFaieta I have used indexes in the IMEI field because I have used IMEI to link between two database. But still it does not help. — Sushank Pokharel, Nov 19 '17 at 06:02
Open a new question where starting from the new joined query you can ask for help on performance tuning. You need to index not only the joined fields but probably some of those you use for where clause or for ordering. — Lelio Faieta, Nov 19 '17 at 10:17

Umesh Moradiya · Answer 1 · 2017-09-26T04:54:14.050

1

$sql = "Your First Query..";

                $result = mysql_query($sql);

                $jsonData = array();

while ($array = mysql_fetch_assoc($result)) {

                        $follower_id=$array['to_follow_user_id']
                        $SQL_ONE = "Your Second Query.....";
                            $result_one = mysql_query($SQL_ONE);
                            while ($array_one = mysql_fetch_assoc($result_one)) {
                                $jsonData[] = $array_one;
                            }


                }

I think you want something like above..

edited Sep 26 '17 at 04:54

answered Sep 25 '17 at 09:20

Umesh Moradiya

202
2
10

*I think you want something like below..* - [Below](https://www.google.co.uk/search?q=define+below&oq=define+below&gs_l=psy-ab.3..0l3j0i10k1.4563.6646.0.6810.14.13.1.0.0.0.107.993.11j1.12.0....0...1.1.64.psy-ab..1.13.998...0i67k1j0i131k1.0.-oGHYzKSFvM) or [above](https://www.google.co.uk/search?q=define+above&oq=define+above&aqs=chrome..69i57j0l5.1408j0j7&sourceid=chrome&ie=UTF-8)? Not the down-voter. – Script47 Sep 25 '17 at 09:23
I will try @Script47, and reply the answer. I haven't tried this. – Sushank Pokharel Nov 17 '17 at 05:22
@Script47 And please would you like to provide some explanation of code.. I don't know much about **JSON** So if you provide some explanation it would be great help for me to know what will I be doing in this code. – Sushank Pokharel Nov 17 '17 at 05:31

while loop within while loop for fetching data from database

1 Answers1