I have a table in which each entry has a view counter. I would like to create a list that displays only entries that are within 100 views of a 1,000 view threshold. But, I want to also show each entry at any interval of 1,000 views. I.E., I would want to show all the following entries:

View Count:
975
1025
1980
2020
4960
5045
9990
10030
45980
46020
etc...

I am not quite sure the best way to do this, or if it is possible, in a MYSQL query.

I was thinking something along the lines of dividing each view count by 1000, and if the result is between x.95 - x.05 showing the row.

Any help on how to implement this, or a better solution, would be appreciated.

I have a table in which each entry has a view counter. I would like to create a list that displays only entries that are within 100 views of a 1,000 view threshold. But, I want to also show each entry at any interval of 1,000 views. I.E., I would want to show all the following entries:

View Count:
975
1025
1980
2020
4960
5045
9990
10030
45980
46020
etc...

I am not quite sure the best way to do this, or if it is possible, in a MYSQL query.

I was thinking something along the lines of dividing each view count by 1000, and if the result is between x.95 - x.05 showing the row.

Any help on how to implement this, or a better solution, would be appreciated.

This should do it:

``SELECT *,ROUND(views/1000,0) intervals FROM poll_questions WHERE views >= (ROUND(views/1000,0) * 1000 - 100) and views <= (ROUND(views/1000,0) * 1000 + 100) ORDER BY intervals ASC, views ASC``

Here I've assumed "views" is the name of the column holding the views.

I have tested this code and it works.

This should do it:

``SELECT *,ROUND(views/1000,0) intervals FROM poll_questions WHERE views >= (ROUND(views/1000,0) * 1000 - 100) and views <= (ROUND(views/1000,0) * 1000 + 100) ORDER BY intervals ASC, views ASC``

Here I've assumed "views" is the name of the column holding the views.

I have tested this code and it works.

Thanks, I tested this and it works. The only bug is that it will also return any row with views <= 100, but this is fixed simply by modifying the code to exclude views <= 100.

I ended up using:

``SELECT * FROM table WHERE views >= (ROUND(views/1000,0) * 1000 - 100) AND views <= (ROUND(views/1000,0) * 1000 + 100) AND views > 100 ORDER BY views DESC``

I also wrote a PHP function to accomplish a similar goal -- determine if a number is within a given range of a given threshold:

``````function near_threshold(\$num,\$threshold,\$range){
\$range = \$range/\$threshold;
\$a = \$num/\$threshold;
\$b = round(\$a, 0);
\$c = \$a-\$b;
\$d = abs(\$c);

if(\$d < \$range && \$b != 0){
return TRUE;
}
else
return FALSE;
}``````

Yeah, true I did notice it returned the 0-rated ones.

I ended up using:

``SELECT * FROM table WHERE views >= (ROUND(views/1000,0) * 1000 - 100) AND views <= (ROUND(views/1000,0) * 1000 + 100) AND views > 100 ORDER BY views DESC``

That would work, though I would have chosen AND (ROUND(views/1000,0)) > 0 as my condition.

Either or - it's just a matter of preference I guess.