Need little help with Mysql paging (LIMIT)...

Question

I have this code:

    $page = $_GET['page'];
    $res_per_page = $_GET['res_per_page'];

    $start_point = (($page * $res_per_page) - $res_per_page);
    $query.= " LIMIT $start_point, $res_per_page";
    $qry_result = mysql_query($query) or die(mysql_error());
    $total_pages = ceil($nr_ads / $res_per_page);

The variable '$nr_ads' is the total nr of ads...

The code works fine, but I wonder how I can figure out which ads are showing, ex:

   Showing ads 10 - 20

Thanks...

Answer 1

print 'Showing ads ' . $start_point . ' - ' . min($nr_ads, $start_point + res_per_page);

Basically you've already got you starting point (which is given to your LIMIT clause in the SQL. The "end point" is calculated by adding $res_pre_page to this starting point. On the last page, however, you may only have 2 records - therefore you should use the min() function to print e.g. "Showing ad 60 - 62".

---

Follow up: Another answer mentions the risk of SQL injection. You'll be safe by using:

$page = intval($_GET['page']);
$res_per_page = max(1, intval($_GET['res_per_page'])); // To avoid division by zero :)

Answer 2

The simplest way would be to create an ID field in your table and use that to display the information you want, your query would be modified like so:

 LIMIT $start_point, $res_per_page ORDER BY ad_id ASC

from there, you can get the first result and the last result and use it to output the information you are gathering.

One very important security note the following code can cause remote SQL injection:

$query.= " LIMIT $start_point, $res_per_page";

It is highly recomended you change it to:

$query.= " LIMIT $start_point, ".mysql_real_escape_string($res_per_page)." ORDER BY ad_id ASC";