php mysql pagination not corresponding with search

Question

0.00/5 (No votes)

See more:

here is a script that i got to work with the search and pagination..However, when I advance to past page one, no results are displayed. So, I'm assuming there is an error within my code. What can I modify within the code in order for the pagination to correspond with the search?

HTML

<!DOCTYPE HTML>
<html>
    <head>
        <title>PHP Paging Tutorial Demo</title>
        <link rel="stylesheet" type="text/css" href="css/style.css">
    </head>
<body>
  <html>
<head>
</head>
<body>
<form name="frmSearch" method="post" action="index.php">
  <table width="599" border="1">
    <tr>
      <th>Keyword
      <input name="var1" type="text" id="var1">
      <input type="submit" value="Search"></th>
    </tr>
  </table>
</form>
<?php
	// include database connection
	include 'db_connect.php';
	$var1 = str_replace(array('%','_'),'',$_POST['var1']);
	
	if (!$var1){
		exit('Invalid form value: '.$var1);
	}

	// page is the current page, if there's nothing set, default is page 1
	$page = isset($_GET['page']) ? $_GET['page'] :
	1;
	// set records or rows of data per page
	$recordsPerPage = 3;
	// calculate for the query LIMIT clause
	$fromRecordNum = ($recordsPerPage * $page) - $recordsPerPage;
	// select all data
	$query = "SELECT 
           *
        FROM 
            broadway 
        WHERE 
        	Name LIKE :search OR PCITY LIKE :search
        ORDER BY 
            Name desc
        LIMIT 
            {$fromRecordNum}

, {$recordsPerPage}

";
/*
            page and its LIMIT clause looks like:
            1 = 0, 5
            2 = 5,10
            3 = 10,15
            4 = 15, 20
            5 = 20, 25
            */
$stmt = $dbh->prepare( $query );
$stmt->bindValue(':search', '%' . $var1 . '%', PDO::PARAM_INT);
$stmt->execute();
//this is how to get number of rows returned
$num = $stmt->rowCount();
//check if more than 0 record found

if($num>0){
	//start table
	echo "<table id='tfhover' class='tftable' border='1'>";
	//creating our table heading
	echo "<tr>";
	echo "<th>Name</th>";
	echo "<th>PCITY</th>";
	echo "<th>DESCR</th>";
	echo "</tr>";
	//retrieve our table contents
	//fetch() is faster than fetchAll()
	//http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
		//extract row, this will make $row['firstname'] to just $firstname only
		extract($row);
		//creating new table row per record
		echo "<tr>";
		echo "<td>{$Name}

	</td>";
	echo "<td>{$PCITY}

</td>";
echo "<td>{$DESCR}

</td>";
echo "</tr>";
}

echo "</table>";
//end table
// *************** <PAGING_SECTION> ***************
echo "<div id='paging'>";
// ***** for 'first' and 'previous' pages

if($page>1){
	// ********** show the first page
	echo "<a href='" . $_SERVER['PHP_SELF'] . "' title='Go to the first page.' class='customBtn'>";
	echo "<span style='margin:0 .5em;'> << </span>";
	echo "</a>";
	// ********** show the previous page
	$prev_page = $page - 1;
	echo "<a href='" . $_SERVER['PHP_SELF']                     . "?page={$prev_page}

' title='Previous page is {$prev_page}

.' class='customBtn'>";
echo "<span style='margin:0 .5em;'> < </span>";
echo "</a>";
}

// ********** show the number paging
// find out total pages
$query = "SELECT COUNT(*) as total_rows FROM broadway WHERE 
        	Name LIKE :search OR PCITY LIKE :search";
$stmt = $dbh->prepare( $query );
$stmt->bindValue(':search', '%' . $var1 . '%', PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_rows = $row['total_rows'];
$total_pages = ceil($total_rows / $recordsPerPage);
// range of num links to show
$range = 2;
// display links to 'range of pages' around 'current page'
$initial_num = $page - $range;
$condition_limit_num = ($page + $range)  + 1;
for ($x=$initial_num; $x<$condition_limit_num; $x++) {
	// be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'
	
	if (($x > 0) && ($x <= $total_pages)) {
		// current page
		
		if ($x == $page) {
			echo "<span class='customBtn' style='background:red;'>$x</span>";
		}

		// not current page else {
			echo " <a href='{$_SERVER['PHP_SELF']}

		?page=$x' class='customBtn'>$x</a> ";
	}

}

}

// ***** for 'next' and 'last' pages

if($page<$total_pages){
	// ********** show the next page
	$next_page = $page + 1;
	echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$next_page}

' title='Next page is {$next_page}

.' class='customBtn'>";
echo "<span style='margin:0 .5em;'> > </span>";
echo "</a>";
// ********** show the last page
echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$total_pages}

' title='Last page is {$total_pages}

.' class='customBtn'>";
echo "<span style='margin:0 .5em;'> >> </span>";
echo "</a>";
}

echo "</div>";
// ***** allow user to enter page number
echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='GET'>";
echo "Go to page: ";
echo "<input type='text' name='page' size='1' />";
echo "<input type='submit' value='Go' class='customBtn' />";
echo "</form>";
// *************** </PAGING_SECTION> ***************
}

// tell the user if no records were found else {
	echo "<div class='noneFound'>No records found.</div>";
}

?>
</body>
</html>

Posted 15-Apr-15 5:03am

Member 11609659

Add a Solution

Comments

ramyajaya 15-Apr-15 21:05pm

Could you please check if their is any error in the browser error console .

Check if the value for get page is passed correctly?

And you have mentioned "when I advance to page one" using search or prev page button which scenario it didn't work check and confirm

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)