Topic: Help with conditional MySQL...

[RJP1]

Hi guys,

I'm a bit stuck here. I've got it mostly working but can't complete my next task. Basically, I have a table where each user has 3 fields and each field has a value.

eg
user_id | field_id | value
1           |      1     |   UK
1           |      2     |   Yes
1           |      3     |   1
2           |      1     |   USA
2           |      2     |   Yes
2           |      3     |   4

I'm trying to count users who match a few conditions. In order to count as 1, the user must be from the country specified in my loop and have a field_id 2 = Yes and field_id = specified by the loop again.

This is what I have so far - it counts just by country which is promising but I don't know how to group the user_id's together and make a conditional mysql statement. Unless it's doable by php with a general SQL statement?

Code: [Select]

$sql3 = "SELECT * FROM `jos_community_fields_values` WHERE `field_id` = '11' AND `value` = '$item->website'";
$result3 = mysql_query($sql3);
$country = mysql_num_rows($result3);
echo $country;
Thanks for your help, really appreciated!

RJP1

[fenway]

The lazy way is to use a bunch of ORs, and check a count in the having clause.

I prefer to cross join the table to itself, one for each condition.