Problem with mysql pagination of results

Dev Articles Community Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

July 21st, 2004, 12:34 PM

phan3069

Registered User

Join Date: Jul 2004

Location: Florida

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Problem with mysql pagination of results

Well I've looked through the forums, and seach google, but cant find an answer to my problem. So I'm hoping someone can help me.

I did a tutorial for the pagination of my mysql results, and it halfway works. Here's the code:

PHP Code:


		
			
<?php
  include 'db.php';
  
  if(!isset($_GET['page'])){
      $page = 1;
  } else {
      $page = $_GET['page'];
  }
  
  // Define the number of results per page
  $max_results = 10;
  
  // Figure out the limit for the query based
  // on the current page number.
  $from = (($page * $max_results) - $max_results);
  
  // Perform MySQL query on only the current page number's results
  
  $sql = "SELECT * FROM music LIMIT $from, $max_results";
  
  $result = mysql_query($sql) or die(mysql_error());
  
  while($row = mysql_fetch_array($result)){
      // Build your formatted results here.
          $variable1=$row["id"];
          $variable2=$row["artist"]; 
          $variable3=$row["song"]; 
          $variable4=$row["album"];
          $variable5=$row["genre"];
  
  //results
  
          print ("<tr>");
          print ("<td class='body_1'>$variable1</td>");
          print ("<td class='body_1'>$variable2</td>"); 
          print ("<td class='body_1'>$variable3</td>"); 
          print ("<td class='body_1'>$variable4</td>");
         print ("<td class='body_1' style='border-right: 0px;'>$variable5</td>"); 
          print ("</tr>"); 
          }
  
  
  // Figure out the total number of results in DB:
  $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM music"),0);
  
  // Figure out the total number of pages. Always round up using ceil()
  $total_pages = ceil($total_results / $max_results);
  
  // Page Number Hyperlinks
  echo "<tr><td colspan=4 align=center>Select a Page<br />";
  
  // Previous Link
  if($page > 1){
      $prev = ($page - 1);
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a>&nbsp;";
  }
  
  for($i = 1; $i <= $total_pages; $i++){
  if(($page) == $i){
  echo "<strong><font color=#FF0000>$i </font></strong>";
  } else {
  echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
  }
  if(($i + 30) % 30 == 0)
  {
  echo '</td></tr><tr><td colspan=4 align=center>';
  }
  }
  
  // Next Link
  if($page < $total_pages){
      $next = ($page + 1);
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
  }
  echo "</center>";
  ?>

When I searched, it displayed all the items in my mysql table. So I replaced

PHP Code:


		
			
 $sql = "SELECT * FROM music LIMIT $from, $max_results";

with:

PHP Code:


		
			
 $sql = "SELECT * FROM music WHERE $type LIKE '%$search%' LIMIT $from, $max_results";

Now when I search, it returns the first 10 resutls like it should, but when I click Next or any of the numbered links, I get this error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%%' LIMIT 10, 10' at line 1

Can someone please help me out?

July 21st, 2004, 03:30 PM

dhouston

Contributing User

Dev Articles Beginner (1000 - 1499 posts)

Join Date: May 2003

Location: Tennessee

Posts: 1,355

Time spent in forums: < 1 sec
Reputation Power: 7

You're not sending $type (or $search) in your query string for the "Next" link, but you're using it in your query. When you hit the "Next" link, your query's being evaluated as "SELECT * FROM music WHERE LIKE '%%' ..." This is invalid SQL. Pass these two variables in your "Next" link and this'll work. Also, consider using the $_GET array to scope your variables so you don't run into problems later (and so your code's portable to systems that don't have register_globals turned on -- if you don't know what register_globals is, do a search here; there's a sticky probably in the PHP forum explaining it all quite nicely).

__________________
Please don't PM me asking for solutions outside the scope of a thread.
Keeping all responses in a thread stands to help others who come along later,
which is after all what this forum's all about.

July 21st, 2004, 06:21 PM

phan3069

Registered User

Join Date: Jul 2004

Location: Florida

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Well, that did the trick. Thanks soooo much!. Thanks for the info on $_GET, I'll have to do that.

July 25th, 2004, 12:50 AM

masami

Registered User

Join Date: Jul 2004

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

I have same problem

Quote:

Originally Posted by phan3069

Well, that did the trick. Thanks soooo much!. Thanks for the info on $_GET, I'll have to do that.

Hi. I have exact same problem as yours. But I don't know how to send $type to the Next link. How did you fix yours? If you showed me the code I would really appreciate it. I've been trying to figure out past two weeks and I'm so stuck. Please please help me!

Thanks,
Masami

August 3rd, 2004, 09:25 PM

phan3069

Registered User

Join Date: Jul 2004

Location: Florida

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Sorry it took so long for my to reply

K. Change this:

PHP Code:


		
			
// Previous Link
   if($page > 1){
       $prev = ($page - 1);
       echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Back</a>&nbsp;";
   }
   // Page Numbers
   for($i = 1; $i <= $total_pages; $i++){
   if(($page) == $i){
   echo "<strong><font color=#FF0000>$i </font></strong>";
   } else {
   echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">[ $i ]</a> ";
   }
   if(($i + 30) % 30 == 0)
   {
   echo '</td></tr><tr><td colspan=4 align=center>';
   }
   }
   
   // Next Link
   if($page < $total_pages){
       $next = ($page + 1);
       echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
   }
   echo "</center>";
   ?>

to this:

PHP Code:


		
			
// Previous Link
   if($page > 1){
       $prev = ($page - 1);
     echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&search=$search&type=$type\"><<Back</a>&nbsp;";
   }
   // Page Numbers
   for($i = 1; $i <= $total_pages; $i++){
   if(($page) == $i){
   echo "<strong><font color=#FF0000>$i </font></strong>";
   } else {
   echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&search=$search&type=$type\">[ $i ]</a> ";
   }
   if(($i + 30) % 30 == 0)
   {
   echo '</td></tr><tr><td colspan=4 align=center>';
   }
   }
   
   // Next Link
   if($page < $total_pages){
       $next = ($page + 1);
     echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&search=$search&type=$type\">Next>></a>";
   }
   echo "</center>";
   ?>

August 20th, 2004, 01:20 AM

cheezylu

Registered User

Join Date: Aug 2004

Location: Chicago, IL

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Registering a session variable??

I was having the same problem too... forums rock! Anyway, Now my search results don't stay when I go to another page making it impossible to search and use multiple pages (paginated). Does anyone know what to do? I heard something about registering session variables, but don't really understand how to do that. Any help would be greatly appreciated.

-- Stephanie --
http://www.cheezylu.com/

August 20th, 2004, 02:58 AM

Itsacon

Command Line Warrior

Join Date: Aug 2004

Location: Sector ZZ9 Plural Z Alpha

Posts: 956

Folding Points: 682581 Folding Title: Super Ultimate Folder - Level 2

Time spent in forums: 6 Days 8 h 23 m 34 sec
Reputation Power: 4

well, sessions is an entirely new chapter, but the short rundown is:

Start your code with session_start() (all the way at the beginning of the code).
After that you can populate and read the global $_SESSION[] array to store and read session variables.

For more detailed info, I suggest going to the PHP forum and start a new thread, or go to PHP.net

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: