在HTML / PHP表MYSQL中对结果进行分类

Question

I have two tables, dishes and days_avail, where dishes contains information regarding dishes cooked and days_avail contains what days a certain dish is available.

days_avail looks like this:

daysid || dishid || monday || tuesday || wednesday || friday

daysid: unique id

dishid: dish id from dishes table

Mon - Fri: boolean

So, what I am able to do is pull the dishes and show relevant info along with what day it is available on. However, what I am trying to accomplish is categorizing the dishes by the days they are available. So something like this:

Monday
dish name
dish desc desc desc

Tuesday
Dish name
dish desc dish desc

My query is as follows:

$sql = "SELECT * FROM dishes JOIN days_avail ON days_avail.dishid = dishes.id WHERE user_id = :cookid ORDER BY days_avail.daysid";
  try {
    $stmt = $db->prepare($sql);
    $stmt->execute(array(
    ':cookid' => $cookid
  ));
}
    catch(Exception $error) {
  echo '<p class="bg-danger">', $error->getMessage(), '</p>';
}

    while($row = $stmt->fetch()) { 
      $dish_name = $row['dish_name'];
      $dish_desc = $row['dish_desc'];
      $dish_price = $row['dish_price'];
      $mon = $row['Monday'];
      $tues = $row['Tuesday'];
      $wed = $row['Wednesday'];
      $thurs = $row['Thursday'];
      $fri = $row['Friday'];
    }

I can't group the dishes under one day its available. I have tried using a if statement (example: if($mon == 1) { echo "Monday // dish_info, etc" }) but this results in the heading being repeated for each dish.

I have also tried using java handlebars to more or less run a query and then simply "stuff" the results in the (appropriate) div boxes but did not work.

I am get a complete lost here. I would greatly appreciate any help.

Thank you

Answer 1

You can do this by putting your returned rows into an array, based off the day available-

...your query code...

$days=array('Monday'=>array(),'Tuesday'=>array(),'Wednesday'=>array(),'Thursday'=>array(),'Friday'=>array());

while($row = $stmt->fetch()) { 
    foreach($days as $day=>$array){
        if($row[$day]){
            $days[$day][]=array('dish_name'=>$row['dish_name'],
                                'dish_desc'=>$row['dish_desc'],
                                'dish_price'=>$row['dish_price']);
        }
    }
}

now just loop over your $days array

foreach($days as $day => $dishes){
    echo "<h2>$day</h2>";
    foreach($dishes as $dish){
        echo $dish['dish_name']."<br />";
        echo $dish['dish_desc']."<br />";
        echo $dish['dish_price']."<br />";
    }
}