Reputation: 13
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 :)
Upvotes: 1
Views: 100
Reputation: 1437
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
Upvotes: 2
Reputation: 26871
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
Upvotes: 0