[SOLVED] Pagination Passing Wrong Data to SQL

pcbytes · March 22, 2007

So I almost have this working. Last hurdle is the Pagination is a little jacked up. I was getting an error on line 49. So I threw a print statement in my code to see what the value is that was being passed to mySQL. Yeah it deff creates some problems. Here is what the data the variable is trying to pass

On the initial pageload before I select a category and submit:

SELECT * FROM upload WHERE category = ''LIMIT -3,3
Fatal error: FATAL ERROR in C:\apache\htdocs\index.php on line 50

After selecting a category and pressing submit it shows the correct amount:

SELECT * FROM upload WHERE category = 'animals'LIMIT 0,3

But after clicking the next button to get the next page of results I get:

SELECT * FROM upload WHERE category = ''LIMIT -3,3
Fatal error: FATAL ERROR in C:\apache\htdocs\index.php on line 50

So some reason on the initial page load, and whenever I click the next button it isnt working right. It works right on the first page after clicking submit, just not on any other pages.

index.php

<!-- FORM TO DISPLAY DATABASE TEMPLATES -->
<form name="form1" method="post" action="<?php echo $PHP_SELF?>">
  <select name="section" size="1" multiple>
    <option selected>layouts</option>
  </select>
  <select name="category" size="1">
    <option selected>animals</option>
    <option>Anime</option>
  </select>
  <br>
  <input type="submit" name="Submit" value="Submit">
</form>
<!-- END DISPLAY FORM -->

<?
//GET FORM DATA
$section = $_POST['section'];
$category = $_POST['category'];

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if

require("connectDB.php"); //DATABASE CONNECTION SEQUENCE

$query = "SELECT count(*) FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'";
$result = mysql_query($query) or trigger_error("FATAL ERROR: " . mysql_error(), E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 3;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno < 1) {
   $pageno = 1;
} elseif ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
$query = "SELECT * FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'$limit";
print $query;
$result = mysql_query($query) or trigger_error("FATAL ERROR", E_USER_ERROR);

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if

echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if

//FETCH SQL DATA AND PRINT IT TO THE SCREEN
while($row = mysql_fetch_array($result)){
$id = $row["id"];
$codes = $row["codes"];
// $codes = ereg_replace("INSERTURLHERE", , $codes); NOT CURRENTLY USING THIS FEATURE
print '<table width="400" border="2" cellspacing="0" cellpadding="0">';

//DISPLAY THUMBNAIL IMAGE FROM DATABASE
print ("<tr><td><img src=\"download.php?id=$id\"></td></tr>"); 

//POPULATE TEXTFIELD WITH CSS CODE FOR TEMPLATE
print "<tr><td><textarea name='textfield' wrap='OFF' cols='50' rows='7'>".$codes."</textarea></td></tr>";
print '</table><br /><br />';

}

require("disconnectDB.php");


?>

HeyRay2 · March 22, 2007

The issue appears to be with how you are calculating your $limit variable.

Try changing this line:

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

to this:

$limit = ' LIMIT '.($pageno * $rows_per_page) - $rows_per_page.','.$rows_per_page;

pcbytes · March 22, 2007

Yeah that only made it return

SELECT * FROM upload WHERE category = 'Anime'-3,3

on all pages. And not display any rows.

HeyRay2 · March 22, 2007

I'm not sure why my change caused another problem with your LIMIT statement, I think I'm starting to see several

other items that are causing your issue, so let's go through them.

You are defining your category and section variables in your form using the POST method, so when you initially

load the page, your $_POST variables are not yet set because you haven't submitted the form,

which causes your query to return 0 results. We should add code to check if the form has been submitted.

After you have submitted the form, the $_POST variables finally are set and your query successfully returns results.

However, once you click on a link, the page refreshes and your $_POST variables are once again not set which causes

your query to return 0 results again.

Also, when your numrows variable gets set to 0 (because of your query not returning any results) this causes your next

query to fail as well, so we should write a conditional for when no results are returned to avoid this.

I would suggest submitting your form using the GET method, so that way your variables are passed in the URL

(along with your pageno variable), and adding a conditional to check if the form was submitted.

Let start by changing how your form was submitted to use the GET method.

Change the line:

<form name="form1" method="post" action="<?php echo $PHP_SELF?>">

to:

<form name="form1" method="GET" action="<?php echo $PHP_SELF?>">

Next, let's add code to check if the form was submitted. Add the following lines of code around your existing code:

// ... your HTML form ...

<? // this is where your PHP code starts

// Check if the form was submitted
if ($_GET['Submit']) {


// THE REST OF YOUR CODE

} else {
// The form was not submitted yet
echo "Please search to see results..."
}

?> // this is where your PHP code ends

Next, let's change how your category and section vaiables get defined. Change the following lines:

//GET FORM DATA
$section = $_POST['section'];
$category = $_POST['category'];

To:

//GET FORM DATA
$section = $_GET['section'];
$category = $_GET['category'];

Next up, let's check if any results were returned when we set the numrows variable. After this line:

$numrows = $query_data[0];

Add the following code:

// Check if any results were returned
if ($numrows < 1){
	// No results returned, show a message
	echo "No Results Returned! Please try searching again!";
} else {

And after these lines:

//POPULATE TEXTFIELD WITH CSS CODE FOR TEMPLATE
print "<tr><td><textarea name='textfield' wrap='OFF' cols='50' rows='7'>".$codes."</textarea></td></tr>";
print '</table><br /><br />';

}

Add another closing bracket:

