Displaying users info

[PunkGo]

like, ok theres 2 tables involved, 1 called inventory, the other users, the users hold all the user's information, then inventory hold the owner, id , and the item_name, when a person clicks on a link i insert the owner and the item_name the id just goes up everytime. but a user can have about 50 items, and like instead of displaying like this,
--EX--
User's Inventory
ring
ring
ring
ring
ring
sword
sword
sword
sword
--/EX--
i want it to be displayed like this,
--EX--
User's Inventory
5 rings
4 swords

[LordShryku]

So you need to do a count(*) and group by item

[PunkGo]

such as.......example plz, im not good with loops 🙁

[LordShryku]

This isn't a loop, this is in your SQL. Let me see what your query is so far, and I can add this on for you

[PunkGo]

oh, okiee dokiee, thx

[PunkGo]

errr, heres whut im using now, and its giving me whut i posted...

if($action == 'inventory'){
		html_middle_top_rpg();
		echo 'You Have ' . $user_inventory . ' Item(s) In Your Inventory:<br>';
		echo 'They Are:<br><br>';
		$res=mysql_query("SELECT * from inventory WHERE owner='$username' order by item_name");

	while($r=mysql_fetch_object($res)) 
	echo $r->item_name ."<BR>\n";
	echo "<br><a href='http://www.counter-craft-central.com/members.php?action=view'>Back To Member's Section</a><br>";
	html_middle_bottom();
}

[LordShryku]

Okie........not a whole lot of info here, but here's a stab at it....

SELECT count(*), item_name 
FROM inventory 
WHERE owner='$username' 
ORDER BY item_name
GROUP BY item_name

[PunkGo]

so my code should be...

if($action == 'inventory'){ 
        html_middle_top_rpg(); 
        echo 'You Have ' . $user_inventory . ' Item(s) In Your Inventory:<br>'; 
        echo 'They Are:<br><br>'; 
        $res=mysql_query("SELECT count(*), item_name 
FROM inventory 
WHERE owner='$username' 
ORDER BY item_name
GROUP BY item_name"); 

    while($r=mysql_fetch_object($res))  
    echo $r->item_name ."<BR>\n"; 
    echo "<br><a href='http://www.counter-craft-central.com/members.php?action=view'>Back To Member's Section</a><br>"; 
    html_middle_bottom(); 
}

correct?

[LordShryku]

Give it a shot....you tell me 😃

[PunkGo]

well i did, heh and i got this

You Have 19 Item(s) In Your Inventory:
They Are:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/punkgo/public_html/members.php on line 202

[LordShryku]

Well damn. Do you have phpMyadmin or something you can try running the query manually against the database?

[PunkGo]

yea i got phpmyadmin, hold up it says

MySQL said:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT count(*), item_name
FROM inventory
WHERE owner='$

[LordShryku]

Hold on now....you gotta run the acual query without the code in myAdmin

Replace $username with a known username and run..

SELECT count(*), item_name

FROM inventory

WHERE owner='$username'
ORDER BY item_name
GROUP BY item_name

[PunkGo]

said this

SQL-query :

SELECT count( * ) , item_name
FROM inventory
WHERE owner = 'punkgo'
ORDER BY item_name
GROUP BY item_name
LIMIT 0 , 30

MySQL said:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY item_name LIMIT 0, 30' at line 5

[LordShryku]

Well, that's progress...

Try putting the GROUP BY before the ORDER BY

[PunkGo]

gj, that works...but when i changed the code...it just said the item_name, like
Ring and
Sword
but in phpmyadmin it worked great, said the number of items n all...

[LordShryku]

Okie....so we're gettin there 🙂

So I don't use mysql_fetch object much, but I 'll guess you need to name you count in SQL (Like SELECT count() AS total, item_name...), and I'm gonna change your loop to mysql_fetch_array, but you can change it back

    $res=mysql_query("SELECT count(*) AS items, item_name 
                      FROM inventory 
                      WHERE owner='".$username."'
                      GROUP BY item_name
                      ORDER BY item_name"); 

while($r=mysql_fetch_array($res)) { 
    echo $r['items']." ".$r['item_name']."<br>\n";
} 

[PunkGo]

thanks sooooo much!!!!, im making a Cool List on my profile and ur gonan be #1 ;-):p

[LordShryku]

No problem bud. Just come back and help other people once you become the uber-coder 😃