WHERE clause messing up query for pagination!

zachatk1 · May 17, 2011

I had originally created a topic on my pagination not working, but I found out what the problem was. The topic I had created was irrelevant to what the problem was... but anyway, here's my situation:

I've got a query that searches in a MySQL table for a certain value. Then it list's all the findings in a table (pretty simple). Anyway, I obviously want to have a pagination system to make it easier to manage the amount of data per page.

The pagination works fine, it's used for another query that just pulls data (so NO WHERE clause is used!). That is what is causing the problem. I spent hours trying to figure out why this is happening, then I figured out that the only difference between the pagination that works and the one that doesn't is the query have the where clause.

What happens when the where clause is used is that the pagination works fine on the first page, but if you click the link to go to a different page, the page is empty and turns up no results so the mysql data that is pulled is lost.

So here is my code. I don't know exactly why the where clause causes the pagination to not work.

$conn = mysql_connect("$mysql_host","$mysql_user","$mysql_password") or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db("$mysql_database",$conn) or trigger_error("SQL", E_USER_ERROR);

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM ACTIVE WHERE MODEL='$model'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 5;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;


$sql = "SELECT * FROM ACTIVE WHERE MODEL='$model' ORDER BY INDEX_ID DESC LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or die('Error: ' . mysql_error());

echo "
<table width=100% align=center border=1 class=\"mod\"><tr>
<td align=center bgcolor=#00FFFF><b>Rating</b></td>
<td align=center bgcolor=#00FFFF><b>Title</b></td>
<td align=center bgcolor=#00FFFF><b>Make/Model</b></td>
<td align=center bgcolor=#00FFFF><b>Type</b></td>
<td align=center bgcolor=#00FFFF><b>Difficulty</b></td>
<td align=center bgcolor=#00FFFF><b>Comments</b></td>
<td align=center bgcolor=#00FFFF><b>Views</b></td>
</tr>"; 
   
     while ($row = mysql_fetch_assoc($result)) {
     { // Begin while
      $title = $row["TITLE"];
      $type = $row["TYPE"];
      $difficulty = $row["DIFFICULTY"];
      $comments = $row["COMMENT_TOTAL"];
      $id = $row['INDEX_ID'];
      $rating = $row["RATING_AVERAGE"];
      $make = $row["MAKE"];
      $model = $row["MODEL"];
      $views = $row["HITS"];

     echo "
     <tr>
      <td>$rating/10</td>
      <td><a href=\"mod.php?id=$id\">$title</a></td>
      <td>$make - $model</td>
      <td>$type</td>
      <td>$difficulty</td>
      <td>$comments</td>
      <td>$views</td>
     </tr>";
    }
   }
   echo "</table><br />"; 
    /******  build the pagination links ******/
echo "<center>";
// range of num links to show
$range = 3;
echo "<i>Page: </i>";
// if not on page 1, don't show back links
if ($currentpage > 1) {
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a stylehref='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'>Previous</a> - ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " <b>$x</b> ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " - <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>Next</a> ";
} // end if
/****** end build pagination links ******/
mysql_close($conn);
}
echo "$pagination</center>";
?>

I really appreciate the help! Thanks.

Psycho · May 17, 2011

I don't need to even read through your code to know what the problem is. You apparently have a page where a user can do a search. When the user submits that page you pass the value for the results to be filtered on. In this case it is the value of $model - although you didn't show how that value is defined! But, I suspect it is via a POST value.

Anyway, once the user selects another page you are reloading the current page passing a page variable - but you are not passing the $model value. Therefore the subsequent page load is trying to run a query to find record where the model value is an empty string - thus the empty results. You need to pass the model value on to each and every page load if you want the records to be continued to be filtered by that result. One way you can do that by appending the model value onto the query string (along with the page value). But, that can get ugly and requires more work to ensure the value doesn't get corrupted in the query string. Two other alternatives would be to store the model value as a cookie or a session value. I would use a session value.

zachatk1 · May 18, 2011

That makes sense! So I tried doing a session but I get the same thing. I'm not exactly sure how to have it post to the same page, so I don't know if this is right.

session_start();

$_SESSION['make'] = $_POST["sel1"];
$_SESSION['model'] = $_POST["sel2"];

//then i just stuck $_SESSION['make and model'] in the search query like this:

$sql = "SELECT COUNT(*) FROM ACTIVE WHERE MODEL='$_SESSION[model]'";
//and
$sql = "SELECT * FROM ACTIVE WHERE MODEL='$_SESSION[model]' ORDER BY INDEX_ID DESC LIMIT $offset, $rowsperpage";

I think I need to retrieve the session, but I'm not exactly sure how...

Psycho · May 18, 2011

Where are you defining the $model variable? You could do something like this:

