My auto increment primary key is plastic_ID and the table name is plastic with two column names of ename and iname. I am not sure of the proper syntax and placement of the session vars of $lastid and $firstid in my code.

You gave me 2 SELECT statements. Can I ******* that your first SELECT statement replaces my $query statement and your second SELECT statement replaces my $sql statement? If not, I want to understand where to put your two SELECT statements. With plastic_ID as the primary key then the two statements you gave me would be:
$query = "SELECT TOP 5
FROM plastic WHERE plastic_ID > $lastid ORDER BY plastic_ID";
$sql = "SELECT FROM (SELECT TOP 5 FROM plastic WHERE plastic_ID < $firstid ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
Would this be correct?
I have $page = isset($GET["page"]) ? $GET["page"] : 1; on line 9 and am not sure that "page" is correct.

I appreciate your reply. Thank you.

    lscruiser;10932939 wrote:

    My auto increment primary key is plastic_ID and the table name is plastic with two column names of ename and iname. I am not sure of the proper syntax and placement of the session vars of $lastid and $firstid in my code.

    You gave me 2 SELECT statements. Can I ******* that your first SELECT statement replaces my $query statement and your second SELECT statement replaces my $sql statement? If not, I want to understand where to put your two SELECT statements. With plastic_ID as the primary key then the two statements you gave me would be:
    $query = "SELECT TOP 5
    FROM plastic WHERE plastic_ID > $lastid ORDER BY plastic_ID";
    $sql = "SELECT FROM (SELECT TOP 5 FROM plastic WHERE plastic_ID < $firstid ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
    Would this be correct?
    I have $page = isset($GET["page"]) ? $GET["page"] : 1; on line 9 and am not sure that "page" is correct.

    I appreciate your reply. Thank you.

    1. DO NOT USE GET
      get creates dirty urls, so just use post for the form submit.

    2 The previous and next buttons should be on SUBMIT buttons on seperate forms on the page so that the submit buttons POST the request, not dirty utl links sending GETS

    if (isset($_POST['submit'])) {
    
      switch ($_POST['direction']) {
      	case 'next':
      		$query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_SESSION['lastid'] . " ORDER BY plastic_ID";
      		break;
      	case 'prev':
      		$query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";;
      		break;
      	default:
      		$query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";
      	}
    
     }
    

    3 As you build your result display, you can store the id of the first result either in a session var, as I show, or you can put it into a hidden field in the Prev form. Likewise for the last id.

    if (isset($_POST['submit'])) {
    
      switch ($_POST['direction']) {
      	case 'next':
      		$query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_POST['lastid'] . " ORDER BY plastic_ID";
      		break;
      	case 'prev':
      		$query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_POST['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";;
      		break;
      	default:
      		$query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";
      	}
    
     }
    

    Up to you really.

      Thank you for your reply. I took your code and replaced it with my code. The result is seeing all entries (not 5 at a time as desired) and the pagination links do not link to the NEXT or PREV pages.
      I appreciate your response and help.
      Here is my updated code.

      <?php
      $db = odbc_connect('','',''); //put your connection here
      function inv_rows($r1) {
      ob_start();
      (int)$number=odbc_result_all($r1);
      ob_clean();
      return $number;
      }
      $page = isset($POST["page"]) ? $POST["page"] : 1;
      if(empty($page)){$page = 1; }
      $query = "SELECT FROM plastic"; // name of table is plastic with columns plastic_ID, ename, and iname.

      $num_result = odbc_exec($db, $query);
      $numrows = inv_rows($num_result);

      echo '<p>There are '.$numrows.' ideas.</p>';
      $limit = 5;
      $limitvalue = $page
      $limit - ($limit);
      $limitnew = $limitvalue + $limit;

      if (isset($POST['submit'])) {
      switch ($
      POST['direction']) {
      case 'next':
      $query = "SELECT TOP 5 FROM plastic WHERE plastic_ID > " . $SESSION['lastid'] . " ORDER BY plastic_ID";
      break;
      case 'prev':
      $query = "SELECT
      FROM (SELECT TOP 5 FROM plastic WHERE plastic_ID < " . $SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
      break;
      default:
      $query = "SELECT TOP 5
      FROM plastic ORDER BY plastic_ID";
      }

      }

      $result = odbc_exec($db, $query);
      while(odbc_fetch_row($result)){

      ?>
      <table style="width: 600;">

      <tr>
      <td style="width: 300; height: 25px;">Name:</td>
      <td style="width: 300; height: 25px;">Idea Name:</td>
      </tr>

      <tr>
      <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
      <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
      </tr>
      <tr>
      <td colspan="5" style="height: 25px"><hr/></td>
      </tr>
      </table>
      <?php //PREVIOUS AND NEXT BUTTONS
      }
      if($page !=1){
      $pageprev = $page - 1;
      echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; }
      else{ echo "&nbsp;PREV&nbsp;"; }
      $numofpages = $numrows / $limit;
      for($i = 1; $i <= $numofpages; ++$i){
      if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
      else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
      }
      if(($numrows % $limit) != 0){
      if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
      else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
      }
      if(($numrows - ($limit * $page)) > 0){
      $pagenext = $page + 1;
      echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }
      else{ echo "&nbsp;NEXT&nbsp;"; }
      odbc_free_result($result);

      exit;
      ?>

        <?php
        $db = odbc_connect('','',''); //put your connection here
        function inv_rows($r1) {
        ob_start();
        (int)$number=odbc_result_all($r1);
        ob_clean();
        return $number;
        }
        $page = isset($_POST["page"]) ? $_POST["page"] : 1;
        if(empty($page)){$page = 1; }
        $query = "SELECT * FROM plastic"; // name of table is plastic with columns plastic_ID, ename, and iname.
        $num_result = odbc_exec($db, $query);
        $numrows = inv_rows($num_result);
        echo '<p>There are '.$numrows.' ideas.</p>';
        $limit = 5;
        $limitvalue = $page * $limit - ($limit);
        $limitnew = $limitvalue + $limit;
        if (isset($_POST['submit'])) {
        switch ($_POST['direction']) {
        case 'next':
        $query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_SESSION['lastid'] . " ORDER BY plastic_ID";
        break;
        case 'prev':
        $query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
        break;
        default:
        $query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";
        }
        }
        $result = odbc_exec($db, $query);
        while(odbc_fetch_row($result)){
        ?>
        <table style="width: 600;">
        <tr>
        <td style="width: 300; height: 25px;">Name:</td>
        <td style="width: 300; height: 25px;">Idea Name:</td>
        </tr>
        <tr>
        <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
        <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
        </tr>
        <tr>
        <td colspan="5" style="height: 25px"><hr/></td>
        </tr>
        </table>
        <?php //PREVIOUS AND NEXT BUTTONS
        }
        if($page !=1){
        $pageprev = $page - 1;
        echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; }
        else{ echo "&nbsp;PREV&nbsp;"; }
        $numofpages = $numrows / $limit;
        for($i = 1; $i <= $numofpages; ++$i){
        if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
        else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
        }
        if(($numrows &#37; $limit) != 0){
        if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
        else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
        }
        if(($numrows - ($limit * $page)) > 0){
        $pagenext = $page + 1;
        echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }
        else{ echo "&nbsp;NEXT&nbsp;"; }
        odbc_free_result($result);
        exit;
        ?>
        

        Please learn to use the php tags

          Excuse me for not formating my question correctly. Hopefully it is formated correctly now. My question is still unanswered form the previous post.
          I took your code and replaced it with my code. The result is seeing all entries (not 5 at a time as desired) and the pagination links do not link to the NEXT or PREV pages.
          I appreciate your response and help.
          Here is my code.

          <?php 
          $db = odbc_connect('',''',''');  //put your connection here 
          function inv_rows($r1)  { 
          ob_start(); 
          (int)$number=odbc_result_all($r1); 
          ob_clean(); 
          return $number; 
          } 
          $page = isset($_POST["page"]) ? $_POST["page"] : 1;
          if(empty($page)){$page = 1; } 
          $query = "SELECT * FROM plastic"; // name of table is plastic with columns plastic_ID, ename, and iname.    	    
          $num_result = odbc_exec($db, $query); $numrows = inv_rows($num_result);
          echo '<p>There are '.$numrows.' ideas.</p>'; $limit = 5; $limitvalue = $page * $limit - ($limit); $limitnew = $limitvalue + $limit; if (isset($_POST['submit'])) { switch ($_POST['direction']) { case 'next': $query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_SESSION['lastid'] . " ORDER BY plastic_ID"; break; case 'prev': $query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC"; break; default: $query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID"; }
          } $result = odbc_exec($db, $query); while(odbc_fetch_row($result)){
          ?> <table style="width: 600;">
          <tr> <td style="width: 300; height: 25px;">Name:</td> <td style="width: 300; height: 25px;">Idea Name:</td> </tr>
          <tr> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> </tr> <tr> <td colspan="5" style="height: 25px"><hr/></td> </tr> </table> <?php //PREVIOUS AND NEXT BUTTONS } if($page !=1){ $pageprev = $page - 1; echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; } else{ echo "&nbsp;PREV&nbsp;"; } $numofpages = $numrows / $limit; for($i = 1; $i <= $numofpages; ++$i){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } } if(($numrows % $limit) != 0){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } } if(($numrows - ($limit * $page)) > 0){ $pagenext = $page + 1; echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } else{ echo "&nbsp;NEXT&nbsp;"; } odbc_free_result($result);
          exit; ?>

            OK. I don't know where you got the design for this pagination, but it is not going to work with an NS db that uses TOP.

            You can find lots of posts and solutions here by searching for pagination. In fact every question you have has been answered many many times here. Except of course that they are for MySQL.

            With mysql you have LIMIT with an offset, so using the page number is OK as one can calculate the offset from the page number and posts-per-page.

            Using TOP one can not calculate anything from the page number. So stop trying to use it and use the firstid, lastid as I said. Either save them as session vars, or put them in the form data. Then you can use them in the queries as I have shown. The reason for this is that there is no real chance that page 5 times per-page 10 = 50 will correspond to the id in any way no matter what db engine you are using. So you can not use id > 50 to get the next page list.

              You are correct in saying there are many solutions for paginations with MySQL. However, I am using an ODBC connection (not my choice) with MSSQL (not my choice) written in PHP. I have not found a workable solution with an ODBC connection to a MSSQL database written in PHP. If you have an example you could share, I would appreciate it. Thank you for your help.

                Actually, I dont know about ODBC (never used it) but there IS a way to use NESTED queries with SELECT TOP with MSSQL to get Paged Results, but you must have a defined primary key.

                $max = 15; // Number Results/Page
                $start = 61 // This would be the where you start, i.e. 16, 31 (1 after last result item)
                
                $query = "SELECT TOP $max * FROM [table] WHERE [column] = 'yes' AND ([primary_key] NOT IN (SELECT TOP $start [primary_key] FROM [table] ORDER BY [primary key])) ";
                

                I hope this made sense, its effectively LIKE using LIMIT clause with MySQL.

                This is less efficient because it does use nested queries, but it works.

                  Yep, that will work Nerd. Nice one 🆒

                  lscruiser, OK, to do this with breadcrumbs to many/any page, you need Nerd's solution. User submits for page 5, 10 records per page. So the subquery has TOP 40. The the outer query selects TOP 10 ... NOT IN. Hope you get that.

                  $perpage = 10;
                  ...
                  ...
                  if ($_POST['submit']) {
                  
                  $page = $_POST['page'];
                  $top = ($page - 1) * $perpage;
                  $sql = "SELECT TOP $perpage * FROM tbl WHERE tblid NOT IN (SELECT TOP $top tblid FROM tbl ORDER BY tblid) ORDER BY tblid"; 	
                  // etc	
                  }
                  

                  Had not thought of that. We live and learn.

                  Now, if it is MickeySoft SQL then you can create a stored proc for this, just feed it the page number and the per-page and off you go. Same-Same for mysql of course.

                  PS Nerd, why the select == yes? I know mssql can be dickey about IN and EXISTS but I still don't get it.

                  My thinking on this was just to have Previous/Next, but that way you can not jump to eg page 5 :queasy:

                    oh sorry that was copied from something that I was forced to move from MySQL to MSSQL, it was an enumeration yes or no, as the previous guy (before me) just gave them (company) access to phpMyAdmin as an 'admin panel', and it was easier to manage with that.

                    i just did a quick replace of items to mask the actual terribly embarrassing column names, but left it there to illustrate the use of a where clause.

                      Big.nerd: I am not sure what you are saying on that last post. I can hard code a name from the database in the WHERE clause and display an entry of that hard coded name. Of course the hard coding name is for testing only. What is the proper syntax to display all the names if I use the WHERE clause? Or, do I use the WHERE clause?
                      Information about my code:
                      Table name = plastic
                      Primary key = plastic_ID
                      column name = ename
                      column name = iname
                      This page should display how many names (as a number) are in the database, list the first 5 names and the iname information along with the PREV and NEXT buttons.
                      My code with your corrections tells how many names are in the database but does not display any names. The PREV and NEXT buttons don't do anything. I am still not sure if line nine has the proper wording of "page". I appreciate your help with this. Thank you.

                      <?php 
                      $db = odbc_connect('WEB_PROD_IDEAS','write','write123');  //put your connection here 
                      function inv_rows($r1)  { 
                      ob_start(); 
                      (int)$number=odbc_result_all($r1); 
                      ob_clean(); 
                      return $number; 
                      } 
                      $page = isset($_POST["page"]) ? $_POST["page"] : 1;  //not sure of "page" text 
                      if(empty($page)){$page = 1; }                
                      $query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.
                      $num_result = odbc_exec($db, $query); $numrows = inv_rows($num_result);
                      echo '<p>There are '.$numrows.' ideas.</p>'; $limit = 5; $limitvalue = $page * $limit - ($limit); $limitnew = $limitvalue + $limit; $max = 5; // Number Results/Page $start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item) $sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) "; $result = odbc_exec($db, $sql); while(odbc_fetch_row($result)){
                      ?> <table style="width: 600;">
                      <tr> <td style="width: 300; height: 25px;">Name:</td> <td style="width: 300; height: 25px;">Idea Name:</td> </tr>
                      <tr> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> </tr> <tr> <td colspan="5" style="height: 25px"><hr/></td> </tr> </table> <?php //PREVIOUS AND NEXT BUTTONS } if($page !=1){ $pageprev = $page - 1; echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; } else{ echo "&nbsp;PREV&nbsp;"; } $numofpages = $numrows / $limit; for($i = 1; $i <= $numofpages; ++$i){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } } if(($numrows &#37; $limit) != 0){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } } if(($numrows - ($limit * $page)) > 0){ $pagenext = $page + 1; echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } else{ echo "&nbsp;NEXT&nbsp;"; } odbc_free_result($result);
                      exit; ?>

                        lscruiser, that comment was for Roger Ramjet as he was asking about the = 'yes' clause I had in the posted example.

                        If you take a look at what Roger mentioned, he cleaned up my code to be a littlle easier to read, if you look @ your code below:

                        $start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item)
                        $sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";
                        

                        You have the same result set every time because you have a static $start set.

                        You are seeing results 6-10 if I am not mistaken, correct?

                        You are using get statements, instead of what Roger had shown you using POST, when you set a URL ?page=3 , etc you are sending it VIA get.

                        This is a potential security risk, but I will give you something that may work, I am pressed for time, so if I mess up, hopefully someone here will fix it!

                        if (!isset($_GET['page']) || empty($_GET['page']) {
                             $start = (int)$_GET['page'];
                             $start = ($start * $max);
                        } else {
                             $start = 0;
                        }
                        
                        //$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item)
                        $sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";
                        

                        That ought to do it, I hope anyways, I gotta run!

                          It is not clear on my part when I get partial code back from you to put it in the correct place. It might be helpful if the entire code with updates was returned. I am having trouble putting it all together when it is returned.
                          The code below has error: Undefined index: submit on line 21.
                          Thank you in advance for your help.

                          <?php 
                          $db = odbc_connect('','','');  //put your connection here 
                          function inv_rows($r1)  { 
                          ob_start(); 
                          (int)$number=odbc_result_all($r1); 
                          ob_clean(); 
                          return $number; 
                          } 
                          $page = isset($_POST["page"]) ? $_POST["page"] : 1;
                          if(empty($page)){$page = 1; }              
                          $query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.
                          $num_result = odbc_exec($db, $query); $numrows = inv_rows($num_result);
                          echo '<p>There are '.$numrows.' ideas.</p>'; $limit = 5; $limitvalue = $page * $limit - ($limit); $limitnew = $limitvalue + $limit; $max = 5; // Number Results/Page $start = 6; // This would be the where you start $perpage = 5; if ($_POST['submit']) {
                          $page = $_POST['page']; $top = ($page - 1) * $perpage; $sql = "SELECT TOP $perpage * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP $top plastic_ID FROM plastic ORDER BY plastic_ID) ORDER BY plastic_ID";
                          } $result = odbc_exec($db, $sql); while(odbc_fetch_row($result)){
                          ?> <table style="width: 600;">
                          <tr> <td style="width: 300; height: 25px;">Name:</td> <td style="width: 300; height: 25px;">Idea Name:</td> </tr>
                          <tr> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> </tr> <tr> <td colspan="5" style="height: 25px"><hr/></td> </tr> </table> <?php //PREVIOUS AND NEXT BUTTONS } if($page !=1){ $pageprev = $page - 1; echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; } else{ echo "&nbsp;PREV&nbsp;"; } $numofpages = $numrows / $limit; for($i = 1; $i <= $numofpages; ++$i){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } } if(($numrows % $limit) != 0){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } } if(($numrows - ($limit * $page)) > 0){ $pagenext = $page + 1; echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } else{ echo "&nbsp;NEXT&nbsp;"; } odbc_free_result($result);
                          exit; ?>

                            Try this out:

                            Note: as a learning point - you can see that I had the SQL query in there, if you replace the SQL query with the info I provided it will yield this result:

                            <?php
                            $db = odbc_connect('WEB_PROD_IDEAS','write','write123');  //put your connection here
                            function inv_rows($r1)  {
                            ob_start();
                            (int)$number=odbc_result_all($r1);
                            ob_clean();
                            return $number;
                            }
                            $page = isset($_POST["page"]) ? $_POST["page"] : 1;  //not sure of "page" text
                            if(empty($page)){$page = 1; }                
                            $query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.
                            $num_result = odbc_exec($db, $query); $numrows = inv_rows($num_result);
                            echo '<p>There are '.$numrows.' ideas.</p>'; $limit = 5; $limitvalue = $page * $limit - ($limit); $limitnew = $limitvalue + $limit; $max = 5; // Number Results/Page //$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item) f (!isset($_GET['page']) || empty($_GET['page']) { $start = (int)$_GET['page']; $start = ($start * $max); } else { $start = 0; } //$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item) $sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) "; $result = odbc_exec($db, $sql); while(odbc_fetch_row($result)){
                            ?> <table style="width: 600;">
                            <tr> <td style="width: 300; height: 25px;">Name:</td> <td style="width: 300; height: 25px;">Idea Name:</td> </tr>
                            <tr> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> </tr> <tr> <td colspan="5" style="height: 25px"><hr/></td> </tr> </table> <?php //PREVIOUS AND NEXT BUTTONS } if($page !=1){ $pageprev = $page - 1; echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; } else{ echo "&nbsp;PREV&nbsp;"; } $numofpages = $numrows / $limit; for($i = 1; $i <= $numofpages; ++$i){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } } if(($numrows &#37; $limit) != 0){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } } if(($numrows - ($limit * $page)) > 0){ $pagenext = $page + 1; echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } else{ echo "&nbsp;NEXT&nbsp;"; } odbc_free_result($result);
                            exit; ?>

                            I know I can be a pain with stuff like this, so feel free to be a pain to me when I ask for something you can help with... 🙂

                            It does help you learn though...

                              If you are using MSSQL 2005 or higher you can make use of Common Table Expressions for a (slightly) more intuitive method to paginate recordsets. Who needs nice and simple LIMIT eh!

                                MSSQL 2008 wont support limit . Better use ROW_NUMBER () for pagination

                                EX:

                                SELECT * FROM ( SELECT ROW_NUMBER() OVERY (ORDER BY COLUMN_NAME) AS Rn , Fname , Middlename,Lname from Persons ) WHERE Rn > Var1 and Rn <var2 ( or use BETWEEN instead of AND )

                                pass var1 start limit and var as end limit

                                  big.nerd or anyone else that can help:
                                  Thank you for your response. I have three questions, not necessarily in the correct order.
                                  1. On line 9 below I am not sure I am calling the field name of "page" the right thing.
                                  2. On line 20 below I receive the error "Notice: Undefined index: page." - with the code that is below. I need to know how to fix this error.
                                  3. On line 25 I need to rename the value of "yes" to where it displays all the results of the column ename.
                                  Details: My table name is "plastic" with primary key of "plastic_ID" and columns of "ename" and "iname". I want my page to display the number of names in the database as a number. Example: There are "XX" names. This part of the code below works. Then I want to display 5 names per page with a PREV and NEXT button to move the user through the results. My connection is ODBC (not my choice) with MSSQL(not my choice) and PHP.
                                  Thank you for your help. Returning the complete updated code helps me understand your changes.

                                  <?php
                                  $db = odbc_connect('','','');  //put your connection here
                                  function inv_rows($r1)  {
                                  ob_start();
                                  (int)$number=odbc_result_all($r1);
                                  ob_clean();
                                  return $number;
                                  }
                                  $page = isset($_POST["page"]) ? $_POST["page"] : 1;//question about this line 9
                                  if(empty($page)){$page = 1; }                
                                  $query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.
                                  $num_result = odbc_exec($db, $query); $numrows = inv_rows($num_result);
                                  echo '<p>There are '.$numrows.' ideas.</p>'; $limit = 5; $limitvalue = $page * $limit - ($limit); $limitnew = $limitvalue + $limit; $max = 5; if (!isset($_GET['page']) || empty($_GET['page'])) { $start = (int)$_GET['page']; //question about this line 20 - Notice: Undefined index: page $start = ($start * $max); } else { $start = 0; } $sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";//question about this line 25 $result = odbc_exec($db, $sql); while(odbc_fetch_row($result)){
                                  ?> <table style="width: 600;">
                                  <tr> <td style="width: 300; height: 25px;">Name:</td> <td style="width: 300; height: 25px;">Idea Name:</td> </tr>
                                  <tr> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> </tr> <tr> <td colspan="5" style="height: 25px"><hr/></td> </tr> </table> <?php //PREVIOUS AND NEXT BUTTONS } if($page !=1){ $pageprev = $page - 1; echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; } else{ echo "&nbsp;PREV&nbsp;"; } $numofpages = $numrows / $limit; for($i = 1; $i <= $numofpages; ++$i){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } } if(($numrows % $limit) != 0){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } } if(($numrows - ($limit * $page)) > 0){ $pagenext = $page + 1; echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } else{ echo "&nbsp;NEXT&nbsp;"; } odbc_free_result($result);
                                  exit; ?>
                                    1. The field name page comes from the section where you say:
                                    if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
                                    else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
                                    }
                                    if(($numrows % $limit) != 0){
                                    if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
                                    else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
                                    } 
                                    

                                    Do you see how you have <a href='?page= $i ?

                                    That is where $_GET['page'] comes from, you are passing the GET variable (when passing it in the URL it is a GET request.

                                    1. Its a notice that $_GET['page'] doesnt exist, at the top of your code, RIGHT BELOW <?php add the following
                                    error_reporting(E_ALL ^ E_NOTICE);
                                    

                                    This will suppress notice's

                                    With question #3,

                                    lscruiser wrote:

                                    3. On line 25 I need to rename the value of "yes" to where it displays all the results of the column ename.

                                    I am not sure, what is the value ename? That is a column name, you may not need that at all.

                                    You can try removing that, you are essentially telling the SQL to give you data where the value of the 'ename' column is yes, when I provided the example on how to do this, I had taken this from a portion of my code that needed it, if you don't need it, remove it.

                                    It seems as if you are trying to pull a POST of the variable PAGE but i don't see you using POST anywhere.

                                    I had made some recommendations in the code below and made a couple changes, I hope this helps..

                                    <?php
                                    error_reporting(E_ALL ^ E_NOTICE); // supress notice's
                                    $db = odbc_connect('','','');  //put your connection here
                                    function inv_rows($r1)  {
                                    ob_start();
                                    (int)$number=odbc_result_all($r1);
                                    ob_clean();
                                    return $number;
                                    }
                                    
                                    // Setting the $max as 5, as in 5 results PER PAGE.
                                    $max = 5; // using this instead of limit
                                    
                                    // if you havn't specified page (as in they clicked on next page)  set $start as 0 (i think, may need to be 1)
                                    // If they have specified it, get the 'page' from the URL (?page=#) and be sure its a number. (thats what (int) does)
                                    // Then set the $page (page number) and the start, is the number of pages * limit, i.e. page 1 is 5, 2 is 10, etc.
                                    if (!isset($_GET['page']) || empty($_GET['page'])) {
                                         $page = (int)$_GET['page']; //question about this line 20 - Notice: Undefined index: page
                                         $start = ($page * $max);
                                    } else {
                                         $start = 0;
                                    }
                                    $query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.                
                                    $num_result = odbc_exec($db, $query); $numrows = inv_rows($num_result);
                                    echo '<p>There are '.$numrows.' ideas.</p>'; // I am assuming this works // Example: Page 2, start will be 10 // Example SQL Query: Select top 5 * FROM plastic WHERE plastic_id is not in the top 10 items, thus you get 11-15... $sql = "SELECT TOP $max * FROM plastic WHERE (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";//question about this line 25 $result = odbc_exec($db, $sql); while(odbc_fetch_row($result)){
                                    ?> <table style="width: 600;">
                                    <tr> <td style="width: 300; height: 25px;">Name:</td> <td style="width: 300; height: 25px;">Idea Name:</td> </tr>
                                    <tr> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> </tr> <tr> <td colspan="5" style="height: 25px"><hr/></td> </tr> </table> <?php //PREVIOUS AND NEXT BUTTONS } if($page !=1){ $pageprev = $page - 1; echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; } else{ echo "&nbsp;PREV&nbsp;"; } $numofpages = $numrows / $max; for($i = 1; $i <= $numofpages; ++$i){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } } if(($numrows % $max) != 0){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } } if(($numrows - ($max * $page)) > 0){ $pagenext = $page + 1; echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } else{ echo "&nbsp;NEXT&nbsp;"; } odbc_free_result($result);
                                    exit; ?>

                                      I have not been able to write a correct SELECT statement for the page information to change.
                                      I found a similar problem: http://www.phpbuilder.com/board/showthread.php?t=10352935 for reference.
                                      The name of my table is plastic. My primary key is plastic_ID. ename represents names and iname represents ideas. I want to show 5 names and ideas per page with pagination. Below are three different SELECT statements that produce the same results if you click on PREV or NEXT buttons. These buttons change when clicked, but the results stay the same.
                                      I use an ODBC connection with MSSQL and PHP.
                                      Can you help me write a correct SELECT statement? Returning the complete code helps me understand your edits. I thank you in advance for your help.

                                      <?php
                                      $db = odbc_connect('','','');  //put your connection here 
                                      function inv_rows($r1)  { 
                                      ob_start(); 
                                      (int)$number=odbc_result_all($r1); 
                                      ob_clean(); 
                                      return $number; 
                                      } 
                                      $page = isset($_GET["page"]) ? $_GET["page"] : 1;
                                      if(empty($page)){$page = 1; }                
                                      $query = "SELECT * FROM plastic"; // name of table is plastic with primary key of plastic_ID and columns ename and iname.
                                      $num_result = odbc_exec($db, $query); $numrows = inv_rows($num_result);
                                      echo '<p>There are '.$numrows.' ideas.</p>'; $limit = 5; $limitvalue = $page * $limit - ($limit); $limitnew = $limitvalue + $limit; //all three SELECT statements below produce a PREV and NEXT action that appears to work BUT the results are the same when clicking to a PREV or NEXT page $sql = "SELECT * FROM (SELECT TOP 5 * FROM (SELECT TOP 5 * FROM plastic ORDER BY ename, iname DESC) as table1 ORDER BY ename, iname DESC) as table2 ORDER BY ename, iname ASC"; //$sql = "SELECT * FROM (SELECT TOP 5 * FROM (SELECT TOP $numrows * FROM plastic ORDER BY ename, iname ASC) as table1 ORDER BY ename, iname DESC) as table2 ORDER BY ename, iname ASC"; //$sql = "SELECT * FROM (SELECT TOP 5 * FROM plastic ORDER BY ename, iname ASC) AS t1 ORDER BY ename, iname DESC"; $result = odbc_exec($db, $sql); while(odbc_fetch_row($result)){
                                      ?> <table style="width: 600;">
                                      <tr> <td style="width: 300; height: 25px;">Name:</td> <td style="width: 300; height: 25px;">Idea Name:</td> </tr>
                                      <tr> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> <td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> </tr> <tr> <td colspan="5" style="height: 25px"><hr/></td> </tr> </table> <?php //PREVIOUS AND NEXT BUTTONS } if($page !=1){ $pageprev = $page - 1; echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; } else{ echo "&nbsp;PREV&nbsp;"; } $numofpages = $numrows / $limit; for($i = 1; $i <= $numofpages; ++$i){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } } if(($numrows % $limit) != 0){ if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } } if(($numrows - ($limit * $page)) > 0){ $pagenext = $page + 1; echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } else{ echo "&nbsp;NEXT&nbsp;"; } odbc_free_result($result);
                                      exit; ?>

                                        My dear fellow you really have not understood this have you.

                                        The purpose of using the subquery is to elliminate the records that you do not want to display because they would be displayed on a previous page. So page 5 should show records 21-25 and in order to achive this we have to elliminate the first 20. That is what the subquery is used for. The query

                                        SELECT TOP 20 plastic_ID  FROM plastic ORDER BY plastic_ID ASC
                                        

                                        Will return the 20 records we DO NOT WANT.
                                        When we use this as a subquery, we want to NOT select these records so we use NOT IN

                                        SELECT TOP 5 * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP 20 plastic_ID  FROM plastic ORDER BY plastic_ID ASC) ORDER BY plastic_id ASC
                                        

                                        The first 20 are elliminated and we then get the next 5