Jump to content


Photo

Query not functioning as intended... *SOLVED*


  • Please log in to reply
6 replies to this topic

#1 gijew

gijew
  • Members
  • PipPipPip
  • Advanced Member
  • 240 posts
  • LocationCalifornia

Posted 09 October 2006 - 09:02 PM

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).

The query
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

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?
I'm considered good looking in some countries

#2 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 09 October 2006 - 09:29 PM

Try using OR for the page NOT LIKE and only use AND for the ip NOT LIKE

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


me!

#3 gijew

gijew
  • Members
  • PipPipPip
  • Advanced Member
  • 240 posts
  • LocationCalifornia

Posted 09 October 2006 - 09:49 PM

Same results =/
I'm considered good looking in some countries

#4 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 09 October 2006 - 10:02 PM

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!

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
-------------------------------------------------------------------------------------------------------------------------------^----------------------------------^


change to....

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;" );


me!

#5 gijew

gijew
  • Members
  • PipPipPip
  • Advanced Member
  • 240 posts
  • LocationCalifornia

Posted 09 October 2006 - 10:20 PM

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...

Table scheme:
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  ;)
I'm considered good looking in some countries

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 09 October 2006 - 10:23 PM

you have couple of stray " ' " in there

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 gijew

gijew
  • Members
  • PipPipPip
  • Advanced Member
  • 240 posts
  • LocationCalifornia

Posted 09 October 2006 - 10:27 PM

That did it.  Works perfect now.  I see the changes you made so thanks you two for the help...
I'm considered good looking in some countries




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users