SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 50 of 50
  1. #26
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, I forgot that the CTE needs to limit its' results too
    PHP Code:
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE{limit clause})  
    select * from LIMIT WHERE{limit clause}"
    // updated $query
      
    $limitClause "";
      for (
    $i 0$i sizeof($_POST['field_names']); $i++) 
      { 
        if (
    strlen(trim($_POST['field_names'][$i])) !== 
          
    && strlen(trim($_POST['operator'][$i])) !== 0
          
    //&& strlen(trim($_POST['keyword'][$i])) !== 0)
          
          
    {
                if ((
    $_POST['field_names'][$i])
                && (
    $_POST['operator'][$i]))
                    {       
                
                     
    $field_name = ($_POST['field_names'][$i]); 
                     
    $operator= ($_POST['operator'][$i]); 
                     
    $keyword= ($_POST['keyword'][$i]);
                 
                     if (
    $i === 0
                     { 
                     if (
    $operator=="like"){
                 
    $limitClause .= " " $field_name " " $operator " '" $keyword "%'"// updated
                    
    } else {
                         
    $limitClause .= " " $field_name " " $operator " '" $keyword "'"// updated
                    

                     } 
                     else 
                     { 
                        if (
    $operator=="like"){
                         
    $limitClause .= " AND " $field_name " " $operator " '" $keyword "%'"// updated
                        
    }else {
                         
    $limitClause .= " AND " $field_name " " $operator " '" $keyword "'"// updated
                        
    }  
                     } 
                } 
                else 
                { 
                    
    $queryIsValid false
                    echo 
    "required information missing or is invalid - query was not executed"
                    break; 
                } 
            } 
        }
      if (
    $queryIsValid
        {
            
    // read the $currentPage from the querystring...
            
    $offset = ($currentpage 1) * $rowsperpage
            
    $limit=$offset $rowsperpage;
            
                    
    $query str_replace('{limit clause}'$limitClause$query); // added this line
            
    $query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC"
        


  2. #27
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had white out for a little bit and went back through it and left some code out, but put it all back to how you have it, and the cms came back but with two errors.

    This error:

    Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\advanced_Search.php on line 832 WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE Contract_Number like 'DOM%' AND Row_Number BETWEEN 1 AND 50 order by ID DESC

    relates to this line:

    Code:
    $result = sqlsrv_query($conn, $query);
    $result2 = sqlsrv_query($conn, $query);
    $r = sqlsrv_fetch_array($result2); //This line is 832
    $numrows = $r[0];
    // number of rows to show per page
     if ($rows == ""){
     $rowsperpage = 50;
     } else {
    $rowsperpage = $rows;// find out total pages
    }
    Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\advanced_Search.php on line 941
    Code:
    while($data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))           
    {
    Thanks again

  3. #28
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Change
    PHP Code:
    $result2 sqlsrv_query($conn$query); 
    To
    PHP Code:
    $result2 sqlsrv_query($conn$query);
    if( 
    $result2=== false ) {
         die( 
    print_rsqlsrv_errors(), true));

    As a side note, why are you running the query twice?
    PHP Code:
    $result sqlsrv_query($conn$query);
    $result2 sqlsrv_query($conn$query); 

  4. #29
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the error:

    Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Syntax error, permission violation, or other nonspecific error [message] => [Microsoft][SQL Server Native Client 10.0]Syntax error, permission violation, or other nonspecific error ) )

    In honesty I'm not 100% sure, I did it as it seemed before there was a conflict and when I did it got it working.

    I was looking at it just now and thinking that it cant be right, and so I coded it out as below, and the same error came up:

    Code:
    //$result = sqlsrv_query($conn, $query);
    //$result2 = sqlsrv_query($conn, $query);
    $result = sqlsrv_query($conn, $query);
    if( $result=== false ) {
         die( print_r( sqlsrv_errors(), true));
    } 
    $r = sqlsrv_fetch_array($result); 
    $numrows = $r[0];
    // number of rows to show per page
     if ($rows == ""){
     $rowsperpage = 50;
     } else {
    $rowsperpage = $rows;// find out total pages
    }
    I suppose it was me using the old I fixed it, it seems to be working dont look back and move on situation, but they always came back to get you.

  5. #30
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by multichild View Post
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select * from LIMIT WHERE Contract_Number like 'DOM%' AND Row_Number BETWEEN 1 AND 50 order by ID DESC
    Got it!

    Your CTE defines the ROW_NUMBER() value as 'RowNumber' and your SELECT against the CTE is using Row_Number, Change the following line
    PHP Code:
    $query .= " AND Row_Number BETWEEN $offset AND $limit order by ID DESC" 
    To be
    PHP Code:
    $query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC" 
    Edit:

    Also make sure your CTE is on one line, and your SELECT * FROM LIMIT is on a separate line. Granted it shouldn't make a difference

  6. #31
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, thought you had it then, but it still came back with the same error sorry.

    Code:
    $offset = ($currentpage - 1) * $rowsperpage + 1; 
    $limit=($offset - 1) + $rowsperpage;  
    //$query = str_replace('{limit clause}', "RowNumber BETWEEN $offset AND $limit", $query);
    $query = str_replace('{limit clause}', $limitClause, $query); // added this line
    $query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
    echo $query; 
    $stmt = sqlsrv_query($conn, $query);

  7. #32
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I don't see anything wrong with the Query syntax (as it runs perfectly fine when I run it), are you sure you connection to the database was successful?

  8. #33
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes all seems fine as I could draw the contracts out.

    I've been up and down it too and comparing to the previous code and it seems fine as you say.

    I will go through it for an hour, and come back to it monday then.

    have a nice weekend.

    thanks again

  9. #34
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Last thing to try and to make sure you echo the query right before calling sqlsrv_query, then run that query in SSMS or something to verify the way it was outputted is accepted by SQL Server.

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

    I hope you dont mind me keeping this going with you.

    I worked backwards through our posts as at one point it was outputting but the pageing wasnt working, so have got it where it is working again, as in outputting the contracts.

    And have found what update causes the error.

    Basically, when the code below is as it is, the contracts output correctly, but they all come out on page 1 rather than the 50 allowed.
    Code:
    if ($i === 0) 
                     { 
                     if ($operator=="like"){
    				 	 $query .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
                    } else {
                         $query .= " " . $field_name . " " . $operator . " '" . $keyword . "'";
                    } 
                     } 
                     else 
                     { 
                        if ($operator=="like"){
                         $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'";
                        }else {
                         $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'";
                        }  
                     } 
                }
    We then changed that too:

    Code:
    if ($i === 0) 
                     { 
                     if ($operator=="like"){
                 $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                    } else {
                         $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                    } 
                     } 
                     else 
                     { 
                        if ($operator=="like"){
                         $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                        }else {
                         $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                        }  
                     } 
                }
    And we get the following error:

    sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\advanced_Search.php on line 834 WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE AND RowNumber BETWEEN '1' AND '50' order by ID DESC

    So its around there, the problem lies, but there nothing much there to look at, as in change.

    I will keep trying things, and if you can help that be great if not no worries.

  11. #36
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Make sure you updated the $query variable to (per my prior post)
    PHP Code:
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE{limit clause})  
    select * from LIMIT WHERE{limit clause}"
    // updated $query 
    And (per my prior post)
    PHP Code:
                    $query str_replace('{limit clause}'$limitClause$query); // added this line
            
    $query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC" 

  12. #37
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep got those in;

    Code:
    $query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select COUNT(*) from LIMIT WHERE {limit clause}";
    $limitClause = "";
      for ($i = 0; $i < sizeof($_POST['field_names']); $i++) 
      { 
        if (strlen(trim($_POST['field_names'][$i])) !== 0 
          && strlen(trim($_POST['operator'][$i])) !== 0) 
          //&& strlen(trim($_POST['keyword'][$i])) !== 0)
          
          {
                if (($_POST['field_names'][$i])
                && ($_POST['operator'][$i]))
                    {       
                
                     $field_name = ($_POST['field_names'][$i]); 
                     $operator= ($_POST['operator'][$i]); 
                     $keyword= ($_POST['keyword'][$i]);
                 
                     if ($i === 0) 
                     { 
                     if ($operator=="like"){
                 		 $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                    } else {
                         $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                    } 
                     } 
                     else 
                     { 
                        if ($operator=="like"){
                         $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                        }else {
                         $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                        }  
                     } 
                } 
                else 
                { 
                    $queryIsValid = false; 
                    echo "required information missing or is invalid - query was not executed"; 
                    break; 
                } 
            } 
        }
    
      if ($queryIsValid) 
    {
            
    //$result = sqlsrv_query($conn, $query);
    $result2 = sqlsrv_query($conn, $query);
    $result = sqlsrv_query($conn, $query);
    //if( $result=== false ) {
         //die( print_r( sqlsrv_errors(), true));
    //} 
    $r = sqlsrv_fetch_array($result); 
    $numrows = $r[0];
    // number of rows to show per page
     if ($rows == ""){
     $rowsperpage = 50;
     } else {
    $rowsperpage = $rows;// 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);
    $query = str_replace('{limit clause}', $limitClause, $query); // added this line
    $query .= " AND RowNumber BETWEEN '$offset' AND '$limit' order by ID DESC";
    echo $query; 
    $stmt = sqlsrv_query($conn, $query);
    i agree this is very strange, as nothing now stands out

  13. #38
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Ah, your are trying to execute the query before you replace the {limit clause} placeholder....

    Put this line
    PHP Code:
    $query str_replace('{limit clause}'$limitClause$query); // added this line 
    So it is right after
    PHP Code:
      if ($queryIsValid


  14. #39
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah yes progress...

    The error has now gone but no contracts in view but the pageing is working in that the correct number of pages are available for the pageing, but nothing on show and when page 2 of the pageing is clicked all the pageing data goes away, as in the number of pages.

    Thanks cpradio

  15. #40
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I would need to see your entire script to see why that is.

  16. #41
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok im am going through myself too, as dont want you thinking im not doing anything.

    Have emailed you a .txt file with it all in.

  17. #42
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, I see what is going on. You need two queries.

    You currently have this
    PHP Code:
        $query        "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select COUNT(*) from LIMIT WHERE {limit clause}"

    Notice, it is ONLY returning the COUNT(*)

    So you need a second query that returns the contracts.
    PHP Code:
        $query        "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select COUNT(*) from LIMIT WHERE {limit clause}"
    ;
        
    $queryContracts "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select * from LIMIT WHERE {limit clause}"

    Then you need to update the limit clause in the new query too
    PHP Code:
            $query   str_replace('{limit clause}'$limitClause$query); // added this line
            
    $queryContracts str_replace('{limit clause}'$limitClause$queryContracts); // added this line 
    Then we need to limit the results of the Contracts query so the following line
    PHP Code:
            $query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
            echo 
    $query;
            
    $stmt sqlsrv_query($conn$query); 
    Becomes
    PHP Code:
            $queryContracts .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
            echo 
    $queryContracts;
            
    $stmt sqlsrv_query($conn$queryContracts); 
    Then you need to use the new query's results by replacing the following line
    PHP Code:
            while ($data sqlsrv_fetch_array($resultSQLSRV_FETCH_ASSOC)) { 
    With
    PHP Code:
            while ($data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC)) { 

  18. #43
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah sorry cpradio, I missed that you replied, was trying to see how to fix it, but wouldnt have got there by the seems, and have put your code in and the pageing on page 1 is correct, as 50 display and the contracts are there to see, which is fantastic.

    I'm sorry to say but when I click page 2 the page refreshes, not pageing goes away and the contracts dont display, it basically goes back to how it is when you first go into the page, so I'm guessing im missing a variable or something.

    Again as I dont want to bother you all day today again, will see if I can see it.

    Thank you again cpradio, its looking great

  19. #44
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm guessing this is the troubel
    Code:
    <?
    if(isset($_POST['advrows']))  
    {
    As its relying on the button click from the form to perform the search
    Code:
    <input type="submit" name="advrows" value="Submit">

  20. #45
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I knew the paging wouldn't work going from page to page, but that is due to entirely different reasons, I wanted to make sure you got the results on Page 1 first before pursuing the rest.

    This is going to be a bit of a process (so please ask questions, if you don't understand how it works).

    First off, the following IF statement contains TOO much code
    PHP Code:
    if (isset($_POST['advrows'])) { 
    It contains EVERYTHING for your output, and that isn't necessary. What it needs to contain should be limited to building the query.

    So to get this on the right track, here is the updated IF statement (make sure you remove the additional } near the end of your page).
    PHP Code:
    //var_dump($_POST); 
    $queryIsValid true;
    //$query = "SELECT * FROM Intranet WHERE";
    $query        "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select COUNT(*) from LIMIT WHERE {limit clause}"
    ;
    $queryContracts "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select * from LIMIT WHERE {limit clause}"
    ;
    $limitClause  "";

    if (isset(
    $_POST['advrows'])) {
        for (
    $i 0$i sizeof($_POST['field_names']); $i++) {
            if (
    strlen(trim($_POST['field_names'][$i])) !== && strlen(trim($_POST['operator'][$i])) !== 0)
            
    //&& strlen(trim($_POST['keyword'][$i])) !== 0)
                
    {
                if ((
    $_POST['field_names'][$i]) && ($_POST['operator'][$i])) {
                    
    $field_name = ($_POST['field_names'][$i]);
                    
    $operator   = ($_POST['operator'][$i]);
                    
    $keyword    = ($_POST['keyword'][$i]);
                    
                    if (
    $i === 0) {
                        if (
    $operator == "like") {
                            
    $limitClause .= " " $field_name " " $operator " '" $keyword "%'"// updated
                        
    } else {
                            
    $limitClause .= " " $field_name " " $operator " '" $keyword "'"// updated
                        
    }
                    } else {
                        if (
    $operator == "like") {
                            
    $limitClause .= " AND " $field_name " " $operator " '" $keyword "%'"// updated
                        
    } else {
                            
    $limitClause .= " AND " $field_name " " $operator " '" $keyword "'"// updated
                        
    }
                    }
                } else {
                    
    $queryIsValid false;
                    echo 
    "required information missing or is invalid - query was not executed";
                    break;
                }
            }
        }

        
    $_SESSION['query'] = $query// added this line
        
    $_SESSION['queryContracts'] = $queryContracts// added this line

    Okay, then we need to add an else statement to the above IF
    PHP Code:
    else
    {
      if (isset(
    $_SESSION['query']) && isset($_SESSION['queryContracts']))
      {
        
    $query $_SESSION['query'];
        
    $queryContracts $_SESSION['queryContracts'];
      }

    The above will allow your queries to be passed from page to page now. And when a new search is performed, it will update the session with the new search criteria.

    Finally, in the code to build the paging, remove ALL references to "&Search=$search&q=$searchKeyword", those DO NOT exist in this context and were likely copied forward from your prior scripts (I counted 6 references to these).

    You can also get rid of these sections of code, as they don't apply
    PHP Code:
            if ($search == "") {
            } else {
                echo 
    "<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:auro;line-height:20px;top:-4px;left:7px;margin-right:10px;>Search: <strong>'$search'</strong>&nbsp;&nbsp;|&nbsp;&nbsp;</span>";
            }
            
            
            if (isset(
    $_GET['q'])) {
                
    $searchKeyword = @$_GET['q'];
                if (
    $searchKeyword == "") {
                } else {
                    
    $trimmed $searchKeyword;
                    echo 
    "<span style=position:relative;margin=0px;margin-bottom:5px;margin-left:2px;top:-3px;font-size:13px;color:#555555;font-family:verdana;position:relative;>Search results for your keyword: <strong>'" $trimmed "'</strong></span></div>";
                }
            } 

  21. #46
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AHHHHHHHHHHHH,

    Sorry cpradio, I changed to your code, and went through it and through it and page 2 of pageing is still having the same problem, in that all the contracts go away and the pageing goes away too, it reverts back to how it is when you first enter the page. I wish I could help you out some more, and have tried.

    Code:
    if(isset($_POST['advrows']))  
    {
    //var_dump($_POST); 
    $queryIsValid = true;
    //$query = "SELECT * FROM Intranet WHERE";
    $query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select COUNT(*) from LIMIT WHERE {limit clause}";
    $queryContracts = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
    select * from LIMIT WHERE {limit clause}";
    $limitClause  = "";
    
    if (isset($_POST['advrows'])) {
        for ($i = 0; $i < sizeof($_POST['field_names']); $i++) {
            if (strlen(trim($_POST['field_names'][$i])) !== 0 && strlen(trim($_POST['operator'][$i])) !== 0)
            //&& strlen(trim($_POST['keyword'][$i])) !== 0)
                {
                if (($_POST['field_names'][$i]) && ($_POST['operator'][$i])) {
                    $field_name = ($_POST['field_names'][$i]);
                    $operator   = ($_POST['operator'][$i]);
                    $keyword    = ($_POST['keyword'][$i]);
                    
                    if ($i === 0) {
                        if ($operator == "like") {
                            $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                        } else {
                            $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                        }
                    } else {
                        if ($operator == "like") {
                            $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                        } else {
                            $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                        }
                    }
                } else {
                    $queryIsValid = false;
                    echo "required information missing or is invalid - query was not executed";
                    break;
                }
            }
        }
    
        $_SESSION['query'] = $query; // added this line
        $_SESSION['queryContracts'] = $queryContracts; // added this line
    } 
    else
    {
      if (isset($_SESSION['query']) && isset($_SESSION['queryContracts']))
      {
        $query = $_SESSION['query'];
        $queryContracts = $_SESSION['queryContracts'];
      }
    }
    
      if ($queryIsValid) 
    {
    
     $query = str_replace('{limit clause}', $limitClause, $query); // added this line
         $queryContracts = str_replace('{limit clause}', $limitClause, $queryContracts); // added this line       
    //$result = sqlsrv_query($conn, $query);
    //$result2 = sqlsrv_query($conn, $query);
    $result = sqlsrv_query($conn, $query);
    //if( $result=== false ) {
         //die( print_r( sqlsrv_errors(), true));
    //} 
    $r = sqlsrv_fetch_array($result); 
    $numrows = $r[0];
    // number of rows to show per page
     if ($rows == ""){
     $rowsperpage = 50;
     } else {
    $rowsperpage = $rows;// 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);
    //$query = str_replace('{limit clause}', $limitClause, $query); // added this line
    //$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
    //echo $query;
    $queryContracts .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
    //echo $queryContracts;
    $stmt = sqlsrv_query($conn, $queryContracts);  
    
    //$stmt = sqlsrv_query($conn, $query);
    //var_dump($conn, $query);   
    
    /******  build the pagination links ******/// range of num links to show 
    $range = 30; 
    echo "<div style=position:relative;height:30px;line-height:30px;><span style=font-family:Arial;font-size:14px;color:#333333;margin-top:9px;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  
    // was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&Rows=$rows' style='color:#333333'><<</a>";   
    // get previous page num    
    $prevpage = $currentpage - 1;    
    // show < link to go back to 1 page    
    // was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; 
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&Rows=$rows' style='color:#333333'><</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          
    // was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x&Rows=$rows' style='color:#333333'>$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     
    // was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; 
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&Rows=$rows' style='color:#333333'>></a> ";   
    // echo forward link for lastpage    
    // was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&Rows=$rows' style='color:#333333'>>></a> ";
    } // end if 
    /****** end build pagination links ******/ 
    echo "</span>";
    if ($rows=="1000000"){
    echo "<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:143px;line-height:20px;top:-4px;left:30px;>Showing <strong>'All'</strong>.&nbsp;&nbsp;|&nbsp;&nbsp;</span>";
    } else {
    echo "<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:200px;line-height:20px;top:-4px;left:30px;>Showing <strong>'$rowsperpage'</strong> per page&nbsp;&nbsp;|&nbsp;&nbsp;</span>";
    }
    
    if(isset($_GET['q']))
    {
      $searchKeyword = @$_GET['q'];
      if ($searchKeyword=="") {
      } else {
      $trimmed = $searchKeyword;
      echo "<span style=position:relative;margin=0px;margin-bottom:5px;margin-left:2px;top:-3px;font-size:13px;color:#555555;font-family:verdana;position:relative;>Search results for your keyword: <strong>'" .$trimmed. "'</strong></span></div>";
      }
    }
    // To change line colour
    $color="1";
    //echo $query;
    while ($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {

  22. #47
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Can you email me your updated file? Or attach it? Maybe use http://pastebin.com?

  23. #48
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I screwed up. It wasn't $query and $queryContracts that the session needed to keep track of, but rather $limitClause.

    You have this code
    PHP Code:
            $_SESSION['query']          = $query// added this line
            
    $_SESSION['queryContracts'] = $queryContracts// added this line
        
    } else {
            if (isset(
    $_SESSION['query']) && isset($_SESSION['queryContracts'])) {
                
    $query          $_SESSION['query'];
                
    $queryContracts $_SESSION['queryContracts'];
            }
        } 
    Replace it with
    PHP Code:
            $_SESSION['limitClause'] = $limitClause// added this line
        
    } else {
            if (isset(
    $_SESSION['limitClause'])) {
                
    $limitClause $_SESSION['limitClause'];
            }
        } 

  24. #49
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Oh! And another piece. Remove this line (which appears above the commented out var_dump($_POST) call)
    PHP Code:
    if (isset($_POST['advrows'])) { 
    Then remove the last } as shown below
    PHP Code:
    //remove this one!
    ?>
    <table>
    <tr>
    <td>
    <div style="position:relative; width:100%; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:12px; margin-top:10px;">Return to <a href="#Top">TOP</a></div>
    <div style="position:relative;float:left;height:50px;width:200px;margin-top:20px;margin-left:-2px;"><a href="http://www.checksafetyfirst.com" target="_blank"><img src="images/csf_logo.jpg" border="0"></a></div>
    </td>
    <tr>
    </table>
    </body>
    </html> 

  25. #50
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Monring cpradio,

    Thank you very much, that works perfectly, and its also very good that it remembers your search if you leave the page and come back too.

    Thank you again...

    I got a few things to finish this off, but you be happy to know you will be free of me today

    Thanks again though, its a lot to take in, but I think when I keep going through it and trying things out, it will sink in, but its very high end stuff, and I appreciate your help.


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
  •