Need help sorting data in database

Konohamaru · April 8, 2006

I'm trying to do a resturant website with PHP. I'm trying to make our menu editable via admin interface. Field 1 is called [b]ID[/b], Field 2 is called [b]DESCRIPTION[/b], and Field 3 is called [b]PRICING[/b]. What I'm trying to do is seperate each row by their [b]ID[/b]. For example:

SOUPS
[EDIT] Item 1
[EDIT] Item 2

APPETIZERS
[EDIT] Item 1
[EDIT] Item 2

---- ETC ----

This is their ID groups.

[code]+Field 1
1 = soups
2 = salads
3 = appetizers
4 = entrees
5 = sushi
6 = a la carte
7 = dessert
8 = beverages

+Field 2
Description

+Field 3
Pricing[/code]

And this is my current code so far:

[code]
<?php

if ($_GET['act'] == 'sushi')
{

$getList = $sql->query("SELECT id, description, pricing FROM sushi " .
" ORDER BY id ASC") or die('Could not connect to the database!');

$admin->html .= html_list_start();
  while ($soupResult = $sql->fetch_array($getList))
  {
    $admin->html .= html_list_result($soupResult);
  }
$admin->html .= html_list_end();
}

function html_list_start() {
return <<<HTML

<br>
<div class="thead">» Drunken Fish Sushi Menu </div>
<div>
<table class="intable"><br>
HTML;
}

function html_list_result($soupResult) {
return <<<HTML

<tr>
<td class="content">{$soupResult['description']}</td>
</tr>
HTML;
}

function html_list_end() {
return <<<HTML

</table></div>

HTML;
}

?>
[/code]

I know it seems messy and unfinished, but I just need to know how to sort by ID and print each groupings seperately. Thanks for any assisstance. Criticism is welcome.

MikeFairbrother · April 8, 2006

not quiet sure what you mean, ellaborate?

However, wouldent it be easier to have a 'menu' database, then inside that, tables for each meal.
Then it would be

[code]<?php

if ($_GET['act'] == 'sushi')
{

$getList = mysql_query("SELECT `id`, `description`, `pricing` FROM `menu` WHERE id=5") or die('Could not connect to the database!');[/code]

id=5 because this was what the id for sushi is...

alternatively, you could have it so its like
[code]
if ($_GET['act'] == 'soups'){
$id = 1 }
elseif ($_GET['act'] == 'salads'){
$id = 2 }
elseif ($_GET['act'] == 'appetizers'){
$id = 3 }
elseif ($_GET['act'] == 'entrees'){
$id = 4 }
elseif ($_GET['act'] == 'sushi'){
$id = 5 }
elseif ($_GET['act'] == 'a la carte'){
$id = 6 }
elseif ($_GET['act'] == 'dessert'){
$id = 7 }
elseif ($_GET['act'] == 'beverages'){
$id = 8 }

$getList = mysql_query("SELECT `id`, `description`, `pricing` FROM `menu` WHERE id=$id") or die('Could not connect to the database!');
[/code]

You could implement the above into some sort of selection, so you would have a hyperlink for each dish/food item, which would then display whatever you selected, all in one file

<a href="menu.php?select=sushi">Sushi</a>
<a href="menu.php?select=salad">Salad</a>

Then change the above respectively:
[code]if ($select == 'sushi'){
$id = 5 }[/code]

Hope this helps...sorry if its not what you wanted :)

Konohamaru · April 8, 2006

That's a good structure to use (as a backup if i cant figure this out), but I what I really want to do is basically display all the menu items on one page sorting by their ID. For example:

SOUPS (ID = 1)
items 1
items 2

SALADS (ID = 2)

items 1
items 2

My problem is updating the results to query and show the next ID. Here's my current code:

[code]
<?php

$n = 1;

If ($n == 1)
{
$name = "Soups";
}
ElseIf ($n == 2)
{
$name = "Salads";
}
ElseIf ($n == 3)
{
$name = "Appetizers";
}
ElseIf ($n == 4)
{
$name = "Entrees";
}
Elseif ($n == 5)
{
$name = "Sushi";
}
Elseif ($n == 6)
{
$name = "A la Carte";
}
Elseif ($n == 7)
{
$name = "Desserts";
}
Elseif ($n == 8)
{
$name = "Beverages";
}

if ($_GET['act'] == 'sushi')
{

$getList = $sql->query("SELECT id, description, pricing FROM sushi " .
" WHERE id=$n ORDER BY id ASC") or die('Could not connect to the database!');

$admin->html .= html_list_start($name);
$n++;

while ($Result = $sql->fetch_array($getList))
{
$admin->html .= html_list_result($Result);
}

$admin->html .= html_list_end();
}

function html_list_start($name) {
return <<<HTML

<div class="thead">» {$name}</div>
<table class="intable">

HTML;
}

function html_list_result($Result) {
return <<<HTML

<tr>
<td class="content">{$Result['description']}</td>
</tr>
HTML;
}

function html_list_end() {
return <<<HTML

</table><br>

HTML;
}

?>
[/code]

Barand · April 8, 2006

I agree with Mike, put all items in a single menu table and have a separate category table

[code]menu               category
---------          -----------
itemID       +---  catID
description  |     catDesc
pricing      |
catID -------+[/code]

So category table contains

1 | soups
2 | salads
3 | appetizers
4 | entrees
5 | sushi
6 | a la carte
7 | dessert
8 | beverages

Your query and processing to group the items
[code]<?php
$sql = "SELECT m.itemID, m.description, m.pricing, c.catDesc
        FROM menu m INNER JOIN category c
        ON m.catID = c.catID
        ORDER BY c.catID, m.itemID";
$res = mysql_query($sql) or die (mysql_error());

$prevCat = '';
echo "<TABLE>";
while (list($id, $desc, $price, $cat) = mysql_fetch_row($res)) {

       if ($cat != $prevCat) {
           echo "<tr>
                  <td colspan='3'><H3>$cat</H3></td>
                  </tr>";
       }
       echo "<tr>
              <td><a href='menuedit.php?id=$id'>EDIT</a></td>
              <td>$desc</td>
              <td>$price</td>
              </tr>";

       $prevCat = $cat;
}
echo "</TABLE>";
?>[/code]

Sign In

Need help sorting data in database

Recommended Posts

Konohamaru

Link to comment

Share on other sites

MikeFairbrother

Link to comment

Share on other sites

Konohamaru

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information