if(isset($_POST['model']))
{
    $model = mysql_real_escape_string(trim($_POST['model']));
    $_SESSION['model'] = $model;
}
elseif(isset($_SESSION['model'])
{
    $model = $_SESSION['model'];
}
else
{
    //model not set - perform error handling
}

Don't forget to use session_start() at the top of the page.

zachatk1 · May 18, 2011

Oh I should have probably said that sel2 is the model! That's why $_SESSION['model'] = $_POST['sel2'].

I tried your code, but I get this:

Parse error: syntax error, unexpected '{'

Psycho · May 18, 2011

I tried your code, but I get this:

Parse error: syntax error, unexpected '{'

So fix it. I plainly state in my signature that I may not test the code. It would require much more time to rebuild databases and other requirements to test some code. You should be able to find and fix the simple syntax error in the code I provided.

jonsjava · May 18, 2011

<snark>

But mjdamato, your code should be perfect!

</snark>

Sorry, had to say it.

Psycho · May 18, 2011

<snark>

But mjdamato, your code should be perfect!

</snark>

Sorry, had to say it.

There is no such thing as perfect code. I take time out of my day to help people for free who simply copy/paste the code without even trying to understand it and then have the audacity to say it doesn't work because of a simple syntax error which they should be perfectly capable of recognizing and fixing. "they" should be more appreciative of the help that is provided to them, IMHO.

ignace · May 18, 2011

I don't see why they should use a session to persist the value (although I do understand mjdamato's argument). But it gives a false believe of safety to the OP since I could mess as much with the POST-ed value as I could with normal query string parameters so I would still have to go through validating and sanitizing the POST-ed value as I would when I received the query string parameter except I would have to do it on every request.

zachatk1 · May 18, 2011

Wow, sorry! I can't believe I didn't see that missing ). Except the code is triggering the error handling part.

Psycho · May 18, 2011

I don't see why they should use a session to persist the value (although I do understand mjdamato's argument). But it gives a false believe of safety to the OP since I could mess as much with the POST-ed value as I could with normal query string parameters so I would still have to go through validating and sanitizing the POST-ed value as I would when I received the query string parameter except I would have to do it on every request.

Can you elaborate on your statement? I'm not sure what you are proposing? I stated in my first post that there were several methods to persist the value and that session values were my preference (with passing on URL or in cookie as two alternatives). If you have a alternative solution, please share it and the reasons you would use it.

Wow, sorry! I can't believe I didn't see that missing ). Except the code is triggering the error handling part.

Well, the error condition would only occur id the POST value is not set AND the session value is not set. If the page can be run without a "model" value to filter on - which is a perfectly acceptable requirement - then you just need to not include the error condition. However, if it is expected - and it is not happening - then you need to determine why the error condition is occurring. Either the POST value is not getting sent/received (could be a typo between the field name and the POST index value) or the session value is not set (again, could be a typo or you are not using session_start()).

zachatk1 · May 19, 2011

Thanks everyone! I got it figured out!!!

ignace · May 19, 2011

Can you elaborate on your statement?

.. One way you can do that by appending the model value onto the query string (along with the page value). But, that can get ugly and requires more work to ensure the value doesn't get corrupted in the query string...

I thought the OP may misinterpret your text and falsely believe using POST in combination with SESSION is secure. I thought I should point it out to the OP. Reading my text again I now realise I have some real trouble explaining myself clearly, clearly.

neha_jaltare · September 17, 2012

