How to split sql loop query into sections

Question

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

criteria 1
criteria 2

Section 2

criteria 1
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!

B. Desai · Accepted Answer · 2017-02-25 10:58:02Z

2

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();
}

answered Feb 25, 2017 at 10:58

B. Desai

16.4k5 gold badges28 silver badges48 bronze badges

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

5 Comments

jmiller Over a year ago

That would mean I would have to specify the current section as a variable, and therefore do this for each section which would mean 8 variables and 8 queries. Which is what my first query was - I just used a WHERE clause to specify each section. I'm looking for one query really.

B. Desai Over a year ago

@user3464091 have you seen my entire solution ? I am not calling quey 8 times. Its only once. Please see answer properly

jmiller Over a year ago

It doesn't work - returns function fetch() on null in. You're not setting $section from the array or the database?

B. Desai Over a year ago

function fetch() on null in is not issue of logic. Check your query executes proper or not

B. Desai Over a year ago

I have added only extra ORDER BY field which is section in your first attempt query. If your first query executed successfully then it must

jmiller · Accepted Answer · 2017-02-25 11:30:45Z

0

@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! :)

edited Feb 25, 2017 at 11:30

answered Feb 25, 2017 at 11:23

jmiller

59811 silver badges29 bronze badges

7 Comments

B. Desai Over a year ago

BTW you only have change of variable i.e $section to $section_db. You are declaring array but I can's see that you have use that array in anywhere in your posted answer code

jmiller Over a year ago

I don't understand how the section is echo'd only once from the if clause and not repeated in the loop though?

jmiller Over a year ago

Yes, but I moved this line $current_section=$section_db; before the <li> as the variable wasn't been passed.

B. Desai Over a year ago

Its because of condition. It will only true when section is changed.

jmiller Over a year ago

I see. Because $current_section is set outside of the while loop?

|

Collectives™ on Stack Overflow

How to split sql loop query into sections

2 Answers 2

5 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related