CODEWITHSUNDEEP
phpmysqlwhile-loop
jmiller
jmiller

Reputation: 588

How to split sql loop query into sections

I have a database table haccp which holds rows of criteria categorised by 8 sections. I'm trying to create a query to fetch all criteria and echo it under each section. For example:

Section 1

  1. criteria 1
  2. criteria 2

Section 2

  1. criteria 1
  2. criteria 2

I tried writing 8 separate sql queries using a WHERE clause and used a while loop to echo the results under each section but this took 30 secs to run and load the page.

PREVIOUS SQL QUERY

$get_delivery_criteria = $mysqli->prepare("SELECT criteria, section, hazard FROM haccp WHERE section='Delivery' ORDER BY criteria");
$get_delivery_criteria->execute();
$get_delivery_criteria->bind_result($criteria_db, $section, $hazard);
$get_delivery_criteria->store_result();
$row_cnt = $get_delivery_criteria->num_rows();
if($row_cnt >= 1)
{
    while ($get_delivery_criteria->fetch() )
    { 
    ?>
        <li><a href="?criteria=<? echo $criteria_db; ?>"><? echo $criteria_db. " ". $hazard; ?></a></li>    
    <? 
    }
    $get_delivery_criteria->close();
}

So i'm working on a new query that lists the sections in an array then uses foreach to loop through the criteria and display each under the section heading.

NEW SQL QUERY

$sections = array("1.Delivery", "2.Storage", "3.Preparation", "4.Cooking", "5.Serving", "6.Cleaning", "7.Building", "8.Management");
$get_criteria = $mysqli->prepare("SELECT criteria, section, hazard FROM haccp ORDER BY criteria");
$get_criteria->execute();
$get_criteria->bind_result($criteria_db, $section_db, $hazard_db);
$get_criteria->store_result();                              
$row_cnt = $get_criteria->num_rows();

if($row_cnt >= 1)
{
    while ($get_criteria->fetch() )
    { 
        foreach ($sections as $section)
        ?>
            <p class="haccp-section"><strong><? echo $section; ?></strong></p>
            <div class="divider"></div>
            <li><a href="?criteria=<? echo $criteria_db; ?>"><? echo $criteria_db. " ". $hazard_db; ?></a></li> 
        <? 
    }
}
$get_criteria->close();

However, this is echo'ing the section heading for each criteria, rather than the heading the the criteria list below.

Hope that makes sense and thanks in advance!

Upvotes: 1

Views: 411

Answers (2)

jmiller
jmiller

Reputation: 588

@B.Desai I've tweaked your query to use $section_db which wasn't correct. This now works as expected: 

                                    $get_criteria = $mysqli->prepare("SELECT criteria, section, hazard FROM haccp ORDER BY criteria");
                                    $get_criteria->execute();
                                    $get_criteria->bind_result($criteria_db, $section_db, $hazard_db);
                                    $get_criteria->store_result();                              
                                    $row_cnt = $get_criteria->num_rows();

                                    if($row_cnt >= 1)
                                    {
                                      $current_section="";
                                        while ($get_criteria->fetch() )
                                        { 
                                          if($section_db != $current_section)
                                          { 
                                          $current_section=$section_db;
                                          ?>
                                            <p class="haccp-section"><strong><? echo $section_db; ?></strong></p>
                                            <div class="divider"></div>
                                          <? 
                                          } 
                                          ?>
                                          <li><a href="?criteria=<? echo $criteria_db; ?>"><? echo $criteria_db. " ". $hazard_db; ?></a></li>    
                                          <? 
                                        }
                                        $get_criteria->close();
                                    }

Thanks for your help! :)

Upvotes: 0

B. Desai
B. Desai

Reputation: 16436

As I understand your issue. May be you want to group your data according to Section. For this try below code : 

<?php
 $get_criteria = $mysqli->prepare("SELECT criteria, section, hazard FROM haccp ORDER BY section,criteria");

  $get_delivery_criteria->execute();
$get_delivery_criteria->bind_result($criteria_db, $section, $hazard);
$get_delivery_criteria->store_result();
$row_cnt = $get_delivery_criteria->num_rows();
if($row_cnt >= 1)
{
  $current_section="";
    while ($get_delivery_criteria->fetch() )
    { 

      if($section != $current_section)
      { ?>
        <p class="haccp-section"><strong><? echo $section; ?></strong></p>
        <div class="divider"></div>
      <?php 
        $current_section=$section;
      } ?>

      <li><a href="?criteria=<? echo $criteria_db; ?>"><? echo $criteria_db. " ". $hazard; ?></a></li>    
    <? 
    }
    $get_delivery_criteria->close();
}

Upvotes: 2

Related Questions