RJP1 Posted June 23, 2010 Share Posted June 23, 2010 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 make a conditional mysql statement where if all 3 fields (country, yes, ID) = the specified reference OR country and yes are true. It must prefer the 3 statements being true, then if the 2 are true... (in other words, if either statement is true, count = 1). Here is what I have so far: $sql = "SELECT COUNT(*) FROM `fields_values` AS tab1, `fields_values` AS tab2, `values` AS tab3, `fields_values` AS tab4 WHERE tab1.user_id = tab2.user_id AND tab2.user_id = tab3.user_id AND tab3.user_id = tab4.user_id AND (tab1.field_id = 11 AND tab1.value = '$item->website') AND (tab2.field_id = 16 AND tab2.value= 'Yes') AND (tab3.field_id = 17 AND tab3.value= '$item->id') AND (tab4.field_id = 18 AND tab4.value>='$date_first' AND tab4.value<='$date_last')"; Thanks for the help! RJP1 Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/ Share on other sites More sharing options...
hamza Posted June 23, 2010 Share Posted June 23, 2010 start learning joins INNER , OUTER < LEFT < RIGHT EQUI and few others Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1076252 Share on other sites More sharing options...
RJP1 Posted June 23, 2010 Author Share Posted June 23, 2010 Thanks for the point in the right direction hamza, really appreciated. I'll have a look! Cheers, RJP1 Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1076255 Share on other sites More sharing options...
RJP1 Posted June 23, 2010 Author Share Posted June 23, 2010 I had a look at joins and it seems it's for multiple tables... the issue I have here is a single table but with a condition. Can anyone suggest a method of solving my problem? Thanks, RJP1 Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1076320 Share on other sites More sharing options...
fenway Posted June 24, 2010 Share Posted June 24, 2010 I don't follow -- you want to ensure that all 3 conditions are met? Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1076393 Share on other sites More sharing options...
RJP1 Posted June 24, 2010 Author Share Posted June 24, 2010 Hi Fenway, At the moment all 3 conditions must be met in order to be counted as +1. I'd like it to count +1 if all 3 are met, or if 2 conditions are met, count still = 1. Is that doable? Cheers, RJP1 Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1076531 Share on other sites More sharing options...
fenway Posted June 24, 2010 Share Posted June 24, 2010 Everything is "doable". But what's the difference between 1 and +1? Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1076630 Share on other sites More sharing options...
RJP1 Posted June 24, 2010 Author Share Posted June 24, 2010 Sorry, no difference. If either is true, the count should be "1"... hard to explain, sorry! RJP1 Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1076663 Share on other sites More sharing options...
fenway Posted June 26, 2010 Share Posted June 26, 2010 So either 2 or 3 results in "true"? A specific 2, or any 2? Link to comment https://forums.phpfreaks.com/topic/205658-help-with-mysql-conditional-queries/#findComment-1077661 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.