Filtering SQL results

Question

I would like to show a filtered result to a few ip's that keep scraping my content. I have blocked them with .htaccess and they change their ip address and continue doing it. So I thought, I want to create a soft block that won't show them all of my content and hopefully they won't even notice.

My table has a auto_increment field

 id | category |  everything else
 1       1
 2       1
 3       4
 4       2

I have been trying something like this.

SELECT * from mytable WHERE `category` = '1' having avg(id/3) = 1 ORDER BY `id` DESC LIMIT 0 , 10

I have searched forever but I am a newb to sql, so I don't even really know what I am searching for. I hope somebody here can please help me! Thanks :)

I want to get only every ID that is divisible by 3 that equals 1, so like ID 1, 4, 7, 10, 13, 16 etc. If I show them a static page they will know something is up because I usually update a couple times a day. — steve, Jun 28 '11 at 04:03
@steve So make it a dynamic page that just generates random values. — Phil, Jun 28 '11 at 04:06
@Phil I'm not sure what you mean? If it's random, the content will change with every page refresh. — steve, Jun 28 '11 at 04:08
possible duplicate of [How do you select every n-th row from mysql](http://stackoverflow.com/questions/858746/how-do-you-select-every-n-th-row-from-mysql) — Phil, Jun 28 '11 at 04:09

score 2 · Accepted Answer · edited Jun 28 '11 at 05:30

2

If you want to get remainder of division by 3, you should use % operator.

SELECT * from mytable WHERE `category` = '1' and id % 3 = 1 ORDER BY `id` 
DESC LIMIT 0 , 10

edited Jun 28 '11 at 05:30

Conrad Frix

51,984
12
96
155

answered Jun 28 '11 at 04:13

Greenisha

1,417
10
14

score 0 · Answer 2 · answered Jun 28 '11 at 04:11

Generally, the ID column is not for doing computations on it. It does not represent anything other than unique identifier of the record (at most, it should be used to sort the records chronologically) - you could have there GUIDs for example, and your application should work.

If you want to store the IPs that you want to block in your DB, consider adding another column to your table, call it status or something similar, and store in this column the status for that ip - this status could be clean, suspicious, blocked, etc. After that, your SELECT should look only after the rows with blocked status

Thanks! I like your suggestion and will do this if the above is not possible. — steve, Jun 28 '11 at 04:15

Filtering SQL results

2 Answers2