Adding details from a table together.

[matty]

Hi,

I have a list of items, this is for a game im currently working on.

When a user buys an item it inserts it into a table called 'equipment'.

When the user goes to thier items, it list all the items from 'equipment' in a table. What I need help with is, if the user brought two of the same item it would come up in different rows in the (HTML) table, what I want it to do instead is add them together so it will only display one row, but it will have x2 by its name.

 

Is there anyway to do this, not altering the database is a perference.

 

Many thanks, Matt

[pocobueno1388]

Hello Nauir [i think], ignore that if it isn't you.

 

In your query you need to put:

 

GROUP BY item_name, that will eliminate it showing the item more than once.

 

Also in the query you need to select the sum to give you the amount of items that are the same.

 

SELECT COUNT(itemID) AS total FROM tbl WHERE condition GROUP BY item_name

 

That would be your final query.

[matty]

Thanks, I can understand all that except... What do you mean by itemid?

 

SELECT COUNT(itemID) AS total FROM tbl WHERE condition GROUP BY item_name

[MadTechie]

Each item should have a unique referance number normally called ID so it will relate to the unique field in your equipment table

[matty]

Thanks for that, I figured that was it =)

[matty]

Ive done it but its not working correctly.

 

Here a section of my code.

 

$weapons = mysql_query("SELECT COUNT(id) AS total FROM pequipment WHERE userid='$playerinfo[id]' AND type='weapon' GROUP BY name");
// We are going to use the "$row" method for this query. This is just my preference.

while ($row = mysql_fetch_array($weapons)) {
    $wep_name = $row["name"];
    $wep_type = $row["iclass"];
    
    $sellcost = $row[cost] / 100 * 40;
    $sellamo = ceil($sellcost);
    
    $wep_sell = number_format($sellamo);
    

 

It comes up as if i have two of one item, but it will not display the name, or anything else? Is my query wrong?

 

Thanks, Matt

[MadTechie]

change

<?php
$weapons = mysql_query("SELECT COUNT(id) AS total FROM pequipment WHERE userid='$playerinfo[id]' AND type='weapon' GROUP BY name");
?>

 

to

<?php
$weapons = mysql_query("SELECT *, COUNT(id) AS total FROM pequipment WHERE userid='$playerinfo[id]' AND type='weapon' GROUP BY name");
?>

 

Note the *,

 

 

OR

 

<?php
$weapons = mysql_query("SELECT COUNT(id) AS total, name, iclass FROM pequipment WHERE userid='$playerinfo[id]' AND type='weapon' GROUP BY name");
?>

 

will be a little quicker

[matty]

Thanks for the quick reply, that worked great.

 

Is there anyway I can display how many items are grouped together?

 

All it displays at the minute is just the name.

 

Cheers

[pocobueno1388]

You would display how many by echoing this out:

 

echo $row['total'];