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

PHP
1

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

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.


<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="<"><</option>
    <option value="<="><=</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="<"><</option>
    <option value="<="><=</option>
    ...
  </select>
  <input type="text" name="keyword[]" /><br />

  ...
</form>

You can then loop through the fields in your 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]);
       $operator= SanitizeOperator($_POST['operator'][$i]);
       $keyword= SanitizeKeyword($_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

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
Off Topic:

Thanks

As always, if you have questions or need something further explained, just ask :slight_smile:

5

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:


<?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

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

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

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

OK yes tried that:


  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

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

TUT, tut, tut!

OK you got it again…

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

12

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

Please post your updated form and code, I’ll need to see them to figure it out.

14

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)

  $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)
    {
      if (IsValidFieldName($_POST['field_names'][$i]) 
        && IsValidOperator($_POST['operator'][$i])) 
      { 
         $field_name = SanitizeFileName($_POST['field_names'][$i]); 
         $operator= SanitizeOperator($_POST['operator'][$i]); 
         $keyword= SanitizeKeyword($_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

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:


      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

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.

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

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.


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

Can you see the problem

18

I got it working, and decided to work it outside rather than inside.


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

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

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


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
}
}