Page 1 of 1

please help php/database question URGENT

Posted: Tue Nov 02, 2010 4:37 pm
by 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:

Code: Select all

$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 terminology goes straight over my head

thanks in advance

Re: please help php/database question URGENT

Posted: Wed Nov 03, 2010 1:27 pm
by Sofw_Arch_Dev
Homework assignment?

Your SQL query is ok, but a next step would be to ensure that items are grouped together by category. There's the concept of GROUP BY in SQL but ORDER BY will suffice for now.

Code: Select all

$sql=mysql_query("SELECT items.itemname, cat.category FROM items,cat WHERE cat.catid=items.catid ORDER BY items.catid");
Then in your code, you keep track of which category you're dealing with and only print out the category when the category changes. For example:

Code: Select all

$currentCategory = 0;
while($res=mysql_fetch_array($sql))
{
    if( $res[ 'category' ] != $currentCategory ) {    // if the current category is not this row's category
       // assign it to be the current category
        $currentCategory = $res[ 'category' ];        
       // and print it out
        echo $res['category'] . "<br>";
    } // if
    echo $res['itemname'] . "<br>";
}
As for ordering things, I think if you research "ORDER BY" you'll be able to figure it out.