Jump to content

Query not functioning as intended... *SOLVED*


gijew

Recommended Posts

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?
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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_increment
ip varchar(15)
browser varchar(255)
page varchar(255)
referrer varchar(255)
date_received  datetime

Thanks for taking the time to help  ;)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.