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

Discussion in 'PHP' started by RJP1, Jun 16, 2010.

0
  1. #1
    Hi guys,

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

    user_id | field_id | value
---------------------------
1       |     1    |   UK
1       |     2    |   Yes
1       |     3    |   1
2       |     1    |   USA
2       |     2    |   No
2       |     3    |   2
    Code (markup):
    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
     
    RJP1, Jun 16, 2010 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,901
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #2
    try something like this in your sql

    $sql = "select count(tab1.user_id )
from myTable as tab1, myTable as tab2, myTable as tab3
where tab1.user_id = tab2.user_id 
and tab2.user_id = tab3.user_id
and (tab1.field_id = 1 and tab1.value = '{$specifiedCountry}')
and (tab2.field_id = 2 and tab2.value= 'Yes')";
    PHP:
    I didn't get what you were after with b) since it seemed to be covered in a).
     
    sarahk, Jun 16, 2010 IP
  3. RJP1

    RJP1 Active Member

    Messages:
    345
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #3
    Thanks for your help sarahk! Really appreciated... However, I'm not that great at mysql yet could you help a little further please?

    	$sql = "select count(user_id)
from jos_community_fields_values as tab1, myTable as tab2, myTable as tab3
where tab1.user_id = tab2.user_id
and tab2.user_id = tab3.user_id
and (tab1.field_id = 11 and tab1.value = '$item->website')
and (tab2.field_id = 16 and tab2.value= 'Yes')";
	$result = mysql_query($sql3);
    Code (markup):
    Could you explain how I use the "as" parts, and what the "tabl." and "tab2" bits are for? Are they needed, they aren't in my mysql table...

    Also, how do I output this result?

    Thanks, RJP1
     
    RJP1, Jun 16, 2010 IP
  4. RJP1

    RJP1 Active Member

    Messages:
    345
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #4
    Ah, this is because it's in a loop. It makes sense as this is counting users related to a main user. The b) section means only the main user in the specified country gets the +1, whereas if the field 3 is left blank, all main users in the specified country get +1...
     
    RJP1, Jun 16, 2010 IP
  5. RJP1

    RJP1 Active Member

    Messages:
    345
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #5
    Ah! I got it working! I had to add in the 3rd clause and it's mostly working how I'd like it to, thanks a lot sarahk!

    RJP1
     
    RJP1, Jun 16, 2010 IP
  6. roopajyothi

    roopajyothi Active Member

    Messages:
    1,302
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    80
    #6
    roopajyothi, Jun 16, 2010 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,901
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #7
    Because your data is normalised (good) we have to query the same table 3 times per user. So we tell it the first time to call itself tab1, the second time tab2 etc. That way we can join it, add conditions as if they were all separate tables.

    The output, in my version, is a count so it should be a very simple result set that gets returned.

    All sorted now?
     
    sarahk, Jun 16, 2010 IP