SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 61
  1. #26
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks cpradio that works perfectly now.

    Thank you very much, you have really helped a lot, and makes me wonder how long people like you have been at this php game, as although I'm trying my best it still doesnt match this sort of knowledge.

    But thank you again.

  2. #27
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi cpradio,

    Me again, I'm following up from the other thread with the wildcard issue, which worked perfectly well, but it didnt work perfectly with the pageing.

    Basically it does the right thing by only displaying the contracts but the pageing doesnt react accordingly.

    Whats happening is that although the contracts on show have been reduced due to the wildcard fix, the pageing doesnt react to the number of contracts on show, it still shows the number of contracts in the database.

    The code below is what changed with the wildcard:

    PHP Code:
      $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%') 
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit order by ID DESC"

  3. #28
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    You need to update the query that has COUNT() in it
    PHP Code:
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"// Added WHERE clause
    $result sqlsrv_query($conn$sql); 
    $r sqlsrv_fetch_array($result); 
    $numrows $r[0]; 

  4. #29
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOl fair play cpradio!!!

    I do have one more thing, and will post it after this, as Im yet to work out how to post and I will also have a go at it myself.

    Thanks again

  5. #30
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you cpradio I got it sorted myself...

    Thanks to your use of the echo feedback.

    Thanks again for everything

  6. #31
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Not problem, if you ever need additional help, don't hesitate to ask here on Sitepoint

  7. #32
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As it goes I got a small issue which I cant seem to work out.

    Basically to allow the user to show the contracts in different ways, I created a drop down and made it liek this ,although you will probably think its a bit old fashioned, it worked so I stuck with it.

    But in some cases it works and in others it doesnt.

    Here is the code:
    PHP Code:
    $search=$_POST['Search'];

    <form name="form1" action="contracts.php" method="post" enctype="multipart/form-data">
    <td height="27" style="position:relative;left:16px;"><span style="font-family:Verdana, Arial, Helvetica, sans-serif; color:#333333; font-size:13px;position:relative;">View By:</span> 
    <select name="Search">
    <option value="">Select Status</option> 
    <!--<option <?php if($cStatus == "Active") echo "selected"?> value="Active">Active</option>--> 
    <option value="Order by ID ASC">All Contracts (Old-New)</option>
    <option value="Order by ID DESC">All Contracts (New-Old)</option>
    <option value="WHERE (Contract_Status='On Hold') Order by Contract_Number">All Contracts (On Hold)</option>
    <option value="WHERE (Contract_Status='Active') Order by Contract_Number">All Contracts (Active)</option>
    <option value="WHERE (Contract_Status='Terminated') Order by Contract_Number">All Contracts (Terminated)</option>  
    <option value="Order by Country ASC">By Country (A-Z)</option> 
    <option value="Order by Region ASC">By Region (A-Z)</option> 
    <option value="Order by No_of_Modules ASC">By Number of Modules (0-10)</option>
    <option value="Order by Annual_Contract_Value">By Contract Value (Low-High)</option>
    <option value="Order by Annual_Contract_Value DESC">By Contract Value (High-Low)</option>
    <option value="WHERE (Food_Safety='1') order by Contract_Number ASC">By FoodCheck (Yes)</option>
    <option value="WHERE (Swimming_Pool_Safety='1') order by Contract_Number ASC">By PoolCheck (Yes)</option>
    <option value="WHERE (Legionella_Safety='1') order by Contract_Number ASC">By AquaCheck (Yes)</option>
    <option value="WHERE (Fire_Safety='1') order by Contract_Number ASC">By FireCheck (Yes)</option>
    <option value="WHERE (Hurricane='1') order by Contract_Number ASC">By CrisisCheck (Yes)</option>
    <option value="WHERE (Green='1') order by Contract_Number ASC">By EcoCheck (Yes)</option>
    <option value="WHERE (Guest='1') order by Contract_Number ASC">By SafetyCheck (Yes)</option>
    <option value="WHERE (Room_Safety='1') order by Contract_Number ASC">By RoomCheck (Yes)</option>
    <option value="WHERE (Quality_Safety='1') order by Contract_Number ASC">By QualityCheck (Yes)</option>
    <option value="WHERE (Supply_Safety='1') order by Contract_Number ASC">By SupplyCheck (Yes)</option>
    <option value="WHERE (Tourcheck='1') order by Contract_Number ASC">By Tourcheck (Yes)</option>
    </select>
    <input type="submit" name="btnsubmit" value="Submit" class="btn-search">
    </td>
    </form>
    Then in the code below I added $search to finish off the select function as below:

    PHP Code:
      $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%') 
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit $search"
    All is fine when $search does star with a WHERE, but works without it. Is there something wrong with the way my code is working.

    Here is the error when I ask it to display using the following:

    <option value="WHERE (Contract_Status='Active') Order by Contract_Number">All Contracts (Active)</option>

    echo = WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 WHERE (Contract_Status='Active') Order by Contract_Number [1] 2 > >> Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 265

  8. #33
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Okay, you should really re-work this 100%. First you have opened yourself to a SQL Injection attack using this approach (not good).

    So I would instead assign each <option> a value of something along these lines: AllContractsOldNew, AllContractsNewOld, AllContractsOnHold, etc.

    Then use a switch statement to build your query, or an array (examples shown below)
    Switch Statement:
    PHP Code:
    switch($search)
    {
        case 
    "AllContractsOldNew":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by ID ASC";  
            break;
         ...
        case 
    "AllContractsOnHold":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Contract_Status='On Hold') Order by Contract_Number";  
            break;
          ...

    Example of Array:
    PHP Code:
    $searchQueries = array();
    $searchQueries['AllContractsOldNew'] = "Order by ID ASC";
    ...
    $searchQueries['AllContractsOnHold'] = "AND (Contract_Status='On Hold') Order by Contract_Number";
    ...

    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit " $searchQueries[$search]; 
    This way you are in control of the final SQL statement and not the user.

  9. #34
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK thank you cpradio, I didnt know about the SQL injection. I will do as you say, and having looked at your answer I think the first one site easiest with me.

    Will make the change now.

    Cheers

  10. #35
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Okay, and if you run into problems, post your updated code so I can help you resolve it.

  11. #36
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi cpradio,

    The new way of doing the order of the contracts by the drop downs worked perfectly.

    But as usual I noticed a bug.

    What seems to be happening is that again its displaying more contracts than it should, and after page 1 the ordering doesnt carry through.

    I'e posted the code below, but I'm pretty sure your going to say its the this bit below, but cant see how to work this in.

    PHP Code:
    // find out how many rows are in the table 
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
    $result sqlsrv_query($conn$sql);
    $r sqlsrv_fetch_array($result);
    $numrows $r[0]; 
    All the code together is below

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

    // number of rows to show per page
    $rowsperpage 2;// 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 1;
    $limit=($offset 1) + $rowsperpage

    if (
    $search==""){ 
      
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%') 
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit order by ID DESC"
    } else { 
      switch(
    $search)
    {
        case 
    "AllContractsOld-New":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by ID ASC";  
            break;
        case 
    "AllContractsNew-Old":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by ID DESC";  
            break;
        case 
    "AllContractsOnHold":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Contract_Status='On Hold') Order by Contract_Number";  
            break;
        case 
    "AllContractsActive":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Contract_Status='Active') Order by Contract_Number";  
            break;
        case 
    "AllContractsTerminated":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Contract_Status='Terminated') Order by Contract_Number";  
            break;
        case 
    "ByCountryA-Z":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Country ASC";  
            break;
        case 
    "ByCountryZ-A":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Country DESC";  
            break;
        case 
    "ByRegionA-Z":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Region ASC";  
            break;
        case 
    "ByRegionZ-A":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Region DESC";  
            break;
        case 
    "ByNumberModulesO-X":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by No_of_Modules ASC";  
            break;
        case 
    "ByNumberModulesX-O":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by No_of_Modules DESC";  
            break;
        case 
    "ByContractValueLow-High":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Annual_Contract_Value ASC";  
            break;
        case 
    "ByContractValueHigh-Low":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Annual_Contract_Value DESC";  
            break;
        case 
    "ByFoodCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Food_Safety='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByPoolCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByAquaCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Legionella_Safety='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByFireCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Fire_Safety='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByCrisisCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Hurricane='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByEcoCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Green='1') Order by Contract_Number ASC";  
            break;
        case 
    "BySafetyCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Guest='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByRoomCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Room_Safety='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByQualityCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Quality_Safety='1') Order by Contract_Number ASC";  
            break;
        case 
    "BySupplyCheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Supply_Safety='1') Order by Contract_Number ASC";  
            break;
        case 
    "ByTourcheckYes":
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Tourcheck='1') Order by Contract_Number ASC";  
            break;
        }  
    }  
    //echo $query;
    $stmt sqlsrv_query($conn$query);  

    /******  build the pagination links ******/// range of num links to show
    $range 30;
    echo 
    "<span style=font-family:Arial;font-size:14px;color:#333333;margin-top:5px;margin-bottom:5px;height:20px;margin-left:2px;>";
    // 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 "</span>";
    while(
    $data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC))          
    {

  12. #37
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Back to we need to update the COUNT() query. I've moved some of your code around to make it easier to set both queries in a single switch statement.

    PHP Code:
    <?php 
    // find out how many rows are in the table  

    switch($search

        case 
    "AllContractsOld-New":                 
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by ID ASC";   
            break; 
        case 
    "AllContractsNew-Old"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by ID DESC";   
            break; 
        case 
    "AllContractsOnHold"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold')"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Contract_Status='On Hold') Order by Contract_Number";   
            break; 
        case 
    "AllContractsActive"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active')"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Contract_Status='Active') Order by Contract_Number";   
            break; 
        case 
    "AllContractsTerminated"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated')"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Contract_Status='Terminated') Order by Contract_Number";   
            break; 
        case 
    "ByCountryA-Z"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Country ASC";   
            break; 
        case 
    "ByCountryZ-A"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Country DESC";   
            break; 
        case 
    "ByRegionA-Z"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Region ASC";   
            break; 
        case 
    "ByRegionZ-A"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Region DESC";   
            break; 
        case 
    "ByNumberModulesO-X"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by No_of_Modules ASC";   
            break; 
        case 
    "ByNumberModulesX-O"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by No_of_Modules DESC";   
            break; 
        case 
    "ByContractValueLow-High"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Annual_Contract_Value ASC";   
            break; 
        case 
    "ByContractValueHigh-Low"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit Order by Annual_Contract_Value DESC";   
            break; 
        case 
    "ByFoodCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Food_Safety='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByPoolCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByAquaCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Legionella_Safety='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByFireCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Fire_Safety='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByCrisisCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Hurricane='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByEcoCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Green='1') Order by Contract_Number ASC";   
            break; 
        case 
    "BySafetyCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Guest='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByRoomCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Room_Safety='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByQualityCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Quality_Safety='1') Order by Contract_Number ASC";   
            break; 
        case 
    "BySupplyCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Supply_Safety='1') Order by Contract_Number ASC";   
            break; 
        case 
    "ByTourcheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit AND (Tourcheck='1') Order by Contract_Number ASC";   
            break;
        default:
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE RowNumber BETWEEN 
    $offset AND $limit order by ID DESC";  
            break;
    }

    $result sqlsrv_query($conn$sql); 
    $r sqlsrv_fetch_array($result); 
    $numrows $r[0]; 

    // number of rows to show per page 
    $rowsperpage 2;// 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 1
    $limit=($offset 1) + $rowsperpage;  

    //echo $query; 
    $stmt sqlsrv_query($conn$query);   

    /******  build the pagination links ******/// range of num links to show 
    $range 30
    echo 
    "<span style=font-family:Arial;font-size:14px;color:#333333;margin-top:5px;margin-bottom:5px;height:20px;margin-left:2px;>"
    // 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 "</span>"
    while(
    $data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC))           
    {

  13. #38
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It came back with an error below:

    Notice: Undefined variable: offset in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 311 Notice: Undefined variable: limit in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 311

    So I'm guessing that we need to perhaps put:

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

  14. #39
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I completely missed that, here is the updated code:
    PHP Code:
    <?php 
    // find out how many rows are in the table  

    switch($search

        case 
    "AllContractsOld-New":                 
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by ID ASC"
    ;   
            break; 
        case 
    "AllContractsNew-Old"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by ID DESC"
    ;   
            break; 
        case 
    "AllContractsOnHold"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold')"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Contract_Status='On Hold') Order by Contract_Number"
    ;   
            break; 
        case 
    "AllContractsActive"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active')"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Contract_Status='Active') Order by Contract_Number"
    ;   
            break; 
        case 
    "AllContractsTerminated"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated')"
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number"
    ;   
            break; 
        case 
    "ByCountryA-Z"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by Country ASC"
    ;   
            break; 
        case 
    "ByCountryZ-A"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by Country DESC"
    ;   
            break; 
        case 
    "ByRegionA-Z"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by Region ASC"
    ;   
            break; 
        case 
    "ByRegionZ-A"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by Region DESC"
    ;   
            break; 
        case 
    "ByNumberModulesO-X"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by No_of_Modules ASC"
    ;   
            break; 
        case 
    "ByNumberModulesX-O"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by No_of_Modules DESC"
    ;   
            break; 
        case 
    "ByContractValueLow-High"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC"
    ;   
            break; 
        case 
    "ByContractValueHigh-Low"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value DESC"
    ;   
            break; 
        case 
    "ByFoodCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Food_Safety='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByPoolCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByAquaCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Legionella_Safety='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByFireCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Fire_Safety='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByCrisisCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Hurricane='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByEcoCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Green='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "BySafetyCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Guest='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByRoomCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Room_Safety='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByQualityCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "BySupplyCheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Supply_Safety='1') Order by Contract_Number ASC"
    ;   
            break; 
        case 
    "ByTourcheckYes"
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1')";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
    select * from LIMIT WHERE {limit clause} AND (Tourcheck='1') Order by Contract_Number ASC"
    ;   
            break;
        default:
                    
    $sql "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
            
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
    select * from LIMIT WHERE {limit clause} order by ID DESC"
    ;  
            break;
    }

    $result sqlsrv_query($conn$sql); 
    $r sqlsrv_fetch_array($result); 
    $numrows $r[0]; 

    // number of rows to show per page 
    $rowsperpage 2;// 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 1
    $limit=($offset 1) + $rowsperpage;  
    $query str_replace('{limit clause}'"RowNumber BETWEEN $offset AND $limit"$query);

    //echo $query; 
    $stmt sqlsrv_query($conn$query);   

    /******  build the pagination links ******/// range of num links to show 
    $range 30
    echo 
    "<span style=font-family:Arial;font-size:14px;color:#333333;margin-top:5px;margin-bottom:5px;height:20px;margin-left:2px;>"
    // 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 "</span>"
    while(
    $data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC))           
    {

  15. #40
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That hasnt come back with any errors, and it has solved the issue of only showing whats relevant to the ordering selection.

    As in if only 1 needs to show, only 1 does, where before 1 appeared on page 1 but then the rest appeared on the other pages.

    But, and I'm sorry this is going on there is still 1 more issue.

    When you say select the highest contracts first it works fine on page 1 as in highest first, but the contracts on the other pages because of the pageing dont comply, they just appear as they wish, well you know what I mean, it doesnt comply with the ordering of page 1.

    Thank again cpradio, I am very grateful.

  16. #41
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    echo out the query for each page and post it here, that will likely lead to the problem.

  17. #42
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just checked it with a 'Terminated' contract ordering as there only 1 and the contract didnt show, so looked into it and as I have set it so that 2 contracts are to be displayed per page if anything that would have fallen onto the second page has to now appear on the first page, it doesnt happen.

    They are still odering by how the are in the database if you can see what I mean.

  18. #43
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    case "AllContractsOld-New":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by ID ASC

    case "AllContractsNew-Old":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by ID DESC

    case "AllContractsOnHold":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Contract_Status='On Hold') Order by Contract_Number

    case "AllContractsActive":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Contract_Status='Active') Order by Contract_Number

    case "AllContractsTerminated":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number

    case "ByCountryA-Z":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Country ASC

    case "ByCountryZ-A":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Country DESC

    case "ByRegionA-Z":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Region ASC

    case "ByRegionZ-A":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Region DESC

    case "ByNumberModulesO-X":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by No_of_Modules ASC

    case "ByNumberModulesX-O":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by No_of_Modules DESC

    case "ByContractValueLow-High":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC

    case "ByContractValueHigh-Low":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value DESC

    case "ByFoodCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Food_Safety='1') Order by Contract_Number ASC

    case "ByPoolCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC

    case "ByAquaCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Legionella_Safety='1') Order by Contract_Number ASC

    case "ByFireCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Fire_Safety='1') Order by Contract_Number ASC

    case "ByCrisisCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Hurricane='1') Order by Contract_Number ASC

    case "ByEcoCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Green='1') Order by Contract_Number ASC

    case "BySafetyCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Guest='1') Order by Contract_Number ASC

    case "ByRoomCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Room_Safety='1') Order by Contract_Number ASC

    case "ByQualityCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC

    case "BySupplyCheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Supply_Safety='1') Order by Contract_Number ASC

    case "ByTourcheckYes":
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} AND (Tourcheck='1') Order by Contract_Number ASC

    Default:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE {limit clause} order by ID DESC

  19. #44
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Sorry, probably should have been a bit more specific on location and what I would need to see.

    Change
    PHP Code:
    $stmt sqlsrv_query($conn$query); 
    To
    PHP Code:
    echo $query;
    $stmt sqlsrv_query($conn$query); 
    For example, just pick one search type (one that filters the results), then visit page 1, copy the query and paste it here, visit page 2, copy the query, and paste it here.

  20. #45
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry mate, OK have choosen the 'Contract Value Low High'

    Here is the echo of Page 1:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 Order by Annual_Contract_Value DESC

    Page 2:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC

    Just to point it out, the way they are order in the database is:

    DOM00001 - 16800
    DOM00002 - 1000
    DOM00003 - 3000
    DOM00004 - 0
    EGY00001 - 2000

    And this is how they appear in the pageing

    Page 1:
    DOM00002 - 1000
    DOM00001 - 16800

    Page 2:
    DOM00004 - 0
    DOM00003 - 3000

  21. #46
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Okay, first thing you should notice is that the ORDER BY clause is different for both pages.

    Page 1 orders by Annual_Contract_Value and Page 2 orders by ID.

    So what that means, is you need to some how persist the $search value across each page so it knows to sort it the correct way.

    One way would be to pass it along with each page

    Find
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "
    Replace with
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&Search=$search'><<</a> "
    Find
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "
    Replace with
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&Search=$search'><</a> "
    Find
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "
    Replace with
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x&Search=$search'>$x</a> "
    Find
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "
    Replace with
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&Search=$search'>></a> "
    Find
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "
    Replace with
    PHP Code:
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&Search=$search'>>></a> "

  22. #47
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right I see yes.

    I did that, and it carried through search, but its didnt appear to re-adjust the ordering for each page.

    I tested it with the contract value low high ordering and the results where the same.

  23. #48
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Okay, give me the query outputs again, and also records that are being displayed (their Contract_Number and their Annual_Contract_Value)

  24. #49
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK Have gone for High to Low Contract Values again, and here is the echo for
    Page 1:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 Order by Annual_Contract_Value DESC

    Page 2:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC
    The ordering is:

    Page 1:
    DOM00001 - 16800
    DOM00002 - 1000

    Page 2:
    DOM00004 - 0
    DOM00003 - 3000

  25. #50
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Okay, here is where it gets complicated again (also note that your $search variable was persisted on Page 2...)

    You need to update the WITH LIMIT statement to (ORDER BY Annual_Contract_Value DESC)

    Example:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%')

    This way it will number the records based on how you want to receive them. Likewise, if you have any where clauses (example: (Quality_Safety='1')), you need to add that to the LIMIT statement too, like so:
    PHP Code:
            $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1'))   
    select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC"



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
  •