Page 1 of 1

Getting the quantity of a distinct column

Posted: Thu Jul 02, 2009 1:41 pm
by annnthony
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.

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>";
        } ?>

Re: Getting the quantity of a distinct column

Posted: Thu Jul 02, 2009 2:43 pm
by BornForCode
Do this from SQL, do not bother to calculate it from php, add in your sql : count(item) as itemsOwned .... group by item

Re: Getting the quantity of a distinct column

Posted: Thu Jul 02, 2009 2:51 pm
by Randwulf
EDIT: Well I wrote up a big fancy post, but probably none of what I suggested would have worked because I'm afraid I'm having a hard time following the logic of the script out of context. How do the 'inventory' and 'itemdata' tables interact? Couldn't you just put a quantity column in?

Also, to count the number of rows in a resource from a MySQL db, you use mysql_num_rows(resource).

Re: Getting the quantity of a distinct column

Posted: Thu Jul 02, 2009 9:03 pm
by annnthony
OK. The table itemdata keeps a record of all available types of items. The inventory table keeps track of what items each user has. Once upon a time a few years ago when this was first put into effect, every item available was unique so I didn't keep track of quantity as a column. This was eventually changed so that a limited number of items were available, but I never altered the inventory table to keep track of the numbers. Continuing to insert new rows whenever a new item was purchased was the fastest solution at the time. The inventory table has thousands of rows, now, and it would be an ordeal to go through the rest of the site and find all the places items can be made/bought and replace them with quantity=quantity+'1' 's.

The inventory table is really the one that I need to figure out how to work with as I'm pretty sure I know what to do once that's taken care of. When users look at their inventory, they see every single item they own, including duplicates. The end result is to try and get things streamlined so that they see one of each item and are able to see how many of that item is in their inventory at that time.

Re: Getting the quantity of a distinct column

Posted: Fri Jul 03, 2009 12:46 am
by annnthony
just bumping to try and get some more feedback.

Re: Getting the quantity of a distinct column

Posted: Sat Jul 04, 2009 10:21 am
by annnthony
Merh. Bumping one last time. Sorry & thanks.