CODEWITHSUNDEEP
mysqlsql
steve
steve

Reputation: 13

Filtering SQL results

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

Answers (2)

Greenisha
Greenisha

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

Tudor Constantin
Tudor Constantin

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

Related Questions