Limit Query Result Per Page MySQL 3

[WilliamMute]

The forum has been very helpful indeed, thank you. I have a problem since last year april, been trying to look for how to sort query results into page numbers i.e display 10 records per page and have a next and previous page numbering for next ten records. I have NO idea how to do this, read few article but they are quite advanced stuff, any help please???

Thank you once more.

 

[LazyJones]

With mysql LIMIT you can pick out the records you want to show. Then have a variable ($offset) which indicates the starting point of where you want your records to be fetched.

lets say first page has $offset=0 and $onPage = 10

Then make you query like this
"SELECT * FROM TABLE ORDER BY id LIMIT $offset, $onPage"

And link to the next ten:
<a href="show.php?offset=$onPage">Next $onpage items</a>

catch the $offset=$_GET['offset'] and fetch the next records

 

[sleipnir214]

Have you seen faq434-5244 ?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[WilliamMute]

Excellent Result Guys, havent tried it out yet LazyJones but am quite certain it would work because it made absolute sense to me. will try it out and let you know


Cheers a Million and One times.

 

[jpadie]

here is some code if you want to be able to go to any page of your recordset. basically a slight variation on sleipnir214's FAQ

<style>
.row_even {background-color:#FFFFCC;}
.row_odd {background-color:#CCFFFF;}
.pages {text-align:center; size:9pt;}
.noncurrent {color:blue;}
.current {color:red;} 
.table {text-align:center;}
</style>
<?
//insert the parameters
$hostname ="";
$username="";
$password = "";
$database ="";
$table = "";
$maxperpage = 10;

@mysql_connect($hostname, $username, $password)
	or die ("can't connect to database manager");
@mysql_select_db($database)
	or die ("can't connect to database");

$sql = "Select count(*) as cnt from $table";

//do the query
$results = mysql_query($sql);

//we need to know how many rows are returned
$row = mysql_fetch_assoc($results);
$numrows = $row['cnt'];

//how many pages are we going to use
$numpages = ceil($numrows/$maxperpage); 

//now work out where to start
$offset = isset($_GET['offset'])?$_GET['offset']:0;

//now do the query
$sql = "Select * from $table limit $maxperpage offset $offset";

$results = mysql_query($sql);
$class = "row_even";
?>
<div class="table">
<table width="80%">
<?
while ($row = mysql_fetch_assoc($results)):
$class = ($class === "row_even") ? "row_odd" : "row_even";
?>
<tr class="<?=$class?>">
<?
foreach($row as $val):
?>
<td><?=$val?></td>
<? endforeach; ?>
</tr>
<? endwhile; ?>
</table>
<br/>
</div>
<? if ($numpages > 1): ?>
<div class="pages">
<? 
for ($i=0; $i<$numpages; $i++):
$class = (($offset/$maxperpage) === $i) ? "current":"noncurrent";
?>
<a class="<?=$class?>" href="<?=$_SERVER['PHP_SELF']?>?offset=<?=$i*$maxperpage?>">&nbsp;<?=$i+1?></a>  
<?
endfor;
?>
</div>
<? endif; ?>

 

[WilliamMute]

Hi Guys,

A little problem am finding with both examples. Firstly, LazyJones example:

The query dosnt seem to be picking up the value of the $offset and $onPage hence the query is not executing. Secondly, How would I know the first record ID number since in the database, the ID's started from 11 and the onPage value for each page thereafter? Thirdly what about arranging the products alphebetically?



sleipnir214's Example:

Again a very very good example, but am having a bit of problem understanding part of it as to implementing it.

The If statement on th $Get, you are testing a state of the $Get ['page'] variable which I cant see where its been set. that is 'page' am abit lost there. is it possible to explain the get process for me in words please?


Thanks again guys...

 

[WilliamMute]

I have the greatest respect for each and everyone of you guys here. You are more than good!


Tahnks just got it working now with jpadie's addition.


Am overwhelmed and greatful.

 

[WilliamMute]

Final question, is it possible to arrange the result in alphebetical order? tried adding
ORDER BY product_name ASC Doesnt work obviously.


Cheers

 

[WilliamMute]

Hello guys, its not Victory quite yet for me. Problem.

The query is not obeying the WHERE statement, because i do not need it to display all the records just the ones that match a certain category called necklace but it is so far displaying ALL the records and in some cases even dublicating a row to make up the 10 row per page. am really tired and sleepy 3:30am pls help

here is the code if would help:


<?php require_once('Connections/Connection.php'); ?>


<?php
mysql_select_db($database_Connection, $Connection);
$table = "products";
$maxperpage = 10;


$sql = "Select count(*) as cnt from $table WHERE Category = 'Necklace'";

//do the query
$results = mysql_query($sql);

//we need to know how many rows are returned
$row = mysql_fetch_assoc($results);
$numrows = $row['cnt'];

//how many pages are we going to use
$numpages = ceil($numrows/$maxperpage);

//now work out where to start
$offset = isset($_GET['offset'])?$_GET['offset']:0;

//now do the query
$sql = "Select * from $table limit $maxperpage offset $offset";

$results = mysql_query($sql);


?>

 

[jpadie]

Hi

you need to put the where statement into both the sql statements. the first statement

$sql = "Select count(*) as cnt from $table WHERE Category = 'Necklace'";

is used for calculating the number of records that would be returned.
the second statementis the one that you actually use for outputting the recordset. Sleipnir's example is perhaps clearer in that he uses different variables for each call.

the "order by" directive will work fine but you, of course, need to put it on the second sql call.

$sql = "Select * from $table where category ='Necklace' order by product_name limit $maxperpage offset $offset";

 

[WilliamMute]

Thanks Jpadie, you are just tooooo good I wish I had your brain!


Cheers again a million and one times