SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 50
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Building an advanced search option using PHP & MSSQL and need some advice

    I am about to start to build an advanced search option in my contract database.

    I am building it using MSSQL and PHP, and have seen one in MySQL using build query, I wondered if somebody can give me some advice as the methods and code to do this.

    Basically the advanced search will be 10 rows of drop downs, the first line having a drop down to select the field name, then the second drop down to select options of whether its +, =, <, > and so on, and then the final text field an open text input field for the keyword.

    Thats the first line, but there 9 more of those, and they coudl all be used and build up to be the advanced search.

    I havent started it yet, but just wanted to get some advice of the pro's.

    Cheers

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    You would simply concatenate your query together using the data you receive from the form. Now you will definitely want to perform a LOT of validation, ensuring the operator is =, <, <=, >, or >=, and not an invalid value (even though it is a drop down; you need this!)

    Same with the field names, you will want to compare the field name selected to ensure it is a valid field name (keep an array of field names or query SQL Server to get a list of column names to compare against).

    Finally, sanitize your keyword by replacing a single quote with two single quotes, etc (I believe we talked about this before in a previous thread).

    One other thing to consider is whether the user would want to perform an AND or OR condition between the 10 rows (or per row) in the query, so you may need to provide that option, or if it is always considered AND/OR, then you can forgo providing that option.

    Your form can be setup a couple of different ways, I personally like to use an array approach.
    HTML Code:
    <form method="post" action="">
      <select name="field_name[]">
        <option value="">Select One</option>
        <option value="contract_id">Contract ID</option>
        ...
      </select>
      <select name="operator[]">
        <option value="">Select One</option>
        <option value="<">&lt;</option>
        <option value="<=">&lt;=</option>
        ...
      </select>
      <input type="text" name="keyword[]" /><br />
    
    
      <select name="field_name[]">
        <option value="">Select One</option>
        <option value="contract_id">Contract ID</option>
        ...
      </select>
      <select name="operator[]">
        <option value="">Select One</option>
        <option value="<">&lt;</option>
        <option value="<=">&lt;=</option>
        ...
      </select>
      <input type="text" name="keyword[]" /><br />
    
      ...
    </form>
    You can then loop through the fields in your code
    PHP Code:
    <?php
      $queryIsValid 
    true;
      
    $query "SELECT * FROM table WHERE";
      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
          
    && IsValidFieldName($_POST['field_names'][$i])
          && 
    IsValidOperator($_POST['operator'][$i]))
        {
           
    $field_name SanitizeFileName($_POST['field_names'][$i]);
           
    $operatorSanitizeOperator($_POST['operator'][$i]);
           
    $keywordSanitizeKeyword($_POST['keyword'][$i]);

           if (
    $i === 0)
           {
             
    $query .= " " $field_name " " $operator " '" $keyword "'";
           }
           else
           {
             
    $query .= " AND " $field_name " " $operator " '" $keyword "'";
           }
        }
        else
        {
          
    $queryIsValid false;
          echo 
    "required information missing or is invalid - query was not executed";
          break;
        }
      }

      if (
    $queryIsValid)
      {
         
    // execute query
      
    }
    ?>

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

    Thanks a lot for getting back to me, its funny but I sort of hope you do , the mentor title you got is spot on.

    Thank you, I will go through all above and make a start, but think it best I try and get my head around it first.

    I'm reading it through now, and yes can see your method, but will still need to go through one line at a time.

    Thanks again cpradio, hope your well.

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

    Thanks a lot for getting back to me, its funny but I sort of hope you do , the mentor title you got is spot on.
    Off Topic:

    Thanks


    Quote Originally Posted by multichild View Post
    Thank you, I will go through all above and make a start, but think it best I try and get my head around it first.

    I'm reading it through now, and yes can see your method, but will still need to go through one line at a time.

    Thanks again cpradio, hope your well.
    As always, if you have questions or need something further explained, just ask

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

    i made a start on this late last week and picking it up again todaya, and have made a little bit of progress but wondered if you can explain something else to me, or maybe I will write what I think it is which I have tried and if you could let me know what you think.

    As you will see for now I have taken away the checks on the inputs, but will put it all back in.

    Code:
    Code:
    <?php
    error_reporting(E_ALL);
    ini_set('display_errors','On');
    if(isset($_POST['advrows']))  
    { 
      $queryIsValid = true; 
      $query = "SELECT * FROM Intranet WHERE"; 
      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 
          && ($_POST['field_names'][$i]) 
          && ($_POST['operator'][$i])) 
        { 
           $field_name = ($_POST['field_names'][$i]); 
           $operator= ($_POST['operator'][$i]); 
           $keyword= ($_POST['keyword'][$i]); 
    
           if ($i === 0) 
           { 
             $query .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; 
           } 
           else 
           { 
             $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; 
           } 
        } 
        else 
        { 
          $queryIsValid = false; 
          echo "required information missing or is invalid - query was not executed"; 
          break; 
        } 
      } 
    
      if ($queryIsValid) 
      { 
         // execute query
    	 echo $query; 
      }
    }
    ?>
    
    <form method="post" action="advanced_Search.php">
    <select name="field_names[]">
        <option value="">Choose Field</option>
        <option value="ID">Database ID</option>
    </select>
    
    <select name="operator[]">
        <option value="">Choose Operator</option>
        <option value="==">equals (=)</option>
    </select>
    
    <input type="text" name="keyword[]" />
    
    <select name="field_name[]">
        <option value="">Choose Field</option>
        <option value="ID">Database ID</option>
    </select>
    <select name="operator[]">
        <option value="">Choose Operator</option>
        <option value="<">does not contain</option>
    </select>
    <input type="text" name="keyword[]" />
    <input type="submit" name="advrows" value="Submit" class="btn-search">
    When I select ID, equal to and enter 1 in the keyword area and click submit, i get the right echo
    SELECT * FROM Intranet WHERE ID == '1'

    But what Im trying to do now is do the same for the second part and add it to the bit above, so as you have it will be something like this.

    SELECT * FROM Intranet WHERE ID == '1' AND WHERE ID == '2'

    So how do I get the 2 to join up using your example.

    I've got a bit of brain freeze and was going to just sit it out and keep trying, but thought Id seek a bit of help.

    Thanks

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I don't quite understand how my code could provide a second WHERE after the AND? As unless WHERE is located in the field_name, it shouldn't be capable of doing that.

    The beginning of the query is established at the top
    $query = "SELECT * FROM table WHERE";

    Then the form fields are read, and it concatenates the field_name, operator, and keyword to the query
    $query is now "SELECT * FROM table WHERE ID == '1'

    Then the second set of form fields are read and it concatenates it to the new query, so your query becomes
    $query is now "SELECT * FROM table WHERE ID == '1' AND ID == '2'

    Granted that query would never return a result as an ID could not be 1 and 2 at the same time, but that is beyond the point.

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

    Sorry yes, I must have typed it as i was thinking it if you know what I mean.

    Yes I do mean:

    "SELECT * FROM table WHERE ID == '1' AND ID == '2'

    But i cant seem to get the 2nd row to echo out as above, only the first part.

    And again yes the ID being looked for twice is not right, but just trying to work my way through it.

    Thanks

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    What output (if any) are you getting for the second test you are running? Maybe put the echo $query outside of the if ($queryIsValid) statement

  9. #9
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK yes tried that:
    Code:
      if ($queryIsValid) 
      { 
         // execute query
    	 //echo $query; 
      }
      echo $query;
    And get the same result. After selecting the first line of drop downs as ID == 1 and the second line of ID == 2 I just get the one line to echo out:

    SELECT * FROM Intranet WHERE ID == '1'

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Ah, you have a typo on your field_names HTML. Your second field_names drop down is field_name[] instead of field_names[]

    Also, next time you may want to put var_dump($_POST) at the top of your script, it will show you everything that is posted back to you from your form

  11. #11
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    TUT, tut, tut!

    OK you got it again...

    Thanks cpradio, will crack on now and see where I get too.

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

    Sorry to bother you with this again, but I seem to have a problem and wondered if you wouldnt mind having a look.

    Yesterdays error fox of a mis-spelling allowed me to put the ten lines together, and when I tried all of them it worked great. The putput reflected the selections of each line, and though great thats job done now to get it working with the proper contracts from the database, but I tried to do just one and it seems that not filling in all of them together means it goes to the error: required information missing or is invalid - query was not executed

    In some cases all 10 will be used, but I think that will be very rare, so its more the case that perhaps 1 line will be used, and maybe up to 3 regularly, but more than that probably not very often.

    Sorry

  13. #13
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Please post your updated form and code, I'll need to see them to figure it out.

  14. #14
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    nm, I found the issue in my original code.

    Here is the updated code (notice the first IF statement is now broken into two IF statements)
    PHP Code:
      $queryIsValid true
      
    $query "SELECT * FROM table WHERE"
      for (
    $i 0$i sizeof($_POST['field_names']); $i++) 
      { 
        if (
    strlen(trim($_POST['field_names'][$i])) !== 
          
    && strlen(trim($_POST['operator'][$i])) !== 
          
    && strlen(trim($_POST['keyword'][$i])) !== 0)
        {
          if (
    IsValidFieldName($_POST['field_names'][$i]) 
            && 
    IsValidOperator($_POST['operator'][$i])) 
          { 
             
    $field_name SanitizeFileName($_POST['field_names'][$i]); 
             
    $operatorSanitizeOperator($_POST['operator'][$i]); 
             
    $keywordSanitizeKeyword($_POST['keyword'][$i]); 

             if (
    $i === 0
             { 
               
    $query .= " " $field_name " " $operator " '" $keyword "'"
             } 
             else 
             { 
               
    $query .= " AND " $field_name " " $operator " '" $keyword "'"
             } 
          } 
          else 
          { 
            
    $queryIsValid false
            echo 
    "required information missing or is invalid - query was not executed"
            break; 
          } 
        }
      } 

  15. #15
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got you yes, I'm not going to pretend that I didnt see it, and could have worked it out myself, because I couldnt have, but thank you anyway it works perfectly.

    Do you mind if I ask you a question.

    Why in the code below:

    Code:
          if (IsValidFieldName($_POST['field_names'][$i]) 
            && IsValidOperator($_POST['operator'][$i]))
    Do you not have any reference to keyword, only to the first two of field_names and operator

  16. #16
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I don't reference keyword, because I plan to just sanitize the keyword and not validate it. If you want to validate it, you can, but you will have a lot of cases to write, for example:

    You will need to check the field_name value and run validation on the keyword based on what field_name is selected.
    PHP Code:
    if($field_name == 'ID')
    {
      return 
    is_int($keyword);
    }
    else if (
    $field_name == "Description")
    {
      
    //not sure what you would validate against?
      
    return is_string($keyword); // maybe? seems pointless
    }
    else if (
    $field_name == 'Phone')
    {
      return 
    preg_match('/\d{3}\-\d{3}\-\d{4}/'$keyword); // matches 000-000-0000 format
    }
    etc... 
    In my opinion, it is easier (borderline lazy) to just sanitize the keyword and run the query, when the user gets back zero results they will check their input to see why.

  17. #17
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's working great now, and so I have started putting the operators in and have come to the like option I need to put in.

    I tried it without the if statement and it worked fine, but the way I have the if statement below seems to be causing white out.

    Code:
    $query .= " " . $field_name . " " . $operator . " '" . $keyword if ($operator=="like"){. "%'";}else{. " '";}
    Can you see the problem

  18. #18
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it working, and decided to work it outside rather than inside.

    Code:
    	   		if ($operator == "like"){
    			$query .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
    			} else {
    			$query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'";
    			}

  19. #19
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by multichild View Post
    I got it working, and decided to work it outside rather than inside.

    Code:
    	   		if ($operator == "like"){
    			$query .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
    			} else {
    			$query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'";
    			}
    In my opinion, this was the better approach, whenever you plan to deviate how something is built, breaking it out like this makes it much more maintainable.

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

    I am made good progress and it seemed I had it finished until I tried to leave the keyword option blank.

    What I was trying to see was if I say asked for all contracts with tabel name of 'Group' and '=' and then left the keyword option blank it didnt like it and it came back with the error below:

    Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\csfintranet\advanced_Search.php on line 826 SELECT * FROM Intranet WHERE
    Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\csfintranet\advanced_Search.php on line 934

    Code:
    if(isset($_POST['advrows']))  
    {
    //var_dump($_POST); 
      $queryIsValid = true; 
      $query = "SELECT * FROM Intranet WHERE"; 
      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"){
    		 $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 . "'";
    		 }  
           } 
        } 
        else 
        { 
          $queryIsValid = false; 
          echo "required information missing or is invalid - query was not executed"; 
          break; 
        } 
      } 
    }
      if ($queryIsValid) 
    {
    output here
    }
    }

  21. #21
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Remove the following line if you want to allow keyword to be blank
    PHP Code:
    && strlen(trim($_POST['keyword'][$i])) !== 

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

    I thought I had it sorted again, but to only see that the pagination doesnt work properly, so I skipped through the other stuff we did, and noticed the code below:

    Code:
     $sql = "SELECT COUNT(*) FROM Intranet";
    $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet)  
    select * from LIMIT WHERE {limit clause} order by ID DESC";
    The row_number im guessing controls the numbers for each page and that allows it to work properly.

    Can you show me how I can integrate that into the below:

    Code:
    $query = "SELECT * FROM Intranet WHERE"; 
      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"){
    		 $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 . "'";
    		 }  
           } 
        } 
        else 
        { 
          $queryIsValid = false; 
          echo "required information missing or is invalid - query was not executed"; 
          break; 
        } 
      } 
    }
      if ($queryIsValid) 
    {

  23. #23
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Can you remind me what the pagination code looks like? What where clause we added to it?

  24. #24
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I think I found it:
    PHP Code:
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet)  
    select * from LIMIT WHERE"
    ;
      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"){
                 
    $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 "'";
                        }  
                     } 
                } 
                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 .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC"
        


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

    I changed it over and echo'd it out and got this belo, when I select Contract_Number like DOM%:

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

    Which as far as I can see is correct, but the pageing isnt working correctly, in that firstly its only meant to be 50 per page, but its bring out all the contracts and so displaying them all on the first page, and then the pageing seems to be offering more pages than contracts.

    I have added it all below again sorry cpradio, but this last bit is now it I think (until Monday morning, when they find more problems)

    Thanks again

    $queryIsValid = true;
    //$query = "SELECT * FROM Intranet WHERE";
    $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet)
    select * from LIMIT WHERE";
    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"){
    $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 . "'";
    }
    }
    }
    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);
    $r = sqlsrv_fetch_array($result2);
    $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 .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";

    echo $query;
    $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&Search=$search&q=$searchKeyword&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&Search=$search&q=$searchKeyword&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&Search=$search&q=$searchKeyword&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&Search=$search&q=$searchKeyword&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&Search=$search&q=$searchKeyword&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 ($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>";
    }
    }
    // To change line colour
    $color="1";
    //echo $query;
    while($data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
    {


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
  •