SQL problem, help needed

[dast]

Im tryin to construct an SQL statement, the criteria comes from six search boxes.

//construct the SQl statement for search facility 
$bolFirst = "true"; 

$strSQL = "SELECT beneficiary_id, beneficiary_name FROM tblBeneficiaries"; 
if ($_SERVER['REQUEST_METHOD'] == "POST") { 


while(list($key,$val) = each ($_POST)) { 
if ($val != "") { 

if ($bolFirst != true) { 
$strSQL = $strSQL . " OR "; 
} 
else { 
$strSQL = $strSQL . " WHERE "; 
} 
if ($key = "beneficiary_id") { 
$strSQL = $strSQL . $key . " = " . $val; 
} 
else { 
$strSQL = $strSQL . "(" . $key . ") LIKE '%" . $val . "%'"; 
} 
$bolFirst = false; 

} 
} while(list($key,$val) = each ($_POST)); 

} 

$strSQL = $strSQL . " ORDER BY beneficiary_id ASC"; 
echo $strSQL; 

This is the SQL i get if i fill all the form fields;

SELECT beneficiary_id, beneficiary_name FROM tblBeneficiaries WHERE beneficiary_id = 1 OR beneficiary_id = test OR beneficiary_id = fjlkj OR beneficiary_id = fjkjfk OR beneficiary_id = jsdkj ORDER BY beneficiary_id ASC

It doesnt seem to recognise any of the other fields, just sticks on beneficiary_id. Any help would be greatly appreciated.

Thanks

[HalfaBee]

You may need to enclose your tests for strings in quotes.

e.g. beneficiary_id = 'fjkjfk'

HalfaBee

[dast]

That has nothing to do with it, its the actual statment the script is creating that is incorrect. The thing is there are 6 form fields, named beneficiary_id, company_name etc. and what I want this script to do is create the SQL statment dynamically. But instead it thinks all the fields are named beneficiary_id.

So instead of this:
WHERE beneficiary_id = 1 OR beneficiary_id = test OR beneficiary_id = fjlkj OR beneficiary_id = fjkjfk

It should be this:
WHERE beneficiary_id = 1 OR company_name = test OR postcode = fjlkj OR email = fjkjfk

Any ideas?

[HalfaBee]

You have two while() statements.
You only need the first one.

When posting PHP use

tags to include the code and it makes it easier to read the code.

HalfaBee

[dast]

Ok will do. Took out the last while statement, still no change. Any other ideas as to why its doing this? thanks

[HalfaBee]

What is wrong with this line?

   if ($key = "beneficiary_id")

Halfabee

[dast]

Well if its the ID box thats filled in the SQL statement will be = to that number, whereas the other search fields just use the LIKE statement. Is that what you meant?

[laserlight]

put it another way.

what is the "=" operator?

[HalfaBee]

The = sign is in PHP not mysql.

If I tell you the answer you may never learn, Monkey.

HalfaBee

[dast]

My bad. Its all suddenly clicked into place, now it works. thanks for helping a mere monkey 🙂