switchdoc Posted November 10, 2006 Share Posted November 10, 2006 Hello all, I am hoping you can help me with what I am sure is a very simple issue. I need to keep track of the date and number of times certain links are clicked. To do that I've written a little script that pushes in the linkID and the date it was visited to a mysql db when that page is visited.The DB looks something like this: [code] LinkID clickdate 604 2006-11-08 111 2006-11-08 111 2006-11-08 111 2006-11-08 229 2006-11-09 662 2006-11-09 183 2006-11-25 229 2006-11-09 183 2006-11-09 [/code]You'll note that LinkID 111 got hit 3 times on the same day. LinkID 183 got hit twice.When I output this I want to showLinkID: 111 Hits: 3LinkID: 183 Hits: 2LinkID: 229 Hits: 1etc...I can't figure out the correct way to count this out of the query.Doing a select all and running it through a while loop lets me pull everything out, but somewhere there in the middle I have to add up the duplicates. Any thoughts appreciated!!-Switch Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/ Share on other sites More sharing options...
btherl Posted November 10, 2006 Share Posted November 10, 2006 [code]SELECT LinkID, clickdate, sum(1) as HitsFROM tableGROUP BY LinkID, clickdate[/code]There you go :) Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122410 Share on other sites More sharing options...
Nicklas Posted November 10, 2006 Share Posted November 10, 2006 Try something like this[code=php:0]SELECT *, COUNT(LinkID) AS total FROM your_table GROUP BY LinkID[/code] Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122418 Share on other sites More sharing options...
btherl Posted November 10, 2006 Share Posted November 10, 2006 Oops.. you want them grouped by LinkID only right? Ignoring the date?[code]SELECT LinkID, sum(1) as HitsFROM tableGROUP BY LinkID[/code]"Select *" will not work because it selects clickdate without aggregating it (in theory anyway.. I have not tried this in mysql) Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122420 Share on other sites More sharing options...
switchdoc Posted November 10, 2006 Author Share Posted November 10, 2006 Thanks for the fast replies!I don't suppose anyone can show me how to pull this out on the other end, I am not quite sure how to echo out the clickdate (sum).Appologies in advanced and thanks!-Switch Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122421 Share on other sites More sharing options...
Nicklas Posted November 10, 2006 Share Posted November 10, 2006 [quote]"Select *" will not work because it selects clickdate without aggregating it (in theory anyway.. I have not tried this in mysql)[/quote]works for me... Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122427 Share on other sites More sharing options...
btherl Posted November 10, 2006 Share Posted November 10, 2006 Switchdoc, do you want seperate lines for each LinkID/clickdate combination (day-by-day stats), or just a seperate line for each LinkID (summary)? From your first post I thought you wanted to ignore clickdate, but now I am unsure..Nicklas, what output does it produce? The query doesn't specify which value clickdate should take after grouping.. Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122433 Share on other sites More sharing options...
switchdoc Posted November 10, 2006 Author Share Posted November 10, 2006 Well,I just went back and looked and I think I screwed you all up. The scoop is I need ALL hits for each link in a date rangeSo if the user selects 11/3 - 11/21Each time the link appears in that date range, I need to count it so I can sayLinkID 111 Hits 3LinkID 237 Hits 56Sorry for the confusion.-Switch Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122441 Share on other sites More sharing options...
Nicklas Posted November 10, 2006 Share Posted November 10, 2006 [quote]Nicklas, what output does it produce?[/quote]Im sorry, I forgot to add [i]clickdate[/i] in my query when I posted it, my query looks like this[code=php:0]SELECT *, COUNT(LinkID) AS total FROM your_table GROUP BY LinkID, clickdate[/code]The data in my database looks like this:[code]LinkID clickdate604 2006-11-08111 2006-11-08111 2006-11-08111 2006-11-08229 2006-11-09662 2006-11-09183 2006-11-25229 2006-11-09183 2006-11-09[/code]After the query, it looks like this[code]LinkID clickdate total111 2006-11-08 3183 2006-11-09 1183 2006-11-25 1229 2006-11-09 2604 2006-11-08 1662 2006-11-09 1[/code] Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122446 Share on other sites More sharing options...
switchdoc Posted November 10, 2006 Author Share Posted November 10, 2006 Hey all,Thanks for all the replies! The answer just came via an email to a buddy I'd sent out earlier. Here is what he ended up having me do so I could get the hit count and use the page name (from another table) rather than just ID:[code]$query = "select count(t1.linkid),t2.pagename from page_counter as t1 inner join pages as t2 on t2.linkid=t1.linkid where t1.clickdate>='$sd' and t1.clickdate<='$ed' group by t1.linkid";$result = mysql_query($query) OR DIE ("there was an error" .mysql_error());while ($line = mysql_fetch_array($result,MYSQL_NUM)){list($count,$name) = $line;[/code]Then I can echo out the page name ($name) and the hits($count) into a table using the while loop.Thanks again for all your replies!-Switch Link to comment https://forums.phpfreaks.com/topic/26772-solved-counting-duplicates-in-a-mysql-query/#findComment-122463 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.