Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts

    Pagination with PHP

    Hi all,

    I'm trying to add page pagination to my page but its causing the following 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 '15' at line 1

    I know it says its an SQL error but before I added the php pagination code the result were returning fine. Can anyone see any problems with the script?

    PHP Code:
    <?php

        $name 
    mysql_real_escape_string($_POST['name']);
        
    $lea mysql_real_escape_string($_POST['lea']);
        
    $type mysql_real_escape_string($_POST['type']);
        
    $postcode mysql_real_escape_string($_POST['postcode']);
        
        if (isset(
    $_GET['pageno'])) {
            
    $pageno $_GET['pageno'];
                } else {
            
    $pageno 1;
        }

        
    $query "SELECT count(*) FROM schools WHERE Name LIKE '%$name%' AND Leaname LIKE '%$lea%' AND SchoolType LIKE '%$type%' AND PostcodeSubArea LIKE '%$postcode' ORDER BY Name ASC";
            
    $result mysql_query($query) or trigger_error("SQL"E_USER_ERROR);
                
    $query_data mysql_fetch_row($result);
                    
    $numrows $query_data[0];

        
    $rows_per_page 15;
            
    $lastpage ceil($numrows/$rows_per_page);
            
        
    $pageno = (int)$pageno;
            if (
    $pageno $lastpage) {
                
    $pageno $lastpage;
        }
            if (
    $pageno 1) {
                
    $pageno 1;
        }
        
        
    $limit 'LIMIT'. ($pageno 1) * $rows_per_page',' $rows_per_page;
        
        
    $query "SELECT * FROM schools $limit";
            
    $result mysql_query($query) or die (mysql_error());
                while (
    $row mysql_fetch_array ($result)) {
            
    // PROCESS CONTENTS ?>
        <a href="schooldetails.php?RID=<?php echo $row['RID'];?>"><?php echo $row['Name'];?></a>    
        <?php        
        
    echo '<br/>';
        echo 
    '<br/>';
        echo 
    'LEA:'.' '.'<span class="normalfont">'.$row['Leaname'].'</span>';
        echo 
    '<br/>';
        echo 
    'Address:'.' '.'<span class="normalfont">'.$row['Address1'].'</span>';
        echo 
    '<br/>';
        echo 
    '<span class="normalfont">'.$row['Address2'].'</span>';
        echo 
    '<br/>';
        echo 
    '<span class="normalfont">'.$row['Address3'].'</span>';
        echo 
    '<br/>';
        echo 
    'Postcode:'.' '.'<span class="normalfont">'.$row['Postcode'].'</span>';
        echo 
    '<br/>';
        echo 
    'Telephone:'.' '.'<span class="normalfont">0'.$row['Phone'].'</span>';
        echo 
    '<br/>';
        echo 
    'Fax:'.' '.'<span class="normalfont">0'.$row['Fax'].'</span>';
        echo 
    '<br/>';?>
        Website: <span class="normalfont"><a href="<?php echo $row['url']?>"><?php echo $row['url'];?></a></span>
    <?php
        
    echo '<br/>';
        echo 
    '<br/>';
    }
        
        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>";
        }
        
        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_SELD']}?pageno=$lastpage'>LAST</a>";
        }
                
    ?>
    Thanks

  • #2
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Here is the error, or at least the first I came across:
    PHP Code:
    $query "SELECT * FROM schools $limit"
    I'm assuming the value if $limit is 15.

  • #3
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by kbluhm View Post
    Here is the error, or at least the first I came across:
    PHP Code:
    $query "SELECT * FROM schools $limit"
    I'm assuming the value if $limit is 15.
    I got the script from here:

    http://www.tonymarston.net/php-mysql/pagination.html

    I was actually assuming the limit variable was related to amount of results actually displayed on the page, not limiting the amount taken out of the database.... is this not the case?

  • #4
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    http://dev.mysql.com/doc/refman/5.0/en/select.html

    I see now in the code where $limit is assigned the value `LIMIT xxx`, so that is not the issue.

    Did you alter the code? Change this line:
    PHP Code:
    $limit 'LIMIT' .($pageno 1) * $rows_per_page .',' .$rows_per_page
    To this:
    PHP Code:
    $limit 'LIMIT ' .($pageno 1) * $rows_per_page .',' .$rows_per_page
    Last edited by kbluhm; 10-23-2009 at 04:25 PM.

  • #5
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by kbluhm View Post
    http://dev.mysql.com/doc/refman/5.0/en/select.html

    I see now in the code where $limit is assigned the value ` LIMIT xxx`, so that is not the issue.

    Did you alter the code? Change this line:
    PHP Code:
    $limit 'LIMIT' .($pageno 1) * $rows_per_page .',' .$rows_per_page
    To this:
    PHP Code:
    $limit 'LIMIT ' .($pageno 1) * $rows_per_page .',' .$rows_per_page
    The only code I've altered is the query, I've put a where clause in...

  • #6
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    That's weird, because in the link you provided, their code on that line contains: 'LIMIT ' (trailing space)

    But yours contains 'LIMIT' (no trailing space).

  • #7
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by kbluhm View Post
    That's weird, because in the link you provided, their code on that line contains: 'LIMIT ' (trailing space)

    But yours contains 'LIMIT' (no trailing space).
    Ah thats done the trick, not sure how it got in there.... but many thanks for your help... I would never of put an error down to a trailing space. I have a long way to go with PHP I think!

  • #8
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Actually think I may have spoken to soon on this one, as it seems to have stopped my search terms working... i.e. it splits results but doesnt take into account what search terms where entered!

    Should the pagination script have an affect on this?


  •  

    Posting Permissions

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