Jump to content

Archived

This topic is now archived and is closed to further replies.

gijew

Query not functioning as intended... *SOLVED*

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?

Share this post


Link to post
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!

Share this post


Link to post
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!

Share this post


Link to post
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  ;)

Share this post


Link to post
Share on other sites
you have couple of stray " ' " in there

[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 <> '69.239.245.135'
GROUP BY page ORDER BY counter DESC
LIMIT 0, 100
[/code]

Share this post


Link to post
Share on other sites
That did it.  Works perfect now.  I see the changes you made so thanks you two for the help...

Share this post


Link to post
Share on other sites

×

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.