gijew Posted October 9, 2006 Share Posted October 9, 2006 I'm "attempting" to pull some data from a stats table we have in our database and I got a wee bit concerned when a few numbers didn't seem to match up. I shortened the amount of records returned so I could actually count them by eye. I was right, they don't match up. Basically (and as I hope you may see) I am trying to count the amount of times a page has been hit, group by that page, exclude an ip range and I'm also using pagination (hence the limit clause).[b]The query[/b][code]SELECT page, count(page) as counter FROM stats WHERE page NOT LIKE '%.gif%' AND page NOT LIKE '%.css%' AND page NOT LIKE '%.js%' ' AND ip NOT LIKE "69.239.245.135" ' GROUP BY page ORDER BY counter DESC LIMIT 0, 100[/code]From what i've counted it is returning all rows...period for each page. It's not excluding the ip and I'm not getting any errors. Any clues on what may be the problem? Quote Link to comment Share on other sites More sharing options...
printf Posted October 9, 2006 Share Posted October 9, 2006 Try using OR for the [b]page NOT LIKE[/b] and only use AND for the [b]ip NOT LIKE[/b][code]SELECT page, count(page) as counter FROM stats WHERE page NOT LIKE '%.gif%' OR page NOT LIKE '%.css%' OR page NOT LIKE '%.js%' ' AND ip NOT LIKE "69.239.245.135" ' GROUP BY page ORDER BY counter DESC LIMIT 0, 100[/code]me! Quote Link to comment Share on other sites More sharing options...
gijew Posted October 9, 2006 Author Share Posted October 9, 2006 Same results =/ Quote Link to comment Share on other sites More sharing options...
printf Posted October 9, 2006 Share Posted October 9, 2006 if your just trying to not match page endings then use '%.css', '%.gif', '%.js'!Also can you please show db scheme.And what all these things![code]SELECT page, count(page) as counter FROM stats WHERE page NOT LIKE '%.gif%' OR page NOT LIKE '%.css%' OR page NOT LIKE '%.js%' ' AND ip NOT LIKE "69.239.245.135" ' GROUP BY page ORDER BY counter DESC LIMIT 0, 100-------------------------------------------------------------------------------------------------------------------------------^----------------------------------^[/code]change to....[code]mysql_query ( "SELECT page, count(page) as counter FROM stats WHERE page NOT LIKE '%.gif' OR page NOT LIKE '%.css' OR page NOT LIKE '%.js' AND ip != '69.239.245.135' GROUP BY page ORDER BY counter DESC LIMIT 0, 100;" );[/code]me! Quote Link to comment Share on other sites More sharing options...
gijew Posted October 9, 2006 Author Share Posted October 9, 2006 I originally tried seperating the exclusions for the page extensions by a comma but the script failed everytime so that's why I have them seperated like that.As for those out of wack apostrophes I don't know where they appeared but those suckas are gone now. Wish that would have solved it though...[b]Table scheme:[/b]id int(11) auto_incrementip varchar(15)browser varchar(255)page varchar(255)referrer varchar(255)date_received datetimeThanks for taking the time to help ;) Quote Link to comment Share on other sites More sharing options...
Barand Posted October 9, 2006 Share Posted October 9, 2006 you have couple of stray " ' " in there[code]SELECT page, count(page) as counterFROM statsWHERE page NOT LIKE '%.gif%'AND page NOT LIKE '%.css%'AND page NOT LIKE '%.js%'AND ip <> '69.239.245.135'GROUP BY page ORDER BY counter DESCLIMIT 0, 100[/code] Quote Link to comment Share on other sites More sharing options...
gijew Posted October 9, 2006 Author Share Posted October 9, 2006 That did it. Works perfect now. I see the changes you made so thanks you two for the help... Quote Link to comment 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.