limit DB results?

jasondavis

if ($start=="" ){
	$start=0;
}
if ($pagesize == "") {
	$pagesize=20;
}

$reccnt=100
$countup = "1";
$res = mysql_query("select * from friend_reg_user order by auto_id desc limit $start,$pagesize");
$riders = mysql_num_rows($res);

Here is a code I use it is used in my paging of mysql results, my trouble is it returns all results and pags them perfectly but I want to just select 100 results and apply my paging to 100 results instead of all

bpat1434

Here is a code I use it is used in my paging of mysql results, my trouble is it returns all results and pags them perfectly but I want to just select 100 results and apply my paging to 100 results instead of all

The bold part I'm confused about. If it returns all results and pages them perfectly, what's wrong? If you have 19 results, and you want a max of 20 per page, then it will show all results every time (since 19 < 20).

Now, to change to to be 100, just change the $pagesize variable value to be 100 instead of 20.

jasondavis

bpat1434 wrote:
The bold part I'm confused about. If it returns all results and pages them perfectly, what's wrong? If you have 19 results, and you want a max of 20 per page, then it will show all results every time (since 19 < 20).

Now, to change to to be 100, just change the $pagesize variable value to be 100 instead of 20.

lets say there is 15,000 results if I use 100 then ir returns 100 on a page, what I want to do is limit it to 100 results but only show like 20 per page

bpat1434

Okay, that's totally different.....

You'd need to run a query to get the 100 record IDs you want. Then split that into groups of 20, or dynamically limit which IDs are used, and take the 20 IDs and create a comma separated value list and use them in the WHERE clause of the array:

<?php
$recent = 100; // How many recent items to get...
$q1 = "SELECT `auto_id` FROM `table` LIMIT 0,$recent";
$result = mysql_query($query) or die('MySQL Query Error (#' . mysql_errno() . '): ' . mysql_error());
$ids = array();
while($row = mysql_fetch_array($result))
{
    $ids[] = $row['id'];
}
$ids = array_unique($ids); // Remove duplicates... in case there are any
$free = @mysql_free_result($result);

$page = $_GET['page']; // Get the page we're on...
$pagesize = 20; // Set the num of records per page
$start = ($page-1)*$pagesize; // 1-1*20 = 0 (page 1), 2-1*20 = 20 (page 2), etc.
$end = $start+$pagesize-1; // 0+20-1 = 19 (page 1), 20+20-1 = 39 (page2), etc.

for($i=0, $max=sizeof($ids); $i<$max; $i++)
{
    if($i >= $start && $i<=$end)
        $auto_ids[] = $ids[$i];
}

$query = "SELECT * FROM `friend_reg_user`WHERE `auto_id` IN(" . implode(',', $auto_ids) . ") ORDER BY `auto_id`DESC";
$result = mysql_query($query) or die('MySQL Error (#' . mysql_errno() . '): ' . mysql_error());

/** Do whatever you want with the new results... **/

Hope that code helps you get started...