Hi all, I'm basically trying to get a count of banner ad impresssions and banner ad click-thrus from phpAdsNew into a simple report on another site to figure out how to accurately get the stats to pass onto php and smarty to output onto a webpage for a user to see their stats on an application I'm working on:
mysql> SELECT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(DISTINCT phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid=phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +----------------------------------+----------------------------------+----- ----+----------+ | md5_url | md5_status | adviews | adclicks | +----------------------------------+----------------------------------+----- ----+----------+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 615 | 616 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1147 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 691 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 607 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1192 | 1212 | +----------------------------------+----------------------------------+----- ----+----------+ 5 rows in set (0.56 sec) mysql> SELECT DISTINCT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(DISTINCT phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid=phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +----------------------------------+----------------------------------+----- ----+----------+ | md5_url | md5_status | adviews | adclicks | +----------------------------------+----------------------------------+----- ----+----------+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 615 | 616 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1147 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 691 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 607 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1192 | 1212 | +----------------------------------+----------------------------------+----- ----+----------+ 5 rows in set (0.53 sec) mysql> SELECT DISTINCT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid =phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +----------------------------------+----------------------------------+----- ----+----------+ | md5_url | md5_status | adviews | adclicks | +----------------------------------+----------------------------------+----- ----+----------+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 616 | 0 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1165 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 694 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 0 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1212 | 0 | +----------------------------------+----------------------------------+----- ----+----------+ 5 rows in set (0.17 sec) This query gives me better results, but where there are more than 1 addclick it gives me other issues: mysql> SELECT DISTINCT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(phpads_adviews.t_stamp) AS adviews, COUNT(DISTINCT phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid=phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +----------------------------------+----------------------------------+----- ----+----------+ | md5_url | md5_status | adviews | adclicks | +----------------------------------+----------------------------------+----- ----+----------+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 616 | 0 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1165 | 1 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 694 | 1 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 0 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1212 | 0 | | 0be71eefef03f2d4fa24ecf9c20a2b0b | 974dd092d580c3c355f627c1ed683603 | 3537 | 3 | | 564a56a8db7efdbf03cb4e77058b1dd6 | fb421588e2c776634f858d3d96cbe6c6 | 1123 | 1 | | 45d5498e742afb4ef4d5fb186c7f080f | 05926606c997edafa74cd22c80d2c7dd | 661 | 1 | | af731abb3ef788dccd61de6e8ac5d33e | c0fa9197f53c946d89fb5ada843b2409 | 1171 | 1 | | 7f5255f12907c6628565dc3a87aa2c4b | c07b1979ea621f831f599f55ba60b989 | 1185 | 1 | | f90f775b1a5a6f3c674d2a8c9d22e017 | c8ba056407795988f00b5253727cd8bb | 2306 | 2 | | c756b6b3cbde805c1d0ca7c611b2ab2b | 67ecd901b1060bce7245a74bfab54454 | 2320 | 2 | | fae8120035f5efae05110faabbfb572e | 773722a45a8aebf4f4bbce0694599be9 | 311 | 0 | | 97a6698b28ea91a6a76efac175bbfeaf | d6b744f3d7563862e5b25e5cc803eee5 | 636 | 1 | ... +----------------------------------+----------------------------------+----- ----+----------+ 33 rows in set (0.53 sec) Any pointers on how to get this working correctly would be appreciated. Regards --jm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
