Math behind dynamic SQL statement

[RyanReese]

Been trying to figure out the best way to set this variables value without resorting to switch statements.

Basically I’m setting up a pagination script. I’m dynamically doing the SQL statement (e.g. on page 1, I’ll have a variable set that will set the lower value to “1” and the max value to “10” thus I’ll be pulling records 1-10.

However this value needs to appropriately update for page 2/3.

$minPage=$_GET['page'];
$maxPage=$_GET['page']+9;
$getData = mysqli_query($connection, "SELECT * FROM `Articles` WHERE ArticleID>=1 AND ArticleID<=10 ORDER BY ArticleID ASC");

I’ll be replacing “1” and “10” with the minPage/maxPage. Ignore the values I prematurely set for minPage/maxPage.

I’m having trouble thinking of hte math to make this work in any situation. With $_GET[‘page’] === 1, It’ll need to pull 1-10, If it equals 2, it needs 11-20.

How should I approach this? Will I need to do switches (or if statements?)

[Mittineague]

I would do a “SELECT … LIMIT $offset, 10” type of thing and pass it the offset value

http://dev.mysql.com/doc/refman/5.0/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

[RyanReese]

Thanks. I actually did figure out the logic for the maxPage but not the min. For maxPage, I take $_GET[‘page’] and then add the string “0” to it. So 1 would be 10. 2, 20…etc.

Still needed an IF/ELSE statement for “1” though to do minPage (if 1, then set value to 1…else make minPage = to $_GET[‘page’] + the string of “1”…)

I’ll look at your link and come back if I have questions. Much appreciated :).

[RyanReese]

Oh wait, I don’t need that in an if/else. I can straight up set that…Nevermind. It’s dynamic now. Here is my result.

$minPage=($getPageNum-1)."1";
$maxPage=$getPageNum."0";

Unless someone can give me a reason otherwise, I’m going to just do this (even though Mitts solutino does look pretty clean…)

[Jeff_Mott]

@RyanReese

I take $_GET[‘page’] and then add the string “0” to it.

So, multiply by 10, then? 1 * 10 would be 10. 2 * 10, 20…etc.

Or, more specifically, multiply by the number of items shown per page.

[RyanReese]

That would make sense.

In the future, as I get more articles, I’ll have an option for 10,25,50 articles (then I can implement the multiplication by number of items shown per page.)

I’m having it default at 10 because of my article count. I’ll keep your idea in mind though for when I do go this route. Thanks for the suggestion Jeff.