Getting the quantity of a distinct column
Posted: Thu Jul 02, 2009 1:41 pm
I have a table that has stored all the items a user owns, but each user can own many of the same item. I'm trying to write out something that will take each unique item ID# and tell me (well, the user) how many of each item they own instead of just listing every row that they own in the PHP output. I've tried SELECT distinct and count to no avail, but likely am not using them correctly.
Additionally, I have no idea out to convert the quantity produced by the mysql query into PHP.
This is the code I have right now. It's a bit of a trainwreck.
Additionally, I have no idea out to convert the quantity produced by the mysql query into PHP.
This is the code I have right now. It's a bit of a trainwreck.
Code: Select all
<?php $sql_query = "SELECT distinct(itemid), id, used FROM inventory WHERE `userid`='$userid' AND `itemid`!='0'";
$result = mysql_query($sql_query);
$results = mysql_numrows($result);
while ($rows = mysql_fetch_array($result))
{
$itemid = $rows[itemid];
$id = $rows[id];
$used = $rows[used];
$sql_query2 = "SELECT id,name,image,description,type,upcost FROM itemdata WHERE `id`='$itemid'";
$result2 = mysql_query($sql_query2);
$rows2 = mysql_fetch_array($result2);
if($rows2[type]==$orderby)
{
/* trial */
$qq = mysql_query("SELECT id,name,image,description,type,upcost FROM itemdata WHERE `id`='$itemid'");
$quantity = mysql_result($qq,0);
echo "<a href=viewitem.php?id=$id>$rows2[name]</a> - Type: $rows2[type] - Strength: $rows2[upcost] - Used: <b>$used</b> - Quantity: ". $rows[count(itemid)]." | Quantity 2: $quantity<br>";
} ?>