Number of items per category in sql table

Question

How do i find how many items there are per unique user in a data base

So say i have hats gloves and lots of other elements in my data base that users have added

how would i find how much each one had

e.g. hats- 35 t-shirts -50

but the problem is the categories would change so it would have to get all the categories then do an MySQL search on each of them... i have done lots of different MySQL tries and they have all failed here is one

$sql = "SELECT COUNT(username) FROM cards GROUP BY username";
$query = mysqli_query($db_conx, $sql);
$row = mysqli_fetch_row($query);
$users = $row[0];

and i dont know where to go from here

Can you post more SQL-Information? How are you Tables structured, how can you select all tshirts or hats? or what is a card etc? — Visionstar, Feb 09 '14 at 16:58

score 0 · Answer 1 · answered Feb 09 '14 at 16:58

0

This is how you can do

select `item`,count(*) as `total` from `test` 
group by `item`

check here

http://www.sqlfiddle.com/#!2/19250/1

You can change the query to have your field names in the query

answered Feb 09 '14 at 16:58

Abhik Chakraborty

44,654
6
52
63

does total become a variable? – Christopher Craven Feb 09 '14 at 17:05
its not a variable its alias so you can get that inside the while loop as while ($row = mysqli_fetch_row($query) ) { echo "item : ".$row["item"]." Count :: ".$row["total"] ; } – Abhik Chakraborty Feb 09 '14 at 17:11
Thank you :D but in that bit you have to use $row[1] becuase its the second perameter and not used as MYSQLI_ASSOC – Christopher Craven Feb 09 '14 at 17:27
can u post the table and some values in sqlfiddle.com and what you want to get ? – Abhik Chakraborty Feb 09 '14 at 17:33

Number of items per category in sql table

1 Answers1