Displaying Data From Multiple MySQL Tables

Question

I'm having a little problem displaying data from a database...

I have two database tables:

categories(cat_id, cat_name)
menu_items(menu_id, menu_name, cat_id, menu_description, menu_price)

I would like to display data as:

Categorie 1 Name

cat_id = 1, menu_name, menu_description, menu_price
cat_id = 1, menu_name, menu_description, menu_price
cat_id = 1, ...

Categorie 2 Name

cat_id = 2, menu_name, menu_description, menu_price
cat_id = 2, menu_name, menu_description, menu_price
cat_id = 2, ...

Categorie 3 Name

cat_id = 3, menu_name, menu_description, menu_price
cat_id = 3, menu_name, menu_description, menu_price
cat_id = 3, ....

...

My code:

function get_menu_items() {
       // query database for a list of categories
       $conn = db_connect();
       $query = "select cat_id, cat_name from categories";
       $result = @$conn->query($query);
       if (!$result) {
         return false;
       }
       $num_cats = @$result->num_rows;
       if ($num_cats == 0) {
          return false;
       }
       $result = db_result_to_array($result);
       return $result;
    }


function display_menu_items($menu_array) {
  if (!is_array($menu_array)) {
     echo "<p>No menu items currently available</p>";
     return;
  }
  foreach ($menu_array as $row)  {
    echo "<section id='".$row['cat_name']."'>";
    echo "<div class='group course'>";
    echo "<h3>".$row['cat_name']."</h3>";

    ***/* Problem */***

     echo "<ul>";
foreach... {
    echo "<li>cat_id = #, menu_name, menu_description, menu_price</li>";
    }
    echo "</ul>

    ***/* Problem */***

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

Any suggestions?

Thanks.

Patrick · Accepted Answer · 2010-10-21 15:44:29Z

2

catRecordset = select * from categories
foreach cat in catRecordset
{
    print( cat.name );  
    menuRecordset = select * from menu where menyu.cat_id = cat.cat_id
    foreach( menuItem in menuRecordSet )
    {
        print( menuItem.whatever );
    }
}

answered Oct 21, 2010 at 15:44

Patrick

8,3387 gold badges59 silver badges73 bronze badges

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

Comments

lethalMango · Accepted Answer · 2012-05-07 22:24:18Z

0

Or even better use join and run only one query

$Recordset = 'select * from categories cat LEFT JOIN menu ON menu.cat_id = cat.cat_id';

$category = '';  
foreach $record in $Recordset  
{
    if ($category != $record['cat_id']) {  
        print( cat.name );   
        $category = $record['cat_id'];  
    }   
    print( $record['menu_name'] ..... );  
}

edited May 7, 2012 at 22:24

lethalMango

4,49114 gold badges58 silver badges92 bronze badges

answered Oct 21, 2010 at 15:50

Yasen Zhelev

4,0454 gold badges33 silver badges57 bronze badges

Collectives™ on Stack Overflow

Displaying Data From Multiple MySQL Tables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related