displaying data from database correctly!

[busby]

ok so im sure this is only a small problem but still here it is:

 

im making a shopping list app where users can create a list...when they view the list they can populate it with categories such as frozen food, fruit, veg etc etc...they can then populate categories with items such as apples, potatoes or ice cream etc etc.

 

now i have some data in the database already...and i wanted to display it on the page like this.

 

 

ASDA SHOPPING LIST

 

fruit

apples

bananas

plums

 

veg

potatoes

carrots

 

frozen

burgers

chips

ice cream

 

however at the moment with my code it displays like this:

 

ASDA SHOPPING LIST

 

fruit

apples

bananas

plums

potatoes

carrots

burgers

chips

ice cream

 

veg

 

frozen

 

 

here is my code:

 

include_once("config_class.php");

$db = new db();         // open up the database object
$db->connect();            // connect to the database

//getting id of the data from url
$id = $_GET['id'];
$sql=mysql_query("SELECT listname FROM list WHERE listid=$id") or die("cannot select: ".mysql_error());

$sql2=mysql_query("SELECT catid, category FROM cat WHERE listid=$id") or die("cannot select: ".mysql_error());

$sql3=mysql_query("SELECT items.itemname, items.itemid, cat.catid FROM items, cat WHERE cat.catid=items.catid") or die("cannot select: ".mysql_error());

$temp_cat = "";

$res=mysql_fetch_array($sql);
echo "<b>" . $res['listname'] . "</b>" . "<br><br>";
echo "<form action='addcat.php?id=$id' method='post'>";
echo "<input type='text' id='addcat' name='addcat'>";
echo "<input type='submit' value='Add Category'>";
echo "</form>";

while($res2=mysql_fetch_array($sql2))
{
echo "<table cellpadding='2' cellspacing='2' width='800'>";
echo "<tr>";

if($res2['category'] != $temp_cat )
    {
echo "<td width='20%'>";
echo "<b>" . $res2['category'] . "</b>" . "</td>";
echo "<td width='20%'><a href='delcat.php?id=$res2[catid]&id2=$id'>Delete Category</a></td>";
echo "<form action='additem.php?id=$res2[catid]&id2=$id' method='post' name='form1'>";
echo "<td width='20%'>";
echo "<input type='text' name='itemname'></td>";
echo "<td width='20%'>";
echo "<input type='submit' name='Submit' value='Add Item'></td>";
echo "</form>";
echo "</tr>";
$temp_cat=$res2['category'];
}	

while($res3=mysql_fetch_array($sql3))
{
echo "<tr>";
echo "<td width='20%'>";
echo "$res3[itemname]" . "</td>";
echo "<td width='20%'>";
echo "<a href='delitem.php'>Delete Item</a>" . "</td>";
echo "</tr>";

}
echo "</table>";
}

 

could someone please help me display this correctly?

 

thanks in advance

 

[busby]

anyone please?

[busby]

really need help here guys

[busby]

somebody

[Vitamin]

use the <h1> through <h6> tags.  Use CSS to get them to look how you want.

[busby]

no m8 ur missing the point...the size of text or css layout isnt part of what im asking

 

 

i want to display my results in a certain way.

 

category and then related items....then the next category and related items...and so on......at the moment its just displaying ALL items under the top category...please help this is urgent

[Vitamin]

Oh sorry I miss under stud what you were trying to do.  Try something like this

 

$sql2=mysql_query("SELECT catid, category FROM cat WHERE listid=$id") or die("cannot select: ".mysql_error());
$sql3=mysql_query("SELECT items.itemname, items.itemid, cat.catid FROM items, cat WHERE cat.catid=items.catid") or die("cannot select: ".mysql_error());

while ($res2 = mysql_fetch_array($sql2) {
    echo $res2['category'];
        while ($res3 = mysql_fetch_array($sql3) {
            if $res2['catid'] == $res3['catid'] { //im not exactly sure if i have this right, but you should be able to figure it out 
                echo $res3['itemname']
            }
        }
}

[busby]

ah thats a little closer...doing it that way now displays the first categories items under the category...but doesnt display any items under any of the other categories...ive never been so tormented by a piece of code before lol

[Vitamin]

The problem is that for each shopping list you want to display you have to have a list of items that are say on list 1, but the items on list 2 are going to be different then the items on list 1.  So you will need another table to keep track of what items are on which list.

 

To fix what you are working on now you should be able to do the following.

 

$sql2=mysql_query("SELECT catid, category FROM cat WHERE listid=$id") or die("cannot select: ".mysql_error());
$sql3=mysql_query("SELECT itemname, itemid, catid FROM items") or die("cannot select: ".mysql_error()); //you want to get the items.catid

while ($res2 = mysql_fetch_array($sql2) {
    echo $res2['category'];
        while ($res3 = mysql_fetch_array($sql3) {
            if $res2['catid'] == $res3['catid'] {
                echo $res3['itemname']
            }
        }
}

I think that should work, but ever list is going to have the same items on it.

 

[Anti-Moronic]

..and this is a time-bomb:

 

$id = $_GET['id'];

 

You have to escape data before you enter it into a sql query, or do it anyway: it's best practice. That should be:

 

$id = intval($_GET['id']);

 

Which will force it to be a number and not some malicious piece of code which could delete your entire database.