please help!!! problem displaying data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
busby
Forum Newbie
Posts: 13
Joined: Tue Nov 02, 2010 4:25 pm

please help!!! problem displaying data

Post 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 :)
Last edited by busby on Sun Nov 07, 2010 1:53 pm, edited 1 time in total.
busby
Forum Newbie
Posts: 13
Joined: Tue Nov 02, 2010 4:25 pm

Re: displaying data from database correctly!

Post by busby »

anyone please?
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

Re: please help!!! problem displaying data

Post by DigitalMind »

show your database structure with comments.
busby
Forum Newbie
Posts: 13
Joined: Tue Nov 02, 2010 4:25 pm

Re: please help!!! problem displaying data

Post 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
busby
Forum Newbie
Posts: 13
Joined: Tue Nov 02, 2010 4:25 pm

Re: please help!!! problem displaying data

Post by busby »

somebody??
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

Re: please help!!! problem displaying data

Post 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)
busby
Forum Newbie
Posts: 13
Joined: Tue Nov 02, 2010 4:25 pm

Re: please help!!! problem displaying data

Post 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
busby
Forum Newbie
Posts: 13
Joined: Tue Nov 02, 2010 4:25 pm

Re: please help!!! problem displaying data

Post by busby »

also it doesnt recognise any variables like itemname etc etc

that code is very complicated for me
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

Re: please help!!! problem displaying data

Post 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
Post Reply