CODEWITHSUNDEEP
phpmysql
AndyKing
AndyKing

Reputation: 139

Display results grouped by a category

OK i have no idea how to word the title better but hopefully i can explain better here:

I have a database of animals, with several columns, one of which is named Category which contains a single word string such as frog, or newt.

I can successfully query the database for these things in two separate queries and print the results separately, but i ideally want to use a single query and then split the data afterwards to print the result to the page but in two sections, one for frogs and one for newts.

currently my code looks like this:

    $query =    "SELECT * FROM livestock WHERE Category = 'frog' OR Category = 'newt'";

    $result = mysqli_query($con, $query) or die(mysqli_error($con));  

    echo"<div class='rowtable'>";

    while($row = mysqli_fetch_array($result)){

        $commondraft = $entry['Name'];

        echo"<a href='/stocklist/".$entry['id']."/".commonName($commondraft)."' class='row'>";
        echo"<div class='common'>".$entry['Name']."</div>"; 
        echo"<div class='descr'>".$row['Description']."</div>";
        echo"<div class='sex'>".$row['Sex']."</div>"; 
        echo"<div class='age'>".$row['Age']."</div>"; 
        echo"<div class='size'>".$row['Size']."</div>";  
        echo"<div class='origin'>".$row['Origin']."</div>"; 
        echo"<div class='scientific'>".$row['Scientific']."</div>"; 
        echo"<div class='prices'>".$row['Price']."</div>"; 

        echo"</a>";
        }
    echo"</div>";

This obviously prints out all entries for both categories of frog AND newt. How can i select just one category here and then use the other category elsewhere without re-querying the database for just the remaining category?

Upvotes: 0

Views: 211

Answers (1)

Igor
Igor

Reputation: 1855

You can do what others suggested (order by Category), but I think this will be better solution: when you retrieve items from livestock table you can put them in separate arrays for every category. I mean, that you can use $items array (in my example) as dictionary (hash map) where key is category name and value is array of all items which belongs to that category. Later, when you want to output items from some category just call output function with desired category. For example (I have simplified output; you can use this for any amount of categories. You just need to change $query in getItems):

    function getItems($con) {
      $items = array();
      $query =    "SELECT * FROM livestock WHERE Category = 'frog' OR Category = 'newt'";
      $result = mysqli_query($con, $query) or die(mysqli_error($con));  
      while($row = mysqli_fetch_array($result)) {
         $category = $row['Category'];
         if (!isset($items[$category])) {
            $items[$category] = array();
         }
         array_push($items[$category], $row);
      }
      return $items;
   }

   function output($category, $items) {
      echo"<div class='rowtable'>";
      foreach ($items[$category] as $entry) {
          echo"<div class='common'>".$entry['Name']."</div>"; 
      }
      echo"</div>";
   }

   $items = getItems($con); // $con holds connection to database
   output('frog', $items); // prints just items from frog category
   output('newt', $items); // prints just items from newt category

Upvotes: 1

Related Questions