SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 61

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Paging using PHP & MSSQL

    As above I'm using PHP and MSSQL, and looking to be able to organise the contracts we have in a database by limiting them to 50 or so on a page.

    Ive looked on the web, and I cant quite get my head around the code.

    This is what I have got at the mo, and would love if somebody could help me out to be able to page all the contracts to 50 a page.

    PHP Code:
    <?php
    if ($search==""){
      
    $query "select * from Intranet order by ID DESC";
    } else {
      
    $query "select * from Intranet $search";


    $stmt sqlsrv_query($conn$query);
    while(
    $data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC))        
    {
    echo 
    "<tr>";
    echo 
    "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&Admin=Edit'><img src='images/icon_edit.gif' alt='Edit Item' height='13'></a></td>";
    echo 
    "<td width='25' bgcolor='#ffffff' class='tableData'><a href='read_contract.php?ID=".$data["ID"]."'><img src='images/icon_view.gif' alt='View Item &amp; Print'></td>";
    echo 
    "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&AdminDelete'><img src='images/delete_Icon.jpg' alt='Delete Item'></a></td>";
    echo 
    "<td width='70' class='tableData'>".$data["Contract_Number"]."</td>";
    echo 
    "<td width='70' class='tableData'>".$data["Contract_Status"]."</td>";
    echo 
    "<td width='55' class='tableData'>".$data["Annual_Contract_Value"]."</td>";
    echo 
    "<td width='80' class='tableData'>Contract Start</td>";
    echo 
    "<td width='80' class='tableData'>Contract Current</td>";
    echo 
    "<td width='60' class='tableData'>".$data["Contract_Length"]."</td>";
    echo 
    "<td width='80' class='tableData'>Renewal Date</td>";
    echo 
    "<td width='110' class='tableData'>".$data["Site_Name"]."</td>";
    echo 
    "<td width='80' class='tableData'>".$data["Street"]."</td>";
    echo 
    "<td width='80' class='tableData'>".$data["City"]."</td>";
    echo 
    "<td width='100' class='tableData'>".$data["State"]."</td>";
    echo 
    "<td width='100' class='tableData'>".$data["Country"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["Region"]."</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Group_Member"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='100' class='tableData'>".$data["Group_Name"]."</td>";
    echo 
    "<td width='45' class='tableData'>".$data["No_of_Rooms"]."</td>";
    echo 
    "<td width='90' class='tableData'>".$data["Type_of_Establishment"]."</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["CSF"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Food_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Swimming_Pool_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Legionella_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Fire_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Hurricane"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Green"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Guest"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Room_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Quality_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Supply_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Tourcheck"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>".$data["No_of_Modules"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["Currency_of_Invoice"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["No_of_audits_per_annum"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["No_of_visits_per_annum"]."</td>";
    echo 
    "<td width='45' class='tableData'>".$data["Seasonal_or_Full"]."</td>";
    echo 
    "<td width='65' class='tableData'>".$data["Month_Opens"]."</td>";
    echo 
    "<td width='65' class='tableData'>".$data["Month_Closes"]."</td>";
    echo 
    "<td width='65' class='tableData'>".$data["Invoicing_Profile"]."</td>";
    echo 
    "</tr>";
    }                
    ?>

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    You need to first return the number of items stored in the table (hint, look up the COUNT() function)

    Then you need to limit the results to the specific page you want to display (hint, look up the LIMIT clause)

    Then you can output the page numbers by using ceil(TOTAL NUMBER OF RECORDS / 50)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by multichild View Post
    Ive looked on the web, and I cant quite get my head around the code.
    there are many fine articles out there on how to do it, and i don't think an impromptu reply on a forum is going to be of higher quality, so you should really pick one of those articles and give it a serious try

    Quote Originally Posted by cpradio View Post
    Then you need to limit the results to the specific page you want to display (hint, look up the LIMIT clause)
    unfortunately, LIMIT is a proprietary sql feature that exists only in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    unfortunately, LIMIT is a proprietary sql feature that exists only in mysql
    Ah, I knew that. Okay, so instead of limit, you need to research ROW_NUMBER in combination with OVER / PARTITION BY.

  5. #5
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, I will see what I can find and take it from there.

    It not an easy one this is it?

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    To do it right, no. It is not easy, but once you do it, you won't forget how to do it again

  7. #7
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I have made some in roads here as I did find a tutorial after but it was for php and MySQL, and I need it in MSSQL, but one person replied in a thread on that site and added some changes to accomodate MSSQL, but in honesty Im not sure where he means to put or make the change.

    Her is his change for MSSQL:

    PHP Code:
    // CREATE A LIMIT QUERY FOR MS SQL DATABASES
    $query "WITH LIMIT AS(
    SELECT FIELD1, FIELD2,
    ROW_NUMBER() OVER (ORDER BY FIELD1) AS 'RowNumber'
    FROM some_table)
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit";
    $result mssql_query($query);
    // while there are rows to be fetched...
    while ($list mssql_fetch_array($result)) {
    echo 
    $list['field1'] . " : " $list['field2'] . "<br />";

    And here is my code again with the changes.

    PHP Code:
    <?php
    // find out how many rows are in the table 
    $sql "SELECT COUNT(*) FROM Intranet";
    $result sqlsrv_query($conn$sql);
    $r sqlsrv_fetch_array($result);
    $numrows $r[0];
    //echo $result;

    // number of rows to show per page
    $rowsperpage 1;// find out total pages
    $totalpages ceil($numrows $rowsperpage);

    // get the current page or set a default
    if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {   
    // cast var as int   
    $currentpage = (int) $_GET['currentpage'];
    } else {   
    // default page num   
    $currentpage 1;
    // end if

    // if current page is greater than total pages...
    if ($currentpage $totalpages) {   
    // set current page to last page   
    $currentpage $totalpages;
    // end if
    // if current page is less than first page...
    if ($currentpage 1) {   
    // set current page to first page   
    $currentpage 1;
    // end if

    // the offset of the list, based on current page 
    $offset = ($currentpage 1) * $rowsperpage;
    $limit=$offset $rowsperpage;

    if (
    $search==""){
      
    $query "select * from Intranet order by ID DESC";
    } else {
      
    $query "select * from Intranet $search";


    $stmt sqlsrv_query($conn$query);
    //echo $query;    
    while($data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC))          
    {
    //echo $data['ID'];
    /******  build the pagination links ******/// range of num links to show
    $range 3;

    // if not on page 1, don't show back links
    if ($currentpage 1) {   
    // show << link to go back to page 1   
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";   
    // get previous page num   
    $prevpage $currentpage 1;   
    // show < link to go back to 1 page   
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
    // end if

    // loop to show links to range of pages around current page
    for ($x = ($currentpage $range); $x < (($currentpage $range) + 1); $x++) {   
    // if it's a valid page number...   
    if (($x 0) && ($x <= $totalpages)) {      
    // if we're on current page...      
    if ($x == $currentpage) {         
    // 'highlight' it but don't make a link         
    echo " [<b>$x</b>] ";      
    // if not current page...      
    } else {         
    // make it a link         
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";      
    // end else   
    // end if 
    // end for

    // if not on last page, show forward and last page links        
    if ($currentpage != $totalpages) {   
    // get next page   
    $nextpage $currentpage 1;    
    // echo forward link for next page    
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";   
    // echo forward link for lastpage   
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
    // end if
    /****** end build pagination links ******/

    echo "<tr>";
    echo 
    "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&Admin=Edit'><img src='images/icon_edit.gif' alt='Edit Item' height='13'></a></td>";
    echo 
    "<td width='25' bgcolor='#ffffff' class='tableData'><a href='read_contract.php?ID=".$data["ID"]."'><img src='images/icon_view.gif' alt='View Item &amp; Print'></td>";
    echo 
    "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&AdminDelete'><img src='images/delete_Icon.jpg' alt='Delete Item'></a></td>";
    echo 
    "<td width='70' class='tableData'>".$data["Contract_Number"]."</td>";
    echo 
    "<td width='70' class='tableData'>".$data["Contract_Status"]."</td>";
    echo 
    "<td width='55' class='tableData'>".$data["Annual_Contract_Value"]."</td>";
    echo 
    "<td width='80' class='tableData'>Contract Start</td>";
    echo 
    "<td width='80' class='tableData'>Contract Current</td>";
    echo 
    "<td width='60' class='tableData'>".$data["Contract_Length"]."</td>";
    echo 
    "<td width='80' class='tableData'>Renewal Date</td>";
    echo 
    "<td width='110' class='tableData'>".$data["Site_Name"]."</td>";
    echo 
    "<td width='80' class='tableData'>".$data["Street"]."</td>";
    echo 
    "<td width='80' class='tableData'>".$data["City"]."</td>";
    echo 
    "<td width='100' class='tableData'>".$data["State"]."</td>";
    echo 
    "<td width='100' class='tableData'>".$data["Country"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["Region"]."</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Group_Member"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='100' class='tableData'>".$data["Group_Name"]."</td>";
    echo 
    "<td width='45' class='tableData'>".$data["No_of_Rooms"]."</td>";
    echo 
    "<td width='90' class='tableData'>".$data["Type_of_Establishment"]."</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["CSF"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Food_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Swimming_Pool_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Legionella_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Fire_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Hurricane"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Green"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Guest"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Room_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Quality_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Supply_Safety"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>";
    if (
    $data["Tourcheck"]==1){
      echo 
    "Yes";
    } else {
      echo 
    "No";
    }
    echo 
    "</td>";
    echo 
    "<td width='60' class='tableData'>".$data["No_of_Modules"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["Currency_of_Invoice"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["No_of_audits_per_annum"]."</td>";
    echo 
    "<td width='60' class='tableData'>".$data["No_of_visits_per_annum"]."</td>";
    echo 
    "<td width='45' class='tableData'>".$data["Seasonal_or_Full"]."</td>";
    echo 
    "<td width='65' class='tableData'>".$data["Month_Opens"]."</td>";
    echo 
    "<td width='65' class='tableData'>".$data["Month_Closes"]."</td>";
    echo 
    "<td width='65' class='tableData'>".$data["Invoicing_Profile"]."</td>";
    echo 
    "</tr>";
    }                
    ?>
    Like I said it seems to work as I have only 4 contracts in the database at the moment, so I cut the number of contracts to display per page to 1.

    What I get then is the actual pageination bit showing up, it not directly controlling the content but also the pageination bit is duplicating itself in accordance with the same number of contracts.

    I can PM people my details if you would like to see it on the site.

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    You will want to move your pagination logic outside of your while loop. It should be either before or after your while loop (depending on if you want it above or below your records)

    From what I can tell, your queries are spot on, so good job.

  9. #9
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah yes, that solved the issue of the pageination creating itself the same number of times as the number of contracts, which is great.

    The pageination is working as such, but the contracts arent only showing the number I set it to which was 1, they are all outputting, so the pageination has no effect on it.

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Can you echo your query right before calling
    Code:
    $result = mssql_query($query);
    So, something like:
    Code:
    echo $query;
    $result = mssql_query($query);
    Then paste the query as it is written here for us to see.

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Also, I see in your code file (second set of code in your post), you still have
    Code:
    if ($search==""){ 
      $query = "select * from Intranet order by ID DESC"; 
    } else { 
      $query = "select * from Intranet $search"; 
    }
    Instead of using your CTE named LIMIT, which could be part of the problem.

  12. #12
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm, I'm sorry Im not sure what you mean by CTE. Do you mean I need to add this bit that was posted in the other thread.

    // CREATE A LIMIT QUERY FOR MS SQL DATABASES
    $query = "WITH LIMIT AS(
    SELECT FIELD1, FIELD2,
    ROW_NUMBER() OVER (ORDER BY FIELD1) AS 'RowNumber'
    FROM some_table)
    select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit";
    $result = mssql_query($query);
    // while there are rows to be fetched...
    while ($list = mssql_fetch_array($result)) {
    echo $list['field1'] . " : " . $list['field2'] . "<br />";
    }

    Because if so, in honesty it confused me.

  13. #13
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me show you first where I put just to be sure its in the right place.

    PHP Code:
    if ($search==""){
      
    $query "select * from Intranet order by ID DESC";
    } else {
      
    $query "select * from Intranet $search";

    echo 
    $query;
    $result mssql_query($query); 
    This is the error that came up

    select * from Intranet order by ID DESC Fatal error: Call to undefined function mssql_query() in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 221

    It also stopped all the contracts from showing

  14. #14
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I just use:

    echo $query;

    I see this as the echo:

    select * from Intranet order by ID DESC

    and the rest of the page with the contracts display too, there no errors

  15. #15
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, remove the mssql_query() reference, as that I just took from one of your other code snippets, but it isn't necessary in your entire file snippet.

    PHP Code:
    // the offset of the list, based on current page  
    $offset = ($currentpage 1) * $rowsperpage
    $limit=$offset $rowsperpage

    if (
    $search==""){ 
      
    $query "select * from Intranet order by ID DESC"
    } else { 
      
    $query "select * from Intranet $search"
    }  

    $stmt sqlsrv_query($conn$query); 
    You have the above, but that isn't using the LIMIT CTE that you created.

    So you need to switch it to:
    PHP Code:
    // the offset of the list, based on current page  
    $offset = ($currentpage 1) * $rowsperpage
    $limit=$offset $rowsperpage

    if (
    $search==""){ 
      
    $query "WITH LIMIT AS( 
    SELECT *, 
    ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' 
    FROM Intranet) 
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit order by ID DESC"
    } else { 
      
    $query "WITH LIMIT AS( 
    SELECT *, 
    ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' 
    FROM Intranet) 
    select * from LIMIT WHERE 
    $search AND RowNumber BETWEEN $offset AND $limit order by ID DESC"
    }  

    $stmt sqlsrv_query($conn$query); 
    Can you show me where $search is created/defined?

  16. #16
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes brilliant that worked, but its now doing something strange.

    On the first page only 1 shows which is right, but on the second page the first one shows and the second one, and then on the third page the third one shows and the second one and so on.

    Its showing the correct one on each page, plus the one before it.

  17. #17
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Please change
    PHP Code:
    $stmt sqlsrv_query($conn$query); 
    To
    PHP Code:
    echo $query;
    $stmt sqlsrv_query($conn$query); 
    And give me the statement that appears on each page.

  18. #18
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Page 1:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 0 AND 1 order by ID DESC [1] 2 3 4 > >>

    Page 2:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 order by ID DESC << < 1 [2] 3 4 > >>

    Page 3:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 2 AND 3 order by ID DESC << < 1 2 [3] 4 > >>

    Page 4:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC << < 1 2 3 [4]

  19. #19
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Change
    PHP Code:
    $offset = ($currentpage 1) * $rowsperpage
    To
    PHP Code:
    $offset = ($currentpage 1) * $rowsperpage 1

  20. #20
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That unfortunately didnt fix it, what it did by the seems was add one to the front page so it showed 2 instead of 1 and took it away from the last page where it showed 1 only.

    Is it dooable so that if you set it to say display 25 it only displays that number on each page, and each time they are fresh results

  21. #21
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Can you put the echo statement back in and give me the query output on each page again, as I thought it should have changed the queries to BETWEEN 1 AND 1, 2 AND 2, 3 AND 3, 4 AND 4, for each page respectively.

  22. #22
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I had to leave the office pretty much straight after my last post, so would you mind if I reply tomorrow.

    Thank you very much for all the help its been great, and to get somewhere too quickly is a bonus.

    Cheers

  23. #23
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Not a problem. Just post the results when you get a chance and we'll keep working on a complete solution

  24. #24
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Morning cpradio,

    OK I put the echo back in and these are the errors.

    Page 1:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 order by ID DESC [1] 2 3 4 > >>

    Page 2:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 2 AND 3 order by ID DESC << < 1 [2] 3 4 > >>

    Page 3:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC << < 1 2 [3] 4 > >>

    Page 4:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 4 AND 5 order by ID DESC << < 1 2 3 [4]

    Thank again for the support

  25. #25
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, change
    PHP Code:
    $limit=$offset $rowsperpage
    to
    PHP Code:
    $limit=($offset 1) + $rowsperpage
    Summary of the recent changes:
    This change was necessary because ROW_NUMBER starts at 1, not 0. So without the +1, you will get an additional record on pages 2+
    PHP Code:
    $offset = ($currentpage 1) * $rowsperpage 1
    The following code is necessary to ensure the limit is met, since we added an additional 1 to the offset, we need to subtract it from the limit so instead of 2, it will remain 1 (or instead of 26, it will be 25; if rowsperpage was 25)
    PHP Code:
    $limit=($offset 1) + $rowsperpage


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
  •