Getting the quantity of a distinct column

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
annnthony
Forum Newbie
Posts: 10
Joined: Mon Jun 30, 2008 11:44 pm

Getting the quantity of a distinct column

Post 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>";
        } ?>
BornForCode
Forum Contributor
Posts: 147
Joined: Mon Feb 11, 2008 1:56 am

Re: Getting the quantity of a distinct column

Post 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
Randwulf
Forum Commoner
Posts: 63
Joined: Wed Jan 07, 2009 7:07 am

Re: Getting the quantity of a distinct column

Post 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).
annnthony
Forum Newbie
Posts: 10
Joined: Mon Jun 30, 2008 11:44 pm

Re: Getting the quantity of a distinct column

Post 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.
annnthony
Forum Newbie
Posts: 10
Joined: Mon Jun 30, 2008 11:44 pm

Re: Getting the quantity of a distinct column

Post by annnthony »

just bumping to try and get some more feedback.
annnthony
Forum Newbie
Posts: 10
Joined: Mon Jun 30, 2008 11:44 pm

Re: Getting the quantity of a distinct column

Post by annnthony »

Merh. Bumping one last time. Sorry & thanks.
Post Reply