Jump to content


Photo

Need help sorting data in database


  • Please log in to reply
3 replies to this topic

#1 Konohamaru

Konohamaru
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 08 April 2006 - 05:37 AM

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 ID, Field 2 is called DESCRIPTION, and Field 3 is called PRICING. What I'm trying to do is seperate each row by their ID. For example:

SOUPS
[EDIT] Item 1
[EDIT] Item 2

APPETIZERS
[EDIT] Item 1
[EDIT] Item 2

---- ETC ----

This is their ID groups.

+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

And this is my current code so far:

<?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">&raquo; 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;
}

?>

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.

#2 MikeFairbrother

MikeFairbrother
  • Members
  • Pip
  • Newbie
  • 7 posts

Posted 08 April 2006 - 02:28 PM

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

<?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!');

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

alternatively, you could have it so its like
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!');

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:
if ($select == 'sushi'){
$id = 5 }

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


#3 Konohamaru

Konohamaru
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 08 April 2006 - 05:33 PM

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:

<?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">&raquo; {$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;
}

?>


#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 08 April 2006 - 07:05 PM

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

menu               category
---------          -----------
itemID       +---  catID
description  |     catDesc
pricing      |
catID -------+

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
<?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>";
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users