Dynamic WHERE clause

[kelechi]

	$sbidDate	        = trim("".@$_POST["bidDate"]);
	$sdueDate	        = trim("".@$_POST["dueDate"]);
	$sprojectTitle		= trim("".@$_POST["projectTitle"]);
	$sbidId			    = trim("".@$_POST["bidId"]);
	$sBidType	        = trim("".@$_POST["BidType"]);
	$sBidStatus         = trim("".@$_POST["BidStatus"]);
	$sdepartment        = trim("".@$_POST["department"]);


if (@$_POST["SEARCH"] == "Search for Solicitation") {
    // yes...user pushed the SEARCH button in the <FORM>


    // Now build up the WHERE:
    $where = "";
    echo $where;
	if ($sbidDate != "") {
		$where = " AND b.BidDate = '".$sbidDate."' ";
	}
	if ($sdueDate != "") {
		$where = $where." AND b.DueDate = '".$sdueDate."' ";
	}
	if ($sprojectTitle != "") {
		$where = $where." AND b.ProjectTitle = '".$sprojectTitle."' ";
	}
	if ($sbidId != "") {
		$where = $where." AND b.BidID = '".$sbidId."' ";
	}
	if ($sBidType != "") {
		$where = $where." AND b.BidType = '".$sBidType."' ";
	}
	if ($sBidStatus != "") {
		$where = $where." AND b.BidStatus = '".$sBidStatus."' ";
	}
	if ($sdepartment != "") {
		$where = $where." AND b.Department = '".$sdepartment."' ";
	}

    //Now build the query. This query should allow users to search by one or more parameters

   $sql = "Select b.ID,convert(char(10),b.BidDate,101) BidDate,convert(char(10),
          b.DueDate,101) DueDate,b.BidTitle,b.DueTime,b.BidID,
          d.Department,b.BidType,CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe convert(char(10),b.AwardDate,101)END AS AwardDate,
          convert(char(10),b.LastUpdate,101) LastUpdate,s.Status
          FROM bids b inner join dept d on b.Department=d.DeptID inner join Status s on b.BidStatus=s.StatusId WHERE ".$where;

	$params = array();
	$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
	$query = sqlsrv_query( $conn, $sql , $params, $options );

	$num_rows = sqlsrv_num_rows($query);
	echo $num_rows;

Dear gurus, please forgive me if this is the wrong forum for this thread.

 

We are trying to build a dynamic where clause where a user can search by one or more parameters to get results.

So far, my attempts are producing the following error:

 

Any ideas how to fix this?

 

Thanks a lot in advance

[mac_gyver]

a) you didn't post the error, so kind of hard to specifically help you.

 

b) don't use @ error suppressors in any code. all they do is hide problems, such as typo errors in variable names.

 

c) all of your form processing code should be inside of a conditional statement that detects if the form was submitted. you will then know that all the text/password/textarea/select fields will be present and they won't produce any errors.

 

d) to detect if your form was submitted, without producing an undefined index error when it wasn't, use isset().

 

e) since you are getting and displaying results on a page, the form should use method='get'

 

f) assuming that it's valid to not supply any of the search terms (to display all results), you should not unconditionally put the WHERE keyword into the sql statement, as it will produce a query error without any sort of sql conditional statement after it. you should only put the WHERE keyword into the sql query statement if there will be a non-empty WHERE term.

 

g) you should build each dynamic where term as an element in an array, then simply implode the array with the AND keyword as the separator. you can test if this array is empty or not to determine if there is a WHERE clause to add to the query (see item f. in this list.)

 

h) you should be escaping external string data being put into the sql query statement (or use prepared queries) to prevent sql errors when the data contains special sql characters and to prevent sql injection.

 

i) have you echoed the final sql statement in $sql so that you know that it is what you intended?

 

j) when you have a set of things that you will process all in the same way, your list of form fields, you need to use a data driven design, where you have the list of the field names in a data structure of some type (array, database table) and that list is used as the input to a loop that controls what one copy of the code does, rather than to repeat the same program logic for each possible item in the list. this will let you change/reuse the code simply by changing the list of field names in the defining data structure. this defining data structure can also be expanded and used to dynamically produce the form, again, by having the code loop over the list of fields and produce the html, without you writing out by hand the html for each field.

[kelechi]

Thanks very much for your reply.

 

Trying to log was extremely frustrating with the new extremely sensitive captcha.

 

Then I tried as hard as I could to just copy and paste the error and it would not paste.

 

So, I included as part of the code.

 

I am surprised to see that it dropped off.

 

Any way, here is the error I am getting:

 

Warning sqlsrv_num_rows() expects parameter 1 to be resource, Boolean given in ...

 

And I had to type this error as I could not copy and paste. I am sure there is a method to the madness. I just need to figure it out.

 

As for not using where clause. I did something similar with asp and I am not sure how that would be different with php.

 

I tried echoing it but could not go beyond the error.

 

It is clear that I am newbie. Conversely, you have demonstrated your expertise so far.

 

I am sure there is a better way to do this.

 

Do you know if a link that I could perhaps use?

[mac_gyver]

the error message means that the sql query failed with an error of some type. the php sqlsrv_query() documentation contains an example showing how to test the result of the query and to display the sqlsrv_error() information that would tell you why the query failed.

[maxxd]

Looking at the way you're building your WHERE clause, it looks like there's no way it's not going to start with an AND keyword. You define $where with a blank string, then every if() branch concatenates a string beginning with ' AND...'. This will throw a syntax error in SQL - try printing the query before you run it and I think you'll see that's the error.

 

There are a couple ways around this. You could test the value of $where in each if() branch to see if it's blank or not before concatenating the keyword 'AND', like so:

$where = '';
if(!empty($sbidDate)){
	if(!empty($where)){
		$where .= " AND ";
	}
	$where .= "b.BidDate = '{$sbidDate}'";
}
if(!empty($sdueDate)){
	if(!empty($where)){
		$where .= " AND ";
	}
	$where .= "b.DueDate = '{$sdueDate}'";
}
...

Or, you could set up a blanket condition in the SQL before you append $where - something like:

$sql = "... WHERE 1=1 {$where};"

As mac_gyver pointed out in his replies, there are other issues with the code you've posted (you're wide open to SQL injection, the query as written will throw a SQL error if no search criteria is supplied, there's a lot of repeated code, etc.), but it looks like the main cause of the specific issue this thread is about is SQL syntax and the logic behind building the WHERE clause.

Edited March 8, 2015 by maxxd