Lastly, in order to be sure that your section and category variables get passed between pages, you need to add them

to each of your pagination links. Add the following line to the end of each link in your code:

&section=$section&category=$category

So for the sake of clarity, here is the full code with my changes added:

<!-- FORM TO DISPLAY DATABASE TEMPLATES -->
<form name="form1" method="GET" action="<?php echo $PHP_SELF?>">
  <select name="section" size="1" multiple>
    <option selected>layouts</option>
  </select>
  <select name="category" size="1">
    <option selected>animals</option>
    <option>Anime</option>
  </select>
  <br>
  <input type="submit" name="Submit" value="Submit">
</form>
<!-- END DISPLAY FORM -->

<?php

// Check if the form was submitted
if ($_GET['Submit']) {

//GET FORM DATA
$section = $_GET['section'];
$category = $_GET['category'];

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if

require("connectDB.php"); //DATABASE CONNECTION SEQUENCE

$query = "SELECT count(*) FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'";
$result = mysql_query($query) or trigger_error("FATAL ERROR: " . mysql_error(), E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

// Check if any results were returned
if ($numrows < 1){
	// No results returned, show a message
	echo "No Results Returned! Please try searching again!";
} else {
	$rows_per_page = 3;
	$lastpage      = ceil($numrows/$rows_per_page);

	$pageno = (int)$pageno;
	if ($pageno < 1) {
	   $pageno = 1;
	} elseif ($pageno > $lastpage) {
	   $pageno = $lastpage;
	} // if

	$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
	$query = "SELECT * FROM upload WHERE category = '" . mysql_real_escape_string($category) . "'$limit";
	print $query;
	$result = mysql_query($query) or trigger_error("FATAL ERROR", E_USER_ERROR);

	if ($pageno == 1) {
	   echo " FIRST PREV ";
	} else {
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1&section=$section&category=$category'>FIRST</a> ";
	   $prevpage = $pageno-1;
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage&section=$section&category=$category'>PREV</a> ";
	} // if

	echo " ( Page $pageno of $lastpage ) ";

	if ($pageno == $lastpage) {
	   echo " NEXT LAST ";
	} else {
	   $nextpage = $pageno+1;
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage&section=$section&category=$category'>NEXT</a> ";
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage&section=$section&category=$category'>LAST</a> ";
	} // if

	//FETCH SQL DATA AND PRINT IT TO THE SCREEN
	while($row = mysql_fetch_array($result)){
		$id = $row["id"];
		$codes = $row["codes"];
		// $codes = ereg_replace("INSERTURLHERE", , $codes); NOT CURRENTLY USING THIS FEATURE
		print '<table width="400" border="2" cellspacing="0" cellpadding="0">';

		//DISPLAY THUMBNAIL IMAGE FROM DATABASE
		print ("<tr><td><img src=\"download.php?id=$id\"></td></tr>"); 

		//POPULATE TEXTFIELD WITH CSS CODE FOR TEMPLATE
		print "<tr><td><textarea name='textfield' wrap='OFF' cols='50' rows='7'>".$codes."</textarea></td></tr>";
		print '</table><br /><br />';

	}

}

require("disconnectDB.php");

} else {
// The form was not submitted yet
echo "Please search to see results..."
}

?>

Let me know how this works for you.

pcbytes · March 22, 2007

Very close to working.

I decided to upload the code and database to a live server so people can better see how its working.

try it on my temporary server http://www.anakin-skywalker.com

The images broke on importing my database, but don't worry about that, not critical to testing.

As you can see the new code removed the error on line 49. But whenever you click the Next Link it returns no results from the database.

HeyRay2 · March 23, 2007

Doh! My bad!

Add this to the end of each of your links:

&Submit=Submit

That will cause your code to think that the form was submitted each time you click a link to another results page.

Sorry I missed that! :-\

Full code to change for clarification:

	if ($pageno == 1) {
	   echo " FIRST PREV ";
	} else {
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1&section=$section&category=$category&Submit=Submit'>FIRST</a> ";
	   $prevpage = $pageno-1;
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage&section=$section&category=$category&Submit=Submit'>PREV</a> ";
	} // if

	echo " ( Page $pageno of $lastpage ) ";

	if ($pageno == $lastpage) {
	   echo " NEXT LAST ";
	} else {
	   $nextpage = $pageno+1;
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage&section=$section&category=$category&Submit=Submit'>NEXT</a> ";
	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage&section=$section&category=$category&Submit=Submit'>LAST</a> ";
	} // if

P.S. - You can test that this last change works simply by adding '&Submit=Submit' to the URL in your browsers address bar after you've done a search on your script.

pcbytes · March 23, 2007

Wow dude. WOW!

I have to give you some MAJOR props! My code is now working 100% as intended! Man you don't even know how long I've been sweating over this! I am rather new to PHP so I'm still getting my feet wet. But its guys like you that help me out when I get stuck that keep me from getting discouraged!

Thanks a million man! ;D ;D

HeyRay2 · March 23, 2007

I've been helped here many times, so it's the least I can do to help out a fellow PHP Freak!

Sign In

[SOLVED] Pagination Passing Wrong Data to SQL

Recommended Posts

pcbytes

Link to comment

Share on other sites

HeyRay2

Link to comment

Share on other sites

pcbytes

Link to comment

Share on other sites

HeyRay2

Link to comment

Share on other sites

pcbytes

Link to comment

Share on other sites

HeyRay2

Link to comment

Share on other sites

pcbytes

Link to comment

Share on other sites

HeyRay2

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information