Hi there,

I just can't figure this out and I hope someone may be kind enough to help me.
I am trying to paginate search results from a simple form. The script I am using works fine for the first page of results but resorts to displaying all the rows from my database when I click to view next page. ANy help would be greatly appreciated.

<?php 
if(isset($_POST['searchquery']) && $_POST['searchquery'] != ""){
    $searchquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['searchquery']);
}
if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$start_from = ($page-1) * 20; 
//Connect to the database through our include 
include_once "php_includes/connect_to_mysql.php";
$sql = "SELECT * FROM clicks WHERE user_name LIKE '%$searchquery%' ORDER BY ID LIMIT $start_from, 20";
$rs_result = mysql_query ($sql); 
?> 
<table>
<tr><td>ID&nbsp;&nbsp;&nbsp;&nbsp;</td><td>Username&nbsp;&nbsp;&nbsp;&nbsp;</td><td>Viewed User&nbsp;&nbsp;&nbsp;&nbsp;</td><td>First Name&nbsp;&nbsp;&nbsp;&nbsp;</td><td>Last Name&nbsp;&nbsp;&nbsp;&nbsp;</td><td>When&nbsp;&nbsp;&nbsp;&nbsp;</td></tr>
<?php 
while ($row = mysql_fetch_assoc($rs_result)) { 
?> 
            <tr>
            <td><? echo $row["id"]; ?></td>
            <td><? echo $row["userid"]; ?></td>
            <td><? echo $row["favourite_id"]; ?></td>
            <td><? echo $row["firstname"]; ?></td>
            <td><? echo $row["lname"]; ?></td>
            <td><? echo $row["when"]; ?></td>
            </tr>
<?php 
}; 
?> 
</table>
<?php 
$sql = "SELECT COUNT(ID) FROM clicks WHERE user_name LIKE '%$searchquery%'"; 
$rs_result = mysql_query($sql); 
$row = mysql_fetch_row($rs_result); 
$total_records = $row[0]; 
$total_pages = ceil($total_records / 20); 

for ($i=1; $i<=$total_pages; $i++) { 
            echo "<a href='simplepag.php?page=".$i."'>".$i."</a> "; 
}; 
?>
</body>
</html>

Are you checking the "COUNT()" somewhere else so that you know how many potential results are available before you offer the user a "Next" or "Previous" option?

No I'm not checking the "COUNT()" anywhere else, this is my complete code (excluding my form) and I'm sorry to say I'm not sure where I should put this so any clues are greatly appreciated.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.