PHP help, how do I count with a condition and 3 records per ID? A Challenge!

[RJP1]

Hi guys,

 

I'm stuck... I need to count users who meet certain conditions on this table:

 

Code:

 

user_id | field_id | value
---------------------------
1       |     1    |   UK
1       |     2    |   Yes
1       |     3    |   1
2       |     1    |   USA
2       |     2    |   No
2       |     3    |   2

 

As you can see, users are grouped in 3's (one row per field). How do I count users who meet these conditions?

 

a) if country (field 1) = specified country AND field 2 = Yes ---> counted as 1

b) if country (field 1) = specified country AND field 2 = Yes AND field 3 = specified number ---> counted as 1

 

Output should be total of a) and b)

 

If anyone could help me with this, that would be awesome! I need the MySQL and PHP statements...

 

Thanks! RJP1

[gizmola]

I don't have time to spoonfeed out all the code, but the simple answer from the SQL side, is that you can count(DISTINCT user_id).  The rest of that is simply constructing the criteria with the appropriate AND and OR's needed.

[Psycho]

This may work. Since I don't have your database and am too lazy to create one, I can't test it. If I am correct the generated query should return a result set with each user ID and their appropriate total. (be sure to replace "table" with the appropriate table name)

 

$country = "USA";
$number  = "1";

$query = "SELECT user_id,
                 SUM (
                   IF (country='{$country}' AND bool='Yes', 1, 0),
                   IF (country='{$country}' AND bool='Yes' AND number='{$number}', 1, 0)
                 ) as total
          FROM (SELECT user_id, value as country
                FROM table
                WHERE field_id = 1) as t1
          JOIN (SELECT value as bool
                FROM table
                WHERE field_id = 2) as t2
            ON t1.user_id = t2.user_id
          JOIN (SELECT value as number
                FROM table
                WHERE field_id = 3) as t3
            ON t1.user_id = t3.user_id";