PHP/mySQLi : Loop inside a loop doesn't return all results

Question

I'm basically in the process of designing a web front-end for a menu, I have created a system to add meals to the menu and now I need to read it to the user. I also need to organize things into categories (aka div's). To do so I wrote code that (I think) should get a list of categories (distinct results in a column), then get the names and prices of each meal for each category.

The issue is that it is not displaying all the categories or meals.

Here's my DB structure, ID is the primary index My code looks like this,

        <?php
        mysqli_report(MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_INDEX);
        $db = new mysqli('localhost', 'username', 'password', 'db');
        if($db->connect_errno > 0){
            die('Unable to connect to database [' . $db->connect_error . ']');
        }

        $query = "SELECT DISTINCT `mealtype` FROM `meals`";
        if(!$result = $db->query($query)){
            die('There was an error running the query [' . $db->error . ']');
            }
        else {
            $row = $result->fetch_assoc();
            while($row = $result->fetch_assoc()){
                $mealtype=$row['mealtype'];
                echo "<div class='pure-u-1-3'><h1>".strtoupper($mealtype)."</h1>";
                $mealquery = "SELECT * from `meals` where `mealtype` = '".$mealtype."'";
                $mealresult = $db->query($mealquery);
                $mealrow = $result->fetch_assoc();
                echo $db->error;
                while($mealrow = $mealresult->fetch_assoc()){
                    echo "<b>".$mealrow['name']."</b>".$mealrow['price'].'<br>';
                    }
                echo "</div>";

            }

        }


        $db->close();
?>

I also noted that without mysqli_report(MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_INDEX); I get a warning, but to my understanding this is due to the use of the DISTINCT selector.

when Error reporting is fully activated it returns

PHP Fatal error:  Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement SELECT DISTINCT `mealtype` FROM `meals`' in (workingdir)/index.php:42

I'm at a complete loss as to why it won't display everything, I'm sure it's to do with the double loop, as when I just ask it to return the table in the menu manager it works fine.

EDIT : The Issue turned out to be a 1D13T error, I should have payed more attention to my code.

possible duplicate of [Fatal error: Uncaught exception 'mysqli\_sql\_exception' with message 'No index used in query/prepared statement'](http://stackoverflow.com/questions/5580039/fatal-error-uncaught-exception-mysqli-sql-exception-with-message-no-index-us) — Daan, Jul 16 '15 at 13:01

Your Common Sense · Accepted Answer · 2015-07-16T13:13:37.853

3

you have extra $row = $result->fetch_assoc(); in your code

while that error, everyone is so impressed with, is irrelevant to this problem.

by the way, the best setting for mysqli report is

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

I wish this kind of questions were legitimate and welcome, yet entitled to deletion right after being solved.

edited Jul 16 '15 at 13:13

answered Jul 16 '15 at 13:04

Your Common Sense

156,878
40
214
345

I should have noticed that ! thanks! Also thanks for the setting on reporting, next time i'll be more careful before posting :) – jmaris Jul 16 '15 at 13:27

PHP/mySQLi : Loop inside a loop doesn't return all results

1 Answers1