[RESOLVED] distinct and count

[Desbrina]

I'm trying to get a system where it counts how many ids you have match the existing ones, which works fine, unless you have 2 or more of one ID, then i have problems

SELECT count( * ) AS total
				FROM user_inventory
				WHERE card_id =".$ID1."
				OR card_id =".$ID2."
				OR card_id =".$ID3."
				OR card_id =".$ID4."
				OR card_id =".$ID5."
				AND status != 'double'

I tried selecting distinct card_id but i still ended up with a total of 6, not 5

[Lars_Berg]

Use parentheses to get the desired operator precedence, e.g.

SELECT count( * ) AS total
   FROM user_inventory
 WHERE (card_id =".$ID1."
	OR card_id =".$ID2."
	OR card_id =".$ID3."
	OR card_id =".$ID4."
	OR card_id =".$ID5. ")"
     and status <> 'double'

[Desbrina]

it still lists 6 cards instead of 5

[rulian]

it will list the all the matches it finds with those ID's , or any isntant where status isnt double
if you are looking for how many of each ID there are you have to group

SELECT `card_id`, count(`card_id`) AS `total`
FROM user_inventory 
WHERE 
	(
		`card_id` =".$ID1." 
		OR `card_id` =".$ID2."
		OR `card_id` =".$ID3." 
		OR `card_id` =".$ID4." 
		OR `card_id` =".$ID5."
 	) 
	AND status <> 'double'
GROUP BY `card_id` 
ORDER BY `total` DESC

[Desbrina]

i'm not looking for how many of each ID
This example might explain it more since me and explaining things dont seem to work lol

IE: The IDs it checks against are (from the database game_doyouhave)
ID1 || ID2 || ID3 || ID4 || ID5
210 || 314 || 2703 || 610 || 9

I have (from user_inventory)
9 || 210 || 314 || 2703 || 610 || 9

Therefore the script is matching 6, since i have 9 twice.

9 || 314 || 2703 || 610 || 9
In that i only have 4, since it only counts 9 once

Hope that explains it better

[Lars_Berg]

SELECT count(distinct cardid)

[Desbrina]

thanks, finally got it working (With a little extra tweaking)