SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 44
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Really Struggling to Paginate Results MSSQL/PHP/PDO

    I'm trying to create a pagination script using a MSSQL database. I think I'm half way there, but finding limited resources to help get it finished up.

    So far, I can output the limited results and set the page numbers correctly. However, the next pages contain the same data.

    If somebody could take a look at this for me and let me know where I'm going wrong, that would be great.

    PHP Code:
    <?php 
    $sth 
    $dbconn->prepare ("SELECT     TOP (5) a.id, a.title, a.text, a.country, a.city, a.cat, a.user_id, a.use_ad, a.viewcount, a.tags, a.status, a.mod_status, publication_issue.img, 
                          publication_issue.publication_id, publication_issue.issue_number, publication_issue.upload_date, publication_issue.cat AS Expr1, 
                          publication_issue.viewcount AS Expr2, publication_issue.status AS Expr3, publication_issue.mod_status AS Expr4, publication_issue.user_id AS Expr5
    FROM         (SELECT     TOP (75) id, title, text, country, city, cat, user_id, use_ad, viewcount, tags, status, mod_status
                           FROM          publication
                           ORDER BY id) AS a INNER JOIN
                          publication_issue ON a.id = publication_issue.publication_id
    ORDER BY a.id DESC"
    ); 

    $sth->execute();

    while (
    $results $sth->fetchAll(PDO::FETCH_ASSOC)){
    //use $results
        
    $pages ceil(count($results)/4); 
        
    $page  = (isset ($_GET['page']))  ? (int) $_GET['page'] : ;
        
    $start = ($page 1) *  $per_page



    foreach (
    $results as $row){

    echo 
    "title: ".$row['title']."<br />";

    }




    }
        
    ?>
        <br><br>
        <?php
        
    if ($pages >=  && $page <= $pages){
        
    //if ($page>1 && $page <= $pages){$previous=($page -1); echo '<a href="?page=' .$previous. '">Previous</a>';}
        
    for($x=1$x<=$pages$x++){ echo ($x == $page) ? ' <strong><a href="?page='.$x.'">'.$x.'</a></strong> ' ' <a href="?page='.$x.'">'.$x.'</a> ';}
        
    //if ($page>1  && $page <= $pages){ $next=($page+1) ; echo '<a href="?page=' .$next. '">Next</a>';}
        
    }






    ?>

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, the problem is your query. You are using TOP(5) to limit your results, and that is not the appropriate way when working with paging. Personally I think Microsoft just hates all of us for not providing a nice way like MySQL did...

    So Let's start by redoing your query:
    The trick is to use OVER (which is also referred to as PARTITION BY)
    This process lets you specify a specific order for your results to be "numbered", thus allowing you to eventually ask for rows 1-5 or 6-10, etc.

    I also like to use a CTE (Common Table Expression) for this as it helps organize your query, but you don't have to use one.

    So with a CTE:
    PHP Code:
    $query = <<<QUERY
       WITH LimitPublications AS (
          SELECT 
            id
            , title
            , text
            , country
            , city
            , cat
            , user_id
            , use_ad
            , viewcount
            , tags
            , status
            , mod_status
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber' 
          FROM publication
       )
       SELECT 
         a.id
         , a.title
         , a.text
         , a.country
         , a.city
         , a.cat
         , a.user_id
         , a.use_ad
         , a.viewcount
         , a.tags
         , a.status
         , a.mod_status
         , publication_issue.img
         , publication_issue.publication_id
         , publication_issue.issue_number
         , publication_issue.upload_date
         , publication_issue.cat AS Expr1
         , publication_issue.viewcount AS Expr2
         , publication_issue.status AS Expr3
         , publication_issue.mod_status AS Expr4
         , publication_issue.user_id AS Expr5
       FROM LimitPublications AS a
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id
       WHERE a.RowNumber BETWEEN 0 AND 5
    QUERY;
    $sth $dbconn->prepare ($query); 
    Without a CTE:
    PHP Code:
    $query = <<<QUERY
       SELECT 
         a.id
         , a.title
         , a.text
         , a.country
         , a.city
         , a.cat
         , a.user_id
         , a.use_ad
         , a.viewcount
         , a.tags
         , a.status
         , a.mod_status
         , publication_issue.img
         , publication_issue.publication_id
         , publication_issue.issue_number
         , publication_issue.upload_date
         , publication_issue.cat AS Expr1
         , publication_issue.viewcount AS Expr2
         , publication_issue.status AS Expr3
         , publication_issue.mod_status AS Expr4
         , publication_issue.user_id AS Expr5
       FROM (
          SELECT 
            id
            , title
            , text
            , country
            , city
            , cat
            , user_id
            , use_ad
            , viewcount
            , tags
            , status
            , mod_status
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber' 
          FROM publication
       ) AS a
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id
       WHERE a.RowNumber BETWEEN 0 AND 5
    QUERY;
    $sth $dbconn->prepare ($query); 
    In both examples I used the HEREDOC syntax to assign the query to a variable so it is easy to read. If you want to bunch them up on one line, feel free.

    Also, you will need a separate query to get the number of records stored in your table so you can determine the number of pages you need, a simple "SELECT COUNT(*) AS TotalRows FROM publication" is perfect for that (granted if you wanted to be "clever" you could return MAX(a.RowNumber) on every record from the queries above and that will give it to you too).

    Hopefully this gets you past the brick wall and towards what you want to see being returned/shown.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Okay, the problem is your query. You are using TOP(5) to limit your results, and that is not the appropriate way when working with paging. Personally I think Microsoft just hates all of us for not providing a nice way like MySQL did...

    So Let's start by redoing your query:
    The trick is to use OVER (which is also referred to as PARTITION BY)
    This process lets you specify a specific order for your results to be "numbered", thus allowing you to eventually ask for rows 1-5 or 6-10, etc.

    I also like to use a CTE (Common Table Expression) for this as it helps organize your query, but you don't have to use one.

    So with a CTE:
    PHP Code:
    $query = <<<QUERY
       WITH LimitPublications AS (
          SELECT 
            id
            , title
            , text
            , country
            , city
            , cat
            , user_id
            , use_ad
            , viewcount
            , tags
            , status
            , mod_status
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber' 
          FROM publication
       )
       SELECT 
         a.id
         , a.title
         , a.text
         , a.country
         , a.city
         , a.cat
         , a.user_id
         , a.use_ad
         , a.viewcount
         , a.tags
         , a.status
         , a.mod_status
         , publication_issue.img
         , publication_issue.publication_id
         , publication_issue.issue_number
         , publication_issue.upload_date
         , publication_issue.cat AS Expr1
         , publication_issue.viewcount AS Expr2
         , publication_issue.status AS Expr3
         , publication_issue.mod_status AS Expr4
         , publication_issue.user_id AS Expr5
       FROM LimitPublications AS a
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id
       WHERE a.RowNumber BETWEEN 0 AND 5
    QUERY;
    $sth $dbconn->prepare ($query); 
    Without a CTE:
    PHP Code:
    $query = <<<QUERY
       SELECT 
         a.id
         , a.title
         , a.text
         , a.country
         , a.city
         , a.cat
         , a.user_id
         , a.use_ad
         , a.viewcount
         , a.tags
         , a.status
         , a.mod_status
         , publication_issue.img
         , publication_issue.publication_id
         , publication_issue.issue_number
         , publication_issue.upload_date
         , publication_issue.cat AS Expr1
         , publication_issue.viewcount AS Expr2
         , publication_issue.status AS Expr3
         , publication_issue.mod_status AS Expr4
         , publication_issue.user_id AS Expr5
       FROM (
          SELECT 
            id
            , title
            , text
            , country
            , city
            , cat
            , user_id
            , use_ad
            , viewcount
            , tags
            , status
            , mod_status
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber' 
          FROM publication
       ) AS a
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id
       WHERE a.RowNumber BETWEEN 0 AND 5
    QUERY;
    $sth $dbconn->prepare ($query); 
    In both examples I used the HEREDOC syntax to assign the query to a variable so it is easy to read. If you want to bunch them up on one line, feel free.

    Also, you will need a separate query to get the number of records stored in your table so you can determine the number of pages you need, a simple "SELECT COUNT(*) AS TotalRows FROM publication" is perfect for that (granted if you wanted to be "clever" you could return MAX(a.RowNumber) on every record from the queries above and that will give it to you too).

    Hopefully this gets you past the brick wall and towards what you want to see being returned/shown.
    Thanks for any help you give me, I've been posting around a few forums and help is quite hard to come by when it comes to MSSQL.

    I built the above query in the Visual Studio query builder, and got an output, but trying to return the values in PHP does nothing.

    How does this look?

    PHP Code:
    <?php
     
    $query 
    $dbconn->prepare("
       SELECT  
         a.id 
         , a.title 
         , a.text 
         , a.country 
         , a.city 
         , a.cat 
         , a.user_id 
         , a.use_ad 
         , a.viewcount 
         , a.tags 
         , a.status 
         , a.mod_status 
         , publication_issue.img 
         , publication_issue.publication_id 
         , publication_issue.issue_number 
         , publication_issue.upload_date 
         , publication_issue.cat AS Expr1 
         , publication_issue.viewcount AS Expr2 
         , publication_issue.status AS Expr3 
         , publication_issue.mod_status AS Expr4 
         , publication_issue.user_id AS Expr5 
       FROM ( 
          SELECT  
            id 
            , title 
            , text 
            , country 
            , city 
            , cat 
            , user_id 
            , use_ad 
            , viewcount 
            , tags 
            , status 
            , mod_status 
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'  
          FROM publication 
       ) AS a 
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id 
       WHERE a.RowNumber BETWEEN 0 AND 5 
    "
    );

    //It fails here
     
    while ($row $query->fetch(PDO::FETCH_ASSOC)) {


    $Num_Rows count($row);


     
    //Set limit per page
     
    $Per_Page 9;  // Per Page
     
    //Get the page number
     
    $Page $_GET["Page"];
     
    //Determine if it is the first page
     
    if(!$_GET["Page"])
    {
        
    $Page=1;
    }
     
    //Declare previous/next page row guide
     
    $Prev_Page $Page-1;
    $Next_Page $Page+1;
     
    //Determine page start
     
    $Page_Start = (($Per_Page*$Page)-$Per_Page);
    if(
    $Num_Rows<=$Per_Page)
    {
        
    $Num_Pages =1;
    }
    else if((
    $Num_Rows $Per_Page)==0)
    {
        
    $Num_Pages =($Num_Rows/$Per_Page) ;
    }
    else
    {
        
    $Num_Pages =($Num_Rows/$Per_Page)+1;
        
    $Num_Pages = (int)$Num_Pages;
    }
     
    //Determine where the page will end
     
    $Page_End $Per_Page $Page;
    IF (
    $Page_End $Num_Rows)
    {
        
    $Page_End $Num_Rows;
    }
     
    ?>
    <ul style="">
     
    <?
     
    //Make it show next rows
     
    for($i=$Page_Start;$i<$Page_End;$i++)
    {
    ?>
     
      <li><?php echo $row['title'];?></li>
     
     
     
    <?
    }
     
    echo 
    '
    </ul>
    '
    ;
     
    //Previous page
     
    if($Prev_Page)
    {
        echo 
    " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> ";
    }
     
    //Display total pages
     
    for($i=1$i<=$Num_Pages$i++){
        if(
    $i != $Page)
        {
            echo 
    "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;";
        }
        else
        {
            echo 
    "<b> $i </b>";
        }
    }
     
    //Create next page link
     
    if($Page!=$Num_Pages)
    {
        echo 
    " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> ";
    }
     
    //Adios
     
    }
    $sth null;
    ?>

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, I might be crazy here (as I'm still a bit new to PDO myself), but I believe you have to execute your prepared statement before you can fetch from it.

    However, before I get to that, there is a lot of re-working of your code that needs to happen, you have too much mixed into the while loop and you'll run into problems that way.

    Okay so for the code:
    PHP Code:
    <?php 
    $Per_Page 
    9;  // Per Page 
      
    //Get the page number 
      
    $Page 1;
      
    //Determine if it is the first page 
      
    if(isset($_GET["Page"]))

        
    $Page=(int)$_GET["Page"]; 
            if (
    $Page 1)
                
    $Page 1;


    $Page_Start = (($Per_Page*$Page)-$Per_Page); 
      
    $query $dbconn->prepare(
       SELECT   
         a.id  
         , a.title  
         , a.text  
         , a.country  
         , a.city  
         , a.cat  
         , a.user_id  
         , a.use_ad  
         , a.viewcount  
         , a.tags  
         , a.status  
         , a.mod_status  
         , publication_issue.img  
         , publication_issue.publication_id  
         , publication_issue.issue_number  
         , publication_issue.upload_date  
         , publication_issue.cat AS Expr1  
         , publication_issue.viewcount AS Expr2  
         , publication_issue.status AS Expr3  
         , publication_issue.mod_status AS Expr4  
         , publication_issue.user_id AS Expr5  
             , MAX(id) AS 'totalRecords'
       FROM (  
          SELECT   
            id  
            , title  
            , text  
            , country  
            , city  
            , cat  
            , user_id  
            , use_ad  
            , viewcount  
            , tags  
            , status  
            , mod_status  
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM publication  
       ) AS a  
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
       WHERE a.RowNumber BETWEEN ? AND ?  
    "
    ); 

    $totalRecords 0;
    $query->execute(array($Page_Start$Page_Start $Per_Page)); // more on this later
    ?>
    <ul style=""> 
    <?php
    while ($row $query->fetch(PDO::FETCH_ASSOC)) { 
        
    $totalRecords $row['totalRecords'];
    ?>
      <li><?php echo $row['title'];?></li> 
    <?php
    }
    ?>
    </ul>
    <?php
    $Num_Rows 
    $totalRecords
      
    //Declare previous/next page row guide 
      
    $Prev_Page $Page-1
    $Next_Page $Page+1;
      
    if(
    $Num_Rows<=$Per_Page

        
    $Num_Pages =1

    else if((
    $Num_Rows $Per_Page)==0

        
    $Num_Pages =($Num_Rows/$Per_Page) ; 

    else 

        
    $Num_Pages =($Num_Rows/$Per_Page)+1
        
    $Num_Pages = (int)$Num_Pages

      
    //Determine where the page will end 
      
    $Page_End $Per_Page $Page
    IF (
    $Page_End $Num_Rows

        
    $Page_End $Num_Rows

        
    //Previous page 
      
    if($Prev_Page

        echo 
    " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "

      
    //Display total pages 
      
    for($i=1$i<=$Num_Pages$i++){ 
        if(
    $i != $Page
        { 
            echo 
    "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"
        } 
        else 
        { 
            echo 
    "<b> $i </b>"
        } 

      
    //Create next page link 
      
    if($Page!=$Num_Pages

        echo 
    " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "

      
    //Adios
    $sth null
    ?>
    You'll quickly notice the first thing I did was move some of your logic to the top of your page, the $Per_Page, $Page, and $Page_Start are going to be important to your query.

    I also validated the $_GET['Page'] variable, to ensure we end up with a number we want (not 0, not -1, etc).

    I then updated the query and replaced 0 and 5 with ? and ? (placeholders). Next I added an execute statement passing the $Page_Start and the ($Page_Start + $Per_Page) so we are grabbing the records for the page we want. Those two values will replace the ? and ? I put in the query.

    Next we loop through the records and store the totalRecords column into $totalRecords (which will be used later -- oh, I added that column) and we echo the title from our query for each record in a List.

    Following the output, we store $totalRecords into $Num_Rows and continue on with your paging output (much of which I left unchanged).

    So give it a shot now and see if you get any output.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again, really appreciated.

    The script fails at the while loop, can't even echo out...

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, I altered the prepared statement to have array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) and I altered the fetch to contain PDO::FETCH_ORI_NEXT

    Not sure if either of those are the issue though... otherwise, you may need to get to your error log and see what error you are receiving.
    PHP Code:
    <?php 
    ini_set
    ('display_errors',1); // try turning on errors
    $Per_Page 9;  // Per Page 
      
    //Get the page number 
      
    $Page 1;
      
    //Determine if it is the first page 
      
    if(isset($_GET["Page"]))

        
    $Page=(int)$_GET["Page"]; 
            if (
    $Page 1)
                
    $Page 1;


    $Page_Start = (($Per_Page*$Page)-$Per_Page); 
      
    $query $dbconn->prepare(
       SELECT   
         a.id  
         , a.title  
         , a.text  
         , a.country  
         , a.city  
         , a.cat  
         , a.user_id  
         , a.use_ad  
         , a.viewcount  
         , a.tags  
         , a.status  
         , a.mod_status  
         , publication_issue.img  
         , publication_issue.publication_id  
         , publication_issue.issue_number  
         , publication_issue.upload_date  
         , publication_issue.cat AS Expr1  
         , publication_issue.viewcount AS Expr2  
         , publication_issue.status AS Expr3  
         , publication_issue.mod_status AS Expr4  
         , publication_issue.user_id AS Expr5  
             , MAX(id) AS 'totalRecords'
       FROM (  
          SELECT   
            id  
            , title  
            , text  
            , country  
            , city  
            , cat  
            , user_id  
            , use_ad  
            , viewcount  
            , tags  
            , status  
            , mod_status  
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM publication  
       ) AS a  
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
       WHERE a.RowNumber BETWEEN ? AND ?  
    "
    , array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 

    $totalRecords 0;
    $query->execute(array($Page_Start$Page_Start $Per_Page)); // more on this later
    ?>
    <ul style=""> 
    <?php
    while ($row $query->fetch(PDO::FETCH_ASSOCPDO::FETCH_ORI_NEXT)) { 
        
    $totalRecords $row['totalRecords'];
    ?>
      <li><?php echo $row['title'];?></li> 
    <?php
    }
    ?>
    </ul>
    <?php
    $Num_Rows 
    $totalRecords
      
    //Declare previous/next page row guide 
      
    $Prev_Page $Page-1
    $Next_Page $Page+1;
      
    if(
    $Num_Rows<=$Per_Page

        
    $Num_Pages =1

    else if((
    $Num_Rows $Per_Page)==0

        
    $Num_Pages =($Num_Rows/$Per_Page) ; 

    else 

        
    $Num_Pages =($Num_Rows/$Per_Page)+1
        
    $Num_Pages = (int)$Num_Pages

      
    //Determine where the page will end 
      
    $Page_End $Per_Page $Page
    IF (
    $Page_End $Num_Rows

        
    $Page_End $Num_Rows

        
    //Previous page 
      
    if($Prev_Page

        echo 
    " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "

      
    //Display total pages 
      
    for($i=1$i<=$Num_Pages$i++){ 
        if(
    $i != $Page
        { 
            echo 
    "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"
        } 
        else 
        { 
            echo 
    "<b> $i </b>"
        } 

      
    //Create next page link 
      
    if($Page!=$Num_Pages

        echo 
    " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "

      
    //Adios
    $sth null
    ?>
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still no luck, no errors either.

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it helps, in the VB Query Builder I get an error:

    Ambiguous column name 'id'

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Ah, yes, change , MAX(id) AS 'totalRecords' to be , MAX(a.id) AS 'totalRecords'
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, now receiving another error in the query:

    Column a.id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, let's simplify it a bit and do two queries to get the Total Records and the Limited Number of Records
    PHP Code:
    <?php 
    ini_set
    ('display_errors',1); // try turning on errors
    $Per_Page 9;  // Per Page 
      
    //Get the page number 
      
    $Page 1;
      
    //Determine if it is the first page 
      
    if(isset($_GET["Page"]))

        
    $Page=(int)$_GET["Page"]; 
            if (
    $Page 1)
                
    $Page 1;


    $Page_Start = (($Per_Page*$Page)-$Per_Page); 
      
    $query $dbconn->prepare(
       SELECT   
         a.id  
         , a.title  
         , a.text  
         , a.country  
         , a.city  
         , a.cat  
         , a.user_id  
         , a.use_ad  
         , a.viewcount  
         , a.tags  
         , a.status  
         , a.mod_status  
         , publication_issue.img  
         , publication_issue.publication_id  
         , publication_issue.issue_number  
         , publication_issue.upload_date  
         , publication_issue.cat AS Expr1  
         , publication_issue.viewcount AS Expr2  
         , publication_issue.status AS Expr3  
         , publication_issue.mod_status AS Expr4  
         , publication_issue.user_id AS Expr5  
       FROM (  
          SELECT   
            id  
            , title  
            , text  
            , country  
            , city  
            , cat  
            , user_id  
            , use_ad  
            , viewcount  
            , tags  
            , status  
            , mod_status  
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM publication  
       ) AS a  
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
       WHERE a.RowNumber BETWEEN ? AND ?  
    "
    , array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 

    $query->execute(array($Page_Start$Page_Start $Per_Page)); // more on this later
    ?>
    <ul style=""> 
    <?php
    while ($row $query->fetch(PDO::FETCH_ASSOCPDO::FETCH_ORI_NEXT)) { 
        
    $totalRecords $row['totalRecords'];
    ?>
      <li><?php echo $row['title'];?></li> 
    <?php
    }
    ?>
    </ul>
    <?php
    $query 
    $dbconn->prepare("SELECT COUNT(*) AS TotalRecords FROM publication");
    $query->execute();
    $totalRecords $query->fetch(PDO::FETCH_ASSOC);

    $Num_Rows $totalRecords["TotalRecords"]; 
      
    //Declare previous/next page row guide 
      
    $Prev_Page $Page-1
    $Next_Page $Page+1;
      
    if(
    $Num_Rows<=$Per_Page

        
    $Num_Pages =1

    else if((
    $Num_Rows $Per_Page)==0

        
    $Num_Pages =($Num_Rows/$Per_Page) ; 

    else 

        
    $Num_Pages =($Num_Rows/$Per_Page)+1
        
    $Num_Pages = (int)$Num_Pages

      
    //Determine where the page will end 
      
    $Page_End $Per_Page $Page
    IF (
    $Page_End $Num_Rows

        
    $Page_End $Num_Rows

        
    //Previous page 
      
    if($Prev_Page

        echo 
    " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "

      
    //Display total pages 
      
    for($i=1$i<=$Num_Pages$i++){ 
        if(
    $i != $Page
        { 
            echo 
    "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"
        } 
        else 
        { 
            echo 
    "<b> $i </b>"
        } 

      
    //Create next page link 
      
    if($Page!=$Num_Pages

        echo 
    " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "

      
    //Adios
    $sth null
    ?>
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  12. #12
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, I think we're making progress.

    We now have the results displaying.

    The publication is 1 to many publication_issue so I switched the second (count) query to publication_issue which outputs 15 so that is now correct, but all 15 results are showing on page 1 and none on page 2 or 3.

    Would it help if I send you a dump of the data in those 2 tables?

  13. #13
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scottlpool2003 View Post
    Right, I think we're making progress.

    We now have the results displaying.

    The publication is 1 to many publication_issue so I switched the second (count) query to publication_issue which outputs 15 so that is now correct, but all 15 results are showing on page 1 and none on page 2 or 3.
    That's definitely progress, so we still have an issue (that's okay).

    Quote Originally Posted by scottlpool2003 View Post
    Would it help if I send you a dump of the data in those 2 tables?
    No, I can help you debug it (it will be a bit slower, but in the end, you'll pick up some tricks

    So let's start out figuring out why page 2 and 3 show nothing. I need to see what the script is receiving for input, and how that affects its output, so add this line before "$query->execute(array($Page_Start, $Page_Start + $Per_Page));":

    PHP Code:
    var_dump($_GET$Page_Start); 
    What does that line do? It will write out all of the variables we receive in the URL (such as the Page Number), and it will tell us how $Page_Start got calculated.

    What I need you to do, is copy and paste the output from that line and post it here for me to see it.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  14. #14
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the slow reply, didn't get chance to test over the weekend.

    Having posted just before the execute I see:

    array(2) { ["id"]=> string(0) "" ["Page"]=> string(1) "1" } int(0)
    Ahhh, I see the problem, there's nothing in the db query to say what to fetch. In this part of the query:

    WHERE a.RowNumber BETWEEN ? AND ?
    How can I integrate:

    WHERE tags LIKE :tags
    Thanks again for your help, greatly appreciated.

    EDIT

    I changed it to:

    PHP Code:
       WHERE a.RowNumber BETWEEN ? AND ? AND tags LIKE :tags 
    And while I'm still getting the correct output $_ROW['title']; its still all on one page, so on the second query: COUNT(*) I did an echo and got a result of 4 on $Num_Rows which isn't correct, I should be getting a result of 15 divided by 5 per page.

    Any ideas?

  15. #15
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scottlpool2003 View Post
    How can I integrate :tags:
    Okay, something else must be going on, as you shouldn't see all rows...

    So let's update the the query section to this:
    PHP Code:
    $query $dbconn->prepare(
       SELECT   
         a.id  
         , a.title  
         , a.text  
         , a.country  
         , a.city  
         , a.cat  
         , a.user_id  
         , a.use_ad  
         , a.viewcount  
         , a.tags  
         , a.status  
         , a.mod_status  
         , publication_issue.img  
         , publication_issue.publication_id  
         , publication_issue.issue_number  
         , publication_issue.upload_date  
         , publication_issue.cat AS Expr1  
         , publication_issue.viewcount AS Expr2  
         , publication_issue.status AS Expr3  
         , publication_issue.mod_status AS Expr4  
         , publication_issue.user_id AS Expr5  
       FROM (  
          SELECT   
            id  
            , title  
            , text  
            , country  
            , city  
            , cat  
            , user_id  
            , use_ad  
            , viewcount  
            , tags  
            , status  
            , mod_status  
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM publication  
       ) AS a  
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
       WHERE a.RowNumber BETWEEN :start AND :end AND tags LIKE :tags  
    "
    , array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 

    $Page_End $Page_Start $Per_Page;
    var_dump($_GET$Page_Start$Page_End$Per_Page); // used to figure out what is being placed below
    $query->execute(array(':start'=>$Page_Start':end'=>$Page_End,':tags'=>'MyTag')); 
    Quote Originally Posted by scottlpool2003 View Post
    And while I'm still getting the correct output $_ROW['title']; its still all on one page, so on the second query: COUNT(*) I did an echo and got a result of 4 on $Num_Rows which isn't correct, I should be getting a result of 15 divided by 5 per page.

    Any ideas?
    The two queries are unrelated (or at least they should be). So let's start small, take the following queries and run them in SQL Server and tell me how many rows you get and for the second query, tell me what the TotalRecords column says.

    Code SQL:
       SELECT   
         a.id  
         , a.title  
         , a.text  
         , a.country  
         , a.city  
         , a.cat  
         , a.user_id  
         , a.use_ad  
         , a.viewcount  
         , a.tags  
         , a.STATUS  
         , a.mod_status  
         , publication_issue.img  
         , publication_issue.publication_id  
         , publication_issue.issue_number  
         , publication_issue.upload_date  
         , publication_issue.cat AS Expr1  
         , publication_issue.viewcount AS Expr2  
         , publication_issue.STATUS AS Expr3  
         , publication_issue.mod_status AS Expr4  
         , publication_issue.user_id AS Expr5  
       FROM (  
          SELECT   
            id  
            , title  
            , text  
            , country  
            , city  
            , cat  
            , user_id  
            , use_ad  
            , viewcount  
            , tags  
            , STATUS  
            , mod_status  
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM publication  
       ) AS a  
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
       WHERE a.RowNumber BETWEEN 0 AND 9 AND tags LIKE 'MyTag'

    Code SQL:
    SELECT COUNT(*) AS TotalRecords FROM publication
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  16. #16
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay...

    The first updated code outputs:

    array(1) { ["id"]=> string(6) "Search" } int(0) int(5) int(5)
    id: Title 1
    id: Title 2
    id: Title 3
    id: Title 4
    id: Title 5
    id: Title 6
    id: Title 7
    id: Title 8
    id: Title 9
    id: Title 10
    id: Title 11
    id: Title 12
    id: Title 13
    id: Title 14
    id: Title 15
    The query I entered into query builder first prompts an error:

    The OVER SQL construct or statement is not supported.
    Then when I click continue, I see the column names with no data in them.

    The final query in query builder outputs:

    TotalRecords
    4

  17. #17
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,108
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    This will seem like a troll but this is precisely why I love ActiveRecord and gems like will_paginate. You simply don't need to worry about this stuff anymore.

    Code ruby:
    Publication.paginate(page: params[:page])

  18. #18
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by markbrown4 View Post
    This will seem like a troll but this is precisely why I love ActiveRecord and gems like will_paginate. You simply don't need to worry about this stuff anymore.

    Code ruby:
    Publication.paginate(page: params[:page])
    I'd rather stick to what I know, I've never coded in Ruby before and don't envision needing to whereas I will use MSSQL/PHP over and over again.

  19. #19
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,108
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    It depends what you're doing this for I guess.. if you are a full time web developer sticking to what you know isn't a good rule.
    You're probably right though that for this case moving to Ruby isn't the right solution, I'm just illustrating that a lot of the complex scripts that people write don't need to be written at all most of the time.

    Here's what the full script would look like:

    Code ruby:
    # publications_controller.rb
    @publications = Publication.paginate(page: params[:page]
     
    # publications/index.haml
    - @publications.each do |publication|
      title:
      = publication.title
      %br
    = will_paginate(@publications)

  20. #20
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are right. I'm still quite new to developing and haven't come across the need for Ruby yet within the project I'm currently working on. I probably will in the future though, I'm quite open minded to learning new languages and want to get into iPhone development which uses JSON naturally and would give me a good head start with Ruby.

  21. #21
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scottlpool2003 View Post
    Okay...

    The first updated code outputs:


    The query I entered into query builder first prompts an error:



    Then when I click continue, I see the column names with no data in them.

    The final query in query builder outputs:
    Okay, post your entire file again, as there is something wrong, as when I run the following; I get the correct values:
    Code SQL:
    CREATE TABLE #Temp 
    (
    	id INT,
    	title VARCHAR(10)
    )
     
    CREATE TABLE #Pub
    (
    	publication_id INT,
    	viewcount INT
    )
     
    INSERT INTO #Temp VALUES (1, 'Title 1')
    INSERT INTO #Temp VALUES (2, 'Title 2')
    INSERT INTO #Temp VALUES (3, 'Title 3')
    INSERT INTO #Temp VALUES (4, 'Title 4')
    INSERT INTO #Temp VALUES (5, 'Title 5')
    INSERT INTO #Temp VALUES (6, 'Title 6')
    INSERT INTO #Temp VALUES (7, 'Title 7')
    INSERT INTO #Temp VALUES (8, 'Title 8')
    INSERT INTO #Temp VALUES (9, 'Title 9')
    INSERT INTO #Temp VALUES (10, 'Title 10')
    INSERT INTO #Temp VALUES (11, 'Title 11')
    INSERT INTO #Temp VALUES (12, 'Title 12')
    INSERT INTO #Temp VALUES (13, 'Title 13')
    INSERT INTO #Temp VALUES (14, 'Title 14')
    INSERT INTO #Temp VALUES (15, 'Title 15')
     
    INSERT INTO #Pub VALUES (1, 3)
    INSERT INTO #Pub VALUES (2, 1)
    INSERT INTO #Pub VALUES (3, 6)
    INSERT INTO #Pub VALUES (4, 8)
    INSERT INTO #Pub VALUES (5, 2)
    INSERT INTO #Pub VALUES (6, 5)
    INSERT INTO #Pub VALUES (7, 7)
    INSERT INTO #Pub VALUES (8, 9)
    INSERT INTO #Pub VALUES (9, 0)
    INSERT INTO #Pub VALUES (10, 1)
    INSERT INTO #Pub VALUES (11, 6)
    INSERT INTO #Pub VALUES (12, 3)
    INSERT INTO #Pub VALUES (13, 8)
    INSERT INTO #Pub VALUES (14, 6)
    INSERT INTO #Pub VALUES (15, 9)
     
    SELECT   
         a.id  
         , a.title
         , p.viewcount
       FROM (  
          SELECT   
            id  
            , title   
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM #Temp  
       ) AS a  
         INNER JOIN #Pub AS p ON a.id = p.publication_id  
       WHERE a.RowNumber BETWEEN 0 AND 9 -- AND tags LIKE 'MyTag'
     
    SELECT COUNT(*) AS TotalRecords FROM #Temp
     
    DROP TABLE #Temp
    DROP TABLE #Pub
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  22. #22
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your query above outputs records 7 through 15.

    Here is my whole script:

    PHP Code:
    <?php 
    ini_set
    ('display_errors',1); // try turning on errors
    $Per_Page 5;  // Per Page 
      
    //Get the page number 
      
    $Page 1;
      
    //Determine if it is the first page 
      
    if(isset($_GET["Page"]))

        
    $Page=(int)$_GET["Page"]; 
            if (
    $Page 1)
                
    $Page 1;


    $Page_Start = (($Per_Page $Page)-$Per_Page); 
      
    $query $dbconn->prepare(
       SELECT   
         a.id  
         , a.title  
         , a.text  
         , a.country  
         , a.city  
         , a.cat  
         , a.user_id  
         , a.use_ad  
         , a.viewcount  
         , a.tags  
         , a.status  
         , a.mod_status  
         , publication_issue.img  
         , publication_issue.publication_id  
         , publication_issue.id AS issue_id
         , publication_issue.issue_number  
         , publication_issue.upload_date  
         , publication_issue.cat AS Expr1  
         , publication_issue.viewcount AS Expr2  
         , publication_issue.status AS Expr3  
         , publication_issue.mod_status AS Expr4  
         , publication_issue.user_id AS Expr5  
       FROM (  
          SELECT   
            id  
            , title  
            , text  
            , country  
            , city  
            , cat  
            , user_id  
            , use_ad  
            , viewcount  
            , tags  
            , status  
            , mod_status  
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM publication  
       ) AS a  
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
       WHERE a.RowNumber BETWEEN :start AND :end AND tags LIKE :tags  
    "
    , array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 
    $Page_End $Page_Start $Per_Page;
    $query->execute(array(':start'=>$Page_Start':end'=>$Page_End,':tags'=>'%e%'));  
    var_dump($_GET$Page_Start$Page_End$Per_Page); // used to figure out what is being placed below

    ?>
    <ul style=""> 
    <?php
    while ($row $query->fetch(PDO::FETCH_ASSOCPDO::FETCH_ORI_NEXT)) { 
        
    $totalRecords $row['totalRecords'];
    ?>
      <li><?php echo "id: ".$row['issue_id']." ".$row['title'];?></li> 
    <?php
    }
    ?>
    </ul>
    <?php
    $query 
    $dbconn->prepare("SELECT COUNT(*) AS TotalRecords FROM publication");
    $query->execute();
    $totalRecords $query->fetch(PDO::FETCH_ASSOC);

    $Num_Rows $totalRecords["TotalRecords"]; 
    //echo $Num_Rows;

      
    //Declare previous/next page row guide 
      
    $Prev_Page $Page-1
    $Next_Page $Page+1;
      
    if(
    $Num_Rows<=$Per_Page

        
    $Num_Pages =1

    else if((
    $Num_Rows $Per_Page)==0

        
    $Num_Pages =($Num_Rows/$Per_Page) ; 

    else 

        
    $Num_Pages =($Num_Rows/$Per_Page)+1
        
    $Num_Pages = (int)$Num_Pages

      
    //Determine where the page will end 
      
    $Page_End $Per_Page $Page
    IF (
    $Page_End $Num_Rows

        
    $Page_End $Num_Rows


        
    //Previous page 
      
    if($Prev_Page

        echo 
    " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "

      
    //Display total pages 
      
    for($i=1$i<=$Num_Pages$i++){ 
        if(
    $i != $Page
        { 
            echo 
    "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"
        } 
        else 
        { 
            echo 
    "<b> $i </b>"
        } 

      
    //Create next page link 
      
    if($Page!=$Num_Pages

        echo 
    " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "

      
    //Adios
    $sth null
    ?>

  23. #23
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scottlpool2003 View Post
    Your query above outputs records 7 through 15.
    Yes, it would because it is numbering the rows by ID descending. Thus 15 through 7 is exactly what I asked for.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  24. #24
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'm stumped. But try this code
    PHP Code:
    <?php 
    ini_set
    ('display_errors',1); // try turning on errors
    $Per_Page 5;  // Per Page 
      
    //Get the page number 
      
    $Page 1;
      
    //Determine if it is the first page 
      
    if(isset($_GET["Page"]))

        
    $Page=(int)$_GET["Page"]; 
            if (
    $Page 1)
                
    $Page 1;


    $Page_Start = (($Per_Page $Page)-$Per_Page) + 1;  //added plus 1
      
    $query $dbconn->prepare(
       SELECT   
         a.id  
         , a.title  
         , a.text  
         , a.country  
         , a.city  
         , a.cat  
         , a.user_id  
         , a.use_ad  
         , a.viewcount  
         , a.tags  
         , a.status  
         , a.mod_status  
         , publication_issue.img  
         , publication_issue.publication_id  
         , publication_issue.id AS issue_id
         , publication_issue.issue_number  
         , publication_issue.upload_date  
         , publication_issue.cat AS Expr1  
         , publication_issue.viewcount AS Expr2  
         , publication_issue.status AS Expr3  
         , publication_issue.mod_status AS Expr4  
         , publication_issue.user_id AS Expr5  
       FROM (  
          SELECT   
            id  
            , title  
            , text  
            , country  
            , city  
            , cat  
            , user_id  
            , use_ad  
            , viewcount  
            , tags  
            , status  
            , mod_status  
            , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
          FROM publication  
       ) AS a  
         INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
       WHERE a.RowNumber BETWEEN :start AND :end AND tags LIKE :tags  
    "
    , array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 
    $Page_End $Page_Start $Per_Page;
    $query->execute(array(':start'=>$Page_Start':end'=>$Page_End,':tags'=>'%e%'));  
    $publications $query->fetchAll(); // added this line

    var_dump($_GET$Page_Start$Page_End$Per_Page); // used to figure out what is being placed below
    var_dump($publications);
    ?>
    <ul style=""> 
    <?php
    foreach ($publications as $row) { // change while loop to foreach
    ?>
      <li><?php echo "id: ".$row['issue_id']." ".$row['title'];?></li> 
    <?php
    }
    ?>
    </ul>
    <?php
    $query 
    $dbconn->prepare("SELECT COUNT(*) AS TotalRecords FROM publication");
    $query->execute();
    $totalRecords $query->fetch(PDO::FETCH_ASSOC);

    var_dump($totalRecords);

    $Num_Rows $totalRecords["TotalRecords"]; 
    //echo $Num_Rows;

      
    //Declare previous/next page row guide 
      
    $Prev_Page $Page-1
    $Next_Page $Page+1;
      
    if(
    $Num_Rows<=$Per_Page

        
    $Num_Pages =1

    else if((
    $Num_Rows $Per_Page)==0

        
    $Num_Pages =($Num_Rows/$Per_Page) ; 

    else 

        
    $Num_Pages =($Num_Rows/$Per_Page)+1
        
    $Num_Pages = (int)$Num_Pages

      
    //Determine where the page will end 
      
    $Page_End $Per_Page $Page
    IF (
    $Page_End $Num_Rows

        
    $Page_End $Num_Rows


        
    //Previous page 
      
    if($Prev_Page

        echo 
    " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "

      
    //Display total pages 
      
    for($i=1$i<=$Num_Pages$i++){ 
        if(
    $i != $Page
        { 
            echo 
    "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"
        } 
        else 
        { 
            echo 
    "<b> $i </b>"
        } 

      
    //Create next page link 
      
    if($Page!=$Num_Pages

        echo 
    " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "

      
    //Adios
    $sth null
    ?>
    Copy and Paste the output from the var_dump calls into your response so I can see what you are getting.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  25. #25
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So does this indicate a problem with the script or my table data?


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
  •