Actually I have same problem with that pagination . I have to search using multiple combo box values.When I clicked on next button didn't get anything :'( . I tried Psycho's solution but still :'(

Here is my code

<?php

$i = 0;

if(!empty($_POST['select2']))

{

foreach ($_POST['select2'] as $selectedOption)

{

$options[$i++] = $selectedOption;

}

$num = count($options);

for ($i=0; $i<$num-1; $i++)

{

$option1 = "'".$options[$i]."',";

}

$option1 .= "'".$options[$i]."'";

//echo $option1;

if(isset($option1))

{

$model= mysql_real_escape_string(trim($option1));

$_SESSION[$option1]=$model;

}

elseif(isset ($_SESSION[$option1]))

{

$model= $_SESSION[$option1];

}

else {

echo "There is an error in your query". mysql_error;

}

/* Pagination Start */

$per_page=6;

$sql = "select date,mobno,city,state,type,telecaller FROM import WHERE time IN($option1)GROUP BY mobno,telecaller ORDER BY date DESC";

$pages_query= mysql_query($sql);

$pages=ceil(mysql_result($pages_query, 0)/$per_page);

//$pages=floor(mysql_result($pages_query, 0)/$per_page);

$page=(isset($_GET['page'])) ? (int)$_GET['page'] : 1;

$start=($page-1) * $per_page;

$sql = "select date,mobno,city,state,type,telecaller FROM import WHERE time IN($option1) GROUP BY mobno,telecaller ORDER BY date DESC LIMIT $start,$per_page";

$query= mysql_query($sql);

if($pages>=1 && $page<=$pages){

for($x=1;$x<=$pages;$x++){

echo ($x==$page) ? '<strong><a style="text-decoration:none" href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ';

}

.

Is there any alternative solution for this code? Thanks in advance.

Christian F. · September 17, 2012

neha_jaltare: Please indent your code properly, and use the

tags around your code. Both of them will make your post and your code a lot easier to read, and by extension make it a lot easier to spot the problem.

Also, it never hurts to explain the problem in a bit more detail too. "Didn't get anything" doesn't tell us a whole lot I'm afraid. Though, when I copied your code into my editor, I noticed that you have a syntax error in there. Most likely due to the haphazard indenting, or a copy-paste error. Fixing the indenting will help you spot it.

In any case, if you haven't done so already I strongly recommend turning on all error reporting in your development environment. You can do so in php.ini.

neha_jaltare · September 17, 2012

Here is my whole code

<?php	
   $i = 0;
       
                if(!empty($_POST['select2']))
                {
	foreach ($_POST['select2'] as $selectedOption)
		{
			$options[$i++] = $selectedOption;
		}
                }
                
                $num = count($options);
	for ($i=0; $i<$num-1; $i++)
	{
	   $option1 = "'".$options[$i]."',";
	}
                 $option1 .= "'".$options[$i]."'";
                 $option2=$_SESSION[$option1];
                 echo $option2;
          
$per_page=6;
    $sql = "select date,mobno,city,state,type,telecaller FROM import WHERE time IN($option1)GROUP BY mobno,telecaller ORDER BY date DESC";	          
$pages_query= mysql_query($sql);  
$pages=ceil(mysql_result($pages_query, 0)/$per_page);
//$pages=floor(mysql_result($pages_query, 0)/$per_page);

$page=(isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start=($page-1) * $per_page;
$sql = "select date,mobno,city,state,type,telecaller FROM import WHERE time IN($option1) GROUP BY mobno,telecaller ORDER BY date DESC LIMIT $start,$per_page";      
$query= mysql_query($sql);
/* Pagination Complete */    
print"<div id='print'>";
print"<center>List of Mobile Numbers for Date <font color='#FF00FF'><U> $_POST[date]</U> </font> <center> <br/>";
print"<table border='1' cellspacing='1' cellpadding='6'>
<tr  bgcolor='#82CAFF'>
<th>Sr.No</th>
<th>Date</th>
<th>Mobile No</th>
<th>City</th>
<th>State</th>
<th>Type</th>
<th>CIExe.</th>
</tr>"; 
$srno1=0;
while($row=mysql_fetch_array($query ))
{
    $srno1=$srno1+1;
print"<tr>";
print"<td>" .$srno1. "</td>";
print"<td>" . $row['date'] . "</td>";
print"<td>" . $row['mobno'] . "</td>";
print"<td>" . $row['city'] . "</td>";
print"<td>" . $row['state'] . "</td>";
print"<td>" . $row['type'] . "</td>";
print"<td>" . $row['telecaller'] . "</td>";
print"</tr>";
}
print"</table>";
print"</div>";
echo"<br/>";
print"<input type='button' value='Print' onclick='fnPrintArea()' /> <br />";
print"<form name='form' method='GET' action='export.php'>";
print"<input type='hidden' name='text1' value='$_POST[date]'  id='text1_1'/><br/>";
print"<input type='submit' name='submit2' value='Export This Record' />";
print"</form>";
if($pages>=1 && $page<=$pages){
    
    for($x=1;$x<=$pages;$x++){
        echo ($x==$page) ? '<strong><a style="text-decoration:none" href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ';
    }
    
}
?>
  </div>     
  <div id="menu1">
  <ul> 
<li><a href="telecaller.php" title="search all record for perticular telecaller using that date">Search by CIExe.</a></li>
<li><a class="current" href="systemdate.php" title="Search record with date and time for all CIExe.s">Search by Date </a></li>
  <li><a href="mobileno.php" title="Search  record individually for either perticular mobile no or CIExe.">Search Mobile No./CIExe.</a></li>
</ul>  
  </div>
<a href="export3.php" class="button blue alt-gradient" id="href">Export All Record</a>
</body>

Please tell me you have any doubt about this code.

Psycho · September 17, 2012

Why are you posting in a thread that is over a year old? You could start a new thread and reference this one if applicable.

Anyway, your problem is exactly the same as the OP in this thread. You stated you used the same solution, but I don't see that you have. The problem is that you are using a POST value to determine the criteria to use for filtering the content. But, when th euser selects to go to another page there will not be any POST value. So, you need to store the POSTed search criteria into a SESSION or in some manner.

But, looking at your code there are other problems as well. Such as this

    $sql = "select date,mobno,city,state,type,telecaller FROM import WHERE time IN($option1)GROUP BY mobno,telecaller ORDER BY date DESC";	          
$pages_query= mysql_query($sql);  
$pages=ceil(mysql_result($pages_query, 0)/$per_page);

You are running a query to return a set of records matching the criteria and using the first value from the first record in determining the number of pages?! You need to get a COUNT() of the records matching the criteria. I stopped looking at the code from that point on. You should find a tutorial on pagination and work through it to understand the process. Then use it to implement as needed for your use.

Sign In

WHERE clause messing up query for pagination!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information