Generating pages based on amount of data returned from MySql Query

Question

im making a review section for my site, and i want to generate pages based on amount of data returned by a MySQL query, something like 15 MySQL Rows pr page, i have tried looking in to the $_GET[pageID] approach, but i simply dont get it.

Could someone please elaborate or give me a suggestion? At the moment i echo Sql results in to a div on a reviews.php page.

What i have at the moment is this:

<?php
$con = mysql_connect("localhost", "cencor","cencor");
 if (!$con) {
    die('<br /><br />Could not connect: ' . mysql_error());
}

function feedbackList() {
mysql_select_db("cencor") or die(mysql_error());

$localIP = $_SERVER['REMOTE_ADDR']; // get IP

$rows = mysql_query("SELECT * FROM feedback WHERE approved=1 ORDER BY sorting DESC");

while ($row = mysql_fetch_array($rows)) {

        echo '
        <div id="reviews">
            <div id="date">'. $row['date'] . "</div> <div id='time'>" . $row['time'] .'</div>
            <h2>'. $row['name'] .'</h2>
            <br>
            <p>'. $row['feedback'] .'</p>
        </div>
        ';
        }
}       
?>

time and date are set in another script, so no worries there, this code runs fine

look [PHP MYSQL Pagination](http://stackoverflow.com/questions/2616697/php-mysql-pagination) — , Feb 04 '13 at 18:00
Please do not vandalize your old questions. While they may no longer be important to you, they may be important to others who come across them. — ceejayoz, Feb 09 '20 at 02:47
@ceejayoz; I saw the question seconds before you edited it and then the answers and I thought SO had broken, 0_0 — Scaramouche, Feb 09 '20 at 02:50

score 0 · Answer 1 · answered Feb 04 '13 at 17:56

First, get the count of all rows in your table:

SELECT COUNT(*) FROM feedback WHERE approved=1

Now you know how many pages you will have and you can display links to each page.

Query the first page:

SELECT * FROM feedback WHERE approved=1 ORDER BY sorting DESC LIMIT 0, 15

This will give you the first 15 rows from your table. On the next pages you would use:

LIMIT 15, 15
LIMIT 30, 15
LIMIT 45, 15

and so on. Just replace the first LIMIT parameter with the offset of rows you would like to have. Second LIMIT parameter is the number of rows to be returned.

score 0 · Answer 2 · answered Feb 04 '13 at 18:06

You could return all of the reviews in one SQL statement and then use PHP's array chunks function to split them into multiple arrays. http://php.net/manual/en/function.array-chunk.php You could then store each array on the page as a JS variable and use AJAX, or whatever have you.

You can pass this function an array (all of the reviews) and a number (say 15) and it will output however many possible arrays of length 5. So in your case, if you had 150 reviews in an array and you wanted to limit a page by 15 reviews, this function would return 10 arrays of 15 elements each.

You then just take each array of reviews and represent it as a page.

Alternatively, you can use LIMIT in SQL for each page. You would want something that looks like:

LIMIT 0, 15
LIMIT 15, 15
LIMIT 30, 15

... etc

The first number is what index to start at and the second number is how many results to show.

So you could do something like (15 * (pageNumber - 1)) to get the first number on each page (assuming pageNumber starts at 1).

Generating pages based on amount of data returned from MySql Query

2 Answers2