SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with this pagination script

    Hello peeps,
    I have a problem with a pagination script, which i have been adapting from an online tutorial. The original script works fine if only selecting a count of the records in a table. The problem arises now because I need to paginate depending on a search criteria. When I click on my next page links I am getting a MySQL error, which doesn't occur if I am only selecting a bare
    count(*)

    Here is my code:

    Code:
    <?php
    $param1='description';
    $param2='image';
    $param3='houseaddress';
    $param4='location';
    $colour1 = "#ff8888";
    $colour2 = "#ff6655";
    $row_count = 0;
    $query=$_POST['query'];
    echo "<b> query value from list box = ".$query."<br />";
    
    
    
    //connect to server and select database
    include ('connect.php');
    
    //set up variable to paginate the table contents
    if(!isset($_GET['page'])){
            $page=1;
            }else{
                    $page=$_GET['page'];
                    }
                    $max_results=2;
                    $from =(($page * $max_results) - $max_results);
    
    
    //gather the topics
    $result = "select $param1, $param2, $param3 from property WHERE housetypeID = $query LIMIT $from, $max_results";
    $get_result = mysql_query($result,$conn) or die(mysql_error());
    
    
    if (mysql_num_rows($get_result) < 1) {
    
    //there are no results, so say so
         echo "<b>sorry no results - please make another choice";
    // and exit, we don't want any page links to nonexistent results
       exit();
    
    } else {
       //create the display string
       $display_block = " <center>
       <table>
       <tr bgcolor='ff8888'>
       <th  colspan='4'>Results</th>
    
       </tr>";
    
       while ($result_info = mysql_fetch_array($get_result)) {
          $description = $result_info['description'];
                $image = ($result_info['image']);
         $houseaddress = $result_info['houseaddress'];
             $location = $result_info['location'];
          //use modulus 2 to alternate the colour of the rows
           $row_colour = ($row_count &#37; 2) ? $colour1 : $colour2;
          //add to display
          $display_block .= "
          <tr>
           <!--insert $row_colour into table data tags -->
              <td bgcolor = $row_colour>$image</td> <td bgcolor= $row_colour>$description</td>
              <td bgcolor = $row_colour>$houseaddress</td>
              <!--<td bgcolor = $row_colour><a href=\"displayResult.php?location=$location\">$location</a>-->
              </td></tr>";
    
          //increment for row colour
          $row_count++;
    
       }
     print $display_block;
    }
           //show paging results
     $total_results=mysql_result(mysql_query("select count(*) as num from  property where housetypeID = $query"),0);
     $total_pages=ceil($total_results/$max_results);
     echo"Query:$result ";
     // show results on screen for debugging purposes
     echo "total pages = ".$total_pages.", and ".$total_results." record(s) in the database <br />";
    
     echo"<p><table class=\"center\"><tr class=\"center\"><td class=\"center\"><b> Select a Page</b><br />";
     if($page>1){
                 $prev=($page-1);
                 echo"<a href='".$_SERVER['PHP_SELF']."?page=$prev&query=$query'><img src=\"images/leftarrow.gif\"
                 width='15' height='15' alt='click for previous records' border='0' alt=''></a> ";
                }
                    for($i=1;$i<=$total_pages;$i++){
                        if(($page)== $i){
                                          echo" <font color='#ff6655'><b>[ $i ]</b> &nbsp;</font>";
                                        }else{
                                                echo"<a href='".$_SERVER['PHP_SELF']."?page=$i&query=$query'><b>$i</b></a>&nbsp;";
                                             }
                                         }
                                     if($page<$total_pages){
                                             $next=($page+1);
                                             echo"<a href='".$_SERVER['PHP_SELF']."?page=$next&query=$query'><img src=\"images/rightarrow.gif\"
                                             width='15' height='15' alt='click for next records' border='0'></a>";
                                             }
                                             echo"</center></td></tr></table>";
    ?>
    You may notice that in the code I am echoing my variables to the screen, which is just something I like to do to keep track of things while I am developing.

    this is the error I get when clicking the next links:

    query value from list box =
    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 'LIMIT 2, 2' at line 1

    Oops forgot to mention that the first page results display correctly as follows:

    query value from list box = 7

    Query:select description, image, houseaddress from property WHERE housetypeID = 7 LIMIT 0, 2 total pages = 3, and 5 record(s) in the database

    as you can see I like to echo my variables while I code, it normally helps.
    It does appear that on clicking the 'next' links that the $query arguement is disappearing, although I am posting it over as in the following example:

    echo"<a href='".$_SERVER['PHP_SELF']."?page=$prev&query=$query'><img src=\"images/leftarrow.gif\"
    width='15' height='15' alt='click for previous records' border='0' alt=''></a> ";
    }
    Last edited by chris davies; Feb 18, 2007 at 07:37.

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query=$_POST['query']; 
    But you're passing it through the url, so you should use $_GET. Might also wanna validate the input (and not only $query):
    PHP Code:
    $query=(int)$_GET['query']; 
    Saul

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply, but using
    Code:
    $query=(int)$_GET['query'];
    just sets the value for $query to 0, so my search criteria is gone again. Let me explain further, $query is an integer value anyway, which is hard coded into the previous form, so while I see your concerns about clean input, all I want to do is search depending on what this posted value already is.

    I am trying to adapt a pagination script (not mine, got it from PHP Freaks a long time ago) so that I can search depending on a posted value. The script works ok if all I want to do is count and paginate every single row in the table, but this is not what I need.

    By echoing my variables I get this echoed to the page:

    posted query value from list box = 7

    Query: SELECT description, image, houseaddress FROM property WHERE housetypeID = 7 LIMIT 0, 2
    there are total 4 pages in total,
    and 8 record(s) in the database


    after clicking the next page link I get:

    posted query value from list box =
    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 'LIMIT 6, 2' at line 1



    Therefore my query displays correctly for the first page, then fails on any subsequent pages.

    I really do need help with this, if anyone could be of assistance and demonstrate to me exactly where I am going wrong.
    people in glass houses shouldn't walk around naked

  4. #4
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Don't trust the user input! So what if it's hard coded into the form, they can still change it.

    So you first post it, then pass it to the url query? Then try:
    PHP Code:
    if(isset($_POST['query'])){
      
    $query=(int)$_POST['query'];
    } elseif(isset(
    $_GET['query'])){
      
    $query=(int)$_GET['query'];
    } else {
      
    $query=0;

    Saul

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do appreciate all what you're saying about the security, and yes it is vitally important, but couldn't I change that later, this script is not live yet, at present it's running on my home system. The issue here is why doesn't the pagination work?
    people in glass houses shouldn't walk around naked

  6. #6
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Did your try my last tip? When you open the first page, the $query is posted through a form. But later on you pass it to the url, so you gotta access it through $_GET. So checking for $_POST then $_GET should work.
    Saul

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ermm, I must salute you sir. Bang dead on correct. Now only if I could understand WHY the code has to be like that?
    people in glass houses shouldn't walk around naked

  8. #8
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Well, as I said. The first time you open the page, you submit the form to it using the post method. So you get the $query from $_POST. But when you output the content and the links to the next pages, you pass the $query through the url, thus making $_GET necessary to get it.

    You could also use get method for the form, so that you can always get it from $_GET.

    Hope it makes sense.
    Saul

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I am so glad there are peeps like you around to help us lesser mortals, you've made my day. thank you
    people in glass houses shouldn't walk around naked

  10. #10
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You are very welcome
    Saul

  11. #11
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,933
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The three most important reasons why HTTP POST should not be used to pass the arguments to a search script are as follows:

    1) the request is NOT idempotent,
    2) the request is NOT idempotent,
    3) the request is NOT idempotent.

  12. #12
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bokehman View Post
    The three most important reasons why HTTP POST should not be used to pass the arguments to a search script are as follows:

    1) the request is NOT idempotent,
    2) the request is NOT idempotent,
    3) the request is NOT idempotent.
    You forget the fourth reason: the request is NOT idempotent.
    Saul


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •