Page 1 of 1

please help!!! problem displaying data

Posted: Sun Nov 07, 2010 12:45 pm
by 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:

Code: Select all

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 :)

Re: displaying data from database correctly!

Posted: Sun Nov 07, 2010 1:53 pm
by busby
anyone please?

Re: please help!!! problem displaying data

Posted: Sun Nov 07, 2010 2:38 pm
by DigitalMind
show your database structure with comments.

Re: please help!!! problem displaying data

Posted: Sun Nov 07, 2010 2:44 pm
by busby
DigitalMind wrote:show your database structure with comments.
i have 3 tables

LIST:
listid listname



CAT:
catid category listid



ITEMS:
itemid itemname catid



those are my tables

Re: please help!!! problem displaying data

Posted: Sun Nov 07, 2010 3:22 pm
by busby
somebody??

Re: please help!!! problem displaying data

Posted: Sun Nov 07, 2010 4:05 pm
by DigitalMind

Code: Select all

<?php

$id = $_GET['id']; // always double check user input! i didn't!

$sql = 'select L.listid, listname, C.catid, category, I.itemid, itemname from list L left join cat C on L.listid = C.listid left join items I on C.catid = I.catid where L.listid = ? order by L.listid, C.catid';
$db = new mysqli('localhost', 'root', '', 'test'); // host, user, password, db name
$stmt = $db->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->bind_result($listid, $listname, $catid, $category, $itemid, $itemname);
$stmt->execute();

$last_listid = false;
$last_catid = false;

while ($stmt->fetch()) {
    if ($last_listid === false || $last_listid !== $listid) {
        $last_listid = $listid;
        echo "<h1>$listname (listid=$listid)</h1><br>";
    }

    if ($last_catid === false || $last_catid !== $catid) {
        $last_catid = $catid;
        echo "<h2>$category (catid=$catid)</h2><br>";
    }
    
    echo "$itemname (itemid=$itemid)<br>";
}
?>
list1 (listid=1)

cat1 (catid=1)

item1 (itemid=1)
item2 (itemid=2)
item3 (itemid=3)

cat2 (catid=2)

item6 (itemid=6)
item4 (itemid=4)
item5 (itemid=5)

cat3 (catid=3)

item9 (itemid=9)
item7 (itemid=7)
item8 (itemid=8)

Re: please help!!! problem displaying data

Posted: Sun Nov 07, 2010 4:17 pm
by busby
dammit im sure that example would work but i dont understand it.

whats all the bind param and stmt variables for? why are you connecting to the database after your select statement? what is L.listid? do you mean list.listid?
god this is tormenting

Re: please help!!! problem displaying data

Posted: Sun Nov 07, 2010 4:24 pm
by busby
also it doesnt recognise any variables like itemname etc etc

that code is very complicated for me

Re: please help!!! problem displaying data

Posted: Mon Nov 08, 2010 2:55 am
by DigitalMind
L. is a table alias
busby wrote:whats all the bind param and stmt variables for?
http://www.php.net/manual/en/mysqli-stmt.bind-param.php