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?

 

$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.