SchweppesAle Posted December 22, 2009 Share Posted December 22, 2009 hi, I was wondering if there's a function which allows me to do this. Basically, I have the following query $query = "SELECT #__jt_banners.id , #__jt_banners.title , #__jt_banners.img_location , #__jt_banners.impressions , #__jt_banners.start_date , #__jt_banners.expiration_date , #__jt_banners.published , #__jt_banner_clicks.id , #__jt_banner_clicks.banner_id FROM #__jt_banners LEFT JOIN #__jt_banner_clicks ON (#__jt_banners.id = #__jt_banner_clicks.banner_id)"; Depending on the id of the banner, some will have entries within the banner_clicks table, some will have none. I need a way of counting the resulting clicks per banner, is there an easy way of doing this? Sort of like mysql_num_rows() except I would specify two required columns, an "id" entry within __jt_banners and an entry "banner_id" within banner_clicks. Quote Link to comment https://forums.phpfreaks.com/topic/185955-left-join-count-resulting-rows-per-attribute-where-blah-blah/ Share on other sites More sharing options...
SchweppesAle Posted December 22, 2009 Author Share Posted December 22, 2009 alright, maybe I should clarify this. I have the following query. $query = "SELECT #__jt_banners.id , #__jt_banners.title , #__jt_banners.img_location , #__jt_banners.impressions , #__jt_banners.start_date , #__jt_banners.expiration_date , #__jt_banners.published FROM #__jt_banners"; I need to count the number of times each (#__jt_banners.id = #__jt_banner_clicks.banner_id) Some banner entries will have clicks, some won't. I'd like to limit this to one query if possible, how should I go about doing this? Quote Link to comment https://forums.phpfreaks.com/topic/185955-left-join-count-resulting-rows-per-attribute-where-blah-blah/#findComment-981968 Share on other sites More sharing options...
sasa Posted December 22, 2009 Share Posted December 22, 2009 try $query = "SELECT #__jt_banners.id , #__jt_banners.title , #__jt_banners.img_location , #__jt_banners.impressions , #__jt_banners.start_date , #__jt_banners.expiration_date , #__jt_banners.published , #__jt_banner_clicks.id , #__jt_banner_clicks.banner_id , COUNT(#__jt_banner_clicks.banner_id) as bann_cnt FROM #__jt_banners LEFT JOIN #__jt_banner_clicks ON (#__jt_banners.id = #__jt_banner_clicks.banner_id) GROUP BY #__jt_banners.id"; Quote Link to comment https://forums.phpfreaks.com/topic/185955-left-join-count-resulting-rows-per-attribute-where-blah-blah/#findComment-982061 Share on other sites More sharing options...
SchweppesAle Posted December 22, 2009 Author Share Posted December 22, 2009 man, so much for dbforums. lol thanks, that worked perfectly Quote Link to comment https://forums.phpfreaks.com/topic/185955-left-join-count-resulting-rows-per-attribute-where-blah-blah/#findComment-982130 Share on other sites More sharing options...
SchweppesAle Posted December 22, 2009 Author Share Posted December 22, 2009 :slams head on keyboard: alright, this one's kind of weird. when i plug the query into phpmyadmin I can clearly see that there is in fact an id associated with each row, however when I run it through mysql_fetch_array or the joomla semi-equivalent "JFactory::loadObjectList", the only row which has an id is the first "0". if I use var_dump the results returned by msql_fetch_array there's only one ID of 0 and the other entries have an ID of NULL. Any idea what would cause this? $query = "SELECT jos_jt_banners.id , jos_jt_banners.title , jos_jt_banners.img_location , jos_jt_banners.impressions , jos_jt_banners.start_date , jos_jt_banners.expiration_date , jos_jt_banners.published , jos_jt_banner_clicks.id , jos_jt_banner_clicks.banner_id , COUNT(jos_jt_banner_clicks.banner_id) as bann_cnt FROM jos_jt_banners LEFT JOIN jos_jt_banner_clicks ON (jos_jt_banners.id = jos_jt_banner_clicks.banner_id) GROUP BY jos_jt_banners.id" Quote Link to comment https://forums.phpfreaks.com/topic/185955-left-join-count-resulting-rows-per-attribute-where-blah-blah/#findComment-982135 Share on other sites More sharing options...
SchweppesAle Posted December 22, 2009 Author Share Posted December 22, 2009 so you guys know I'm not making this up vardumped results. This is for loadObjectList. array(3) { [0]=> object(stdClass)#90 (9) { ["id"]=> string(1) "0" ["title"]=> string(4) "Test" ["img_location"]=> string(73) "http://darkerprojects.dreamhosters.com/images/TheFalconBanner-PodCast.gif" ["impressions"]=> string(1) "0" ["start_date"]=> string(19) "2009-12-21 09:33:24" ["expiration_date"]=> string(19) "2009-12-21 09:33:24" ["published"]=> string(1) "1" ["banner_id"]=> string(1) "0" ["bann_cnt"]=> string(1) "2" } [1]=> object(stdClass)#88 (9) { ["id"]=> NULL ["title"]=> string(11) "Flag Banner" ["img_location"]=> string(56) "http://www.si.edu/encyclopedia_Si/nmah/images/banner.jpg" ["impressions"]=> string(1) "0" ["start_date"]=> string(19) "2009-12-20 14:06:04" ["expiration_date"]=> string(19) "2009-12-19 14:06:04" ["published"]=> string(1) "0" ["banner_id"]=> NULL ["bann_cnt"]=> string(1) "0" } [2]=> object(stdClass)#85 (9) { ["id"]=> NULL ["title"]=> string(16) "Make that Dollar" ["img_location"]=> string(63) "http://www.makethatdollar.com/images/462/banners_mtd_728x90.gif" ["impressions"]=> string(1) "0" ["start_date"]=> string(19) "2009-12-21 14:09:58" ["expiration_date"]=> string(19) "2009-12-21 14:09:58" ["published"]=> string(1) "0" ["banner_id"]=> NULL ["bann_cnt"]=> string(1) "0" } } mysql_fetch_array(after second run. notice the id is null here as well) array(19) { [0]=> string(1) "1" ["id"]=> NULL [1]=> string(11) "Flag Banner" ["title"]=> string(11) "Flag Banner" [2]=> string(56) "http://www.si.edu/encyclopedia_Si/nmah/images/banner.jpg" ["img_location"]=> string(56) "http://www.si.edu/encyclopedia_Si/nmah/images/banner.jpg" [3]=> string(1) "0" ["impressions"]=> string(1) "0" [4]=> string(19) "2009-12-20 14:06:04" ["start_date"]=> string(19) "2009-12-20 14:06:04" [5]=> string(19) "2009-12-19 14:06:04" ["expiration_date"]=> string(19) "2009-12-19 14:06:04" [6]=> string(1) "0" ["published"]=> string(1) "0" [7]=> NULL [8]=> NULL ["banner_id"]=> NULL [9]=> string(1) "0" ["bann_cnt"]=> string(1) "0" } Quote Link to comment https://forums.phpfreaks.com/topic/185955-left-join-count-resulting-rows-per-attribute-where-blah-blah/#findComment-982137 Share on other sites More sharing options...
SchweppesAle Posted December 22, 2009 Author Share Posted December 22, 2009 nevermind, there was an extra id attribute I didn't notice SELECT jos_jt_banners.id , jos_jt_banners.title , jos_jt_banners.img_location , jos_jt_banners.impressions , jos_jt_banners.start_date , jos_jt_banners.expiration_date , jos_jt_banners.published , jos_jt_banner_clicks.banner_id , COUNT(jos_jt_banner_clicks.banner_id) as bann_cnt FROM jos_jt_banners LEFT JOIN jos_jt_banner_clicks ON (jos_jt_banners.id = jos_jt_banner_clicks.banner_id) GROUP BY jos_jt_banners.id Quote Link to comment https://forums.phpfreaks.com/topic/185955-left-join-count-resulting-rows-per-attribute-where-blah-blah/#findComment-982142 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.