php/mysql help please urgent

[busby]

hi. im going to try and explain this as clearly as possible...im in a hurry for a solution :/

 

so i need to create a shopping list application that allows a user to create a list, populate it with categories (frozen food, fruit, electrical etc) and then populate categories with items (burgers, apples, DVD's) they need to be able to add, edit and delete lists, items and categories aswell as be able to order them individually (for example move frozen category up or down the list to any position...same for items in categories).

 

so i created a database...i have 3 tables:

 

List table (listid, listname, listorder)

Category table (catid, listid, catname, catorder)

item table (itemid, catid, itemname, itemorder)

 

i got it to create, edit and delete lists...but when i view a list to see the categories and items in that list i cant get it to display properly.

 

i used this code:

 

$sql=mysql_query("SELECT items.itemname, cat.category FROM items,cat WHERE cat.catid=items.catid");
while($res=mysql_fetch_array($sql))
{
        echo $res['category'] . "<br>";
        echo $res['itemname'] . "<br>";
}

 

however it displays the results as follows:

 

frozen

chips

frozen

burgers

fruit & veg

apples

fruit & veg

potatoes

 

what i need is for it to display the category name only once...then each item under that correct category..then display the next category and its respected items etc etc.

 

that is my main problem..the only other thing i would like to ask is how do i order things? and i dont just mean ascending or descending..i mean individual items...someone told me to create an order field in each table..but i dont know what to do with them.

 

please help anybody?

im not great with php any examples would be greatly appreciated as most terminology goes straight over my head

thanks in advance

[awjudd]

Question 1: what i need is for it to display the category name only once...then each item under that correct category..then display the next category and its respected items etc etc.

 

Keep track of the current header and use an if statement if it is different then print it, otherwise don't.

 

Question 2: I would order by the category and then the itemname (ORDER BY category, itemname)

 

~judda

[busby]

how do i keep track of it? also...i know i can order by specific fields...but i need to be able to order individually...for example...if i wanted to place apples second from bottom...i dont know how

[awjudd]

How do you keep track of it? ... in a variable?

$prevCategory = $res['category'];

 

The sorting in that manner should probably as you said put the things followed by a rank you would need a simple table mapping the orders, or you could do it in PHP (less efficient) using the array sorting functions.

 

~judda

[busby]

i think something is going right over my head here...i dont get this keeping track of it...how does assigning it a variable keep track? i mean...isnt assigning it a variable just a quicker way of writing out $res['category']? doesnt do anything other than give it a new name.

 

could i get a slightly more detailed explanation?

 

im not sure how i can write code to do this...i cant picture it in my head

[awjudd]

Not in the case where we are trying to see if something changes ...

 

<?php
$sql=mysql_query("SELECT items.itemname, cat.category FROM items,cat WHERE cat.catid=items.catid");
$curr = '';
while($res=mysql_fetch_array($sql))
{
    if ( $curr != $res [ 'category' ] )
    {
        echo $res['category'] . "<br>";
        $curr = $res [ 'category' ];
    }
        echo $res['itemname'] . "<br>";
}

 

Something like that would work.

 

~judda