PHP - working with multiple sql where clauses

[memprog]

I am writing a small php application that returns results based on information a user can enter into multiple text fields.

here is what I have:

search.php
-------------
Order Info ID:
<INPUT type="text" maxLength=100 size=17 name=orderId>
TELEPHONE:
<INPUT type="text" maxLength=20 size=14 name=phone>
PON:
<INPUT type="text" maxLength=100 size=27 name=ponValue>


results.php
----------------
<?
$pon = $_POST['ponValue'];
$telephone = $_POST['phone'];
$order = $_POST['orderID'];

$select = "select * from orderinfo and day <>'' " ;
$whereClause = "";
$res = $db_connection->Execute($select);


?>

<?
//this is what I don't know how to do.
//I need to add the where clause to the sql statement if the user
//..has entered a value into any of the text fields

if($pon !="")
{
whereClause = " and pon = ".'$pon';
echo $res->Fields['$pon']->Value;
}
?>

How can I add this where clause to my sql statment based on what value the user has entered?

please help.

thanks,
Mark

[GJay]

PHP Code:

$select = "select * from orderinfo and day <>'' " ;
$whereClause = "";
if(isset($pon))
  $where.='pon='.mysql_real_escape_string($pon).' AND';
if(isset($telephone))
  $where.='telephone='.mysql_real_escape_string($telephone).' AND';
if(isset($order))
  $where.='order='.mysql_real_escape_string($order).' AND';
if($where!='')
  $where=' WHERE '.substr($where,0,-4);
$select.=$where;
$res = $db_connection->Execute($select); 


[phpfr33k]

Originally Posted by GJay

PHP Code:

$select = "select * from orderinfo and day <>'' " ;
$whereClause = "";
if(isset($pon))
  $where.='pon='.mysql_real_escape_string($pon).' AND';
if(isset($telephone))
  $where.='telephone='.mysql_real_escape_string($telephone).' AND';
if(isset($order))
  $where.='order='.mysql_real_escape_string($order).' AND';
if($where!='')
  $where=' WHERE '.substr($where,0,-4);
$select.=$where;
$res = $db_connection->Execute($select); 


Don't want to sound pushy, but there are a couple of other ways to do this.

PHP Code:

$select = "select * from orderinfo and day <>'' " ;
$whereClause = "";
if(!empty($pon))
  $where.='pon='.mysql_real_escape_string($pon).' AND';
if(!empty($telephone))
  $where.='telephone='.mysql_real_escape_string($telephone).' AND';
if(!empty($order))
  $where.='order='.mysql_real_escape_string($order).' AND';
if($where!='')
  $where=' WHERE '.substr($where,0,-4);
$select.=$where;
$res = $db_connection->Execute($select); 


by using the empty function you stop someone from entering blank characters in a form which would not get caught by isset, and would trigger an error.
Although, If your a fan of arrays... You might try this.

PHP Code:


$select = "select * from orderinfo and day <>'' " ;

if(count($_GET) > 3){

exit; // Someone is trying to inject get variables!

}
elseif(count($_GET) > 1){

foreach($_GET as $x => $y){

$arr_where[] = "(`" . $x . "` LIKE '" . $y . "')";

}

$str_where = implode(" AND ",$arr_where);

$select .= " WHERE " . $str_where;

} 


This would allow for scalability. If suddenly you need to ask for more information, It is all still optional just change the 3 in the first if statement to the total number of items your form should return.

[memprog]

phpfr33k,

I am giving the user the option of either entering 1, multiple or, no values. Will the empty function still work?

[memprog]

I neglected to mention something, I have this in a while loop so basically it looks like this....

search.php
-------------
Order Info ID:
<INPUT type="text" maxLength=100 size=17 name=orderId>
TELEPHONE:
<INPUT type="text" maxLength=20 size=14 name=phone>
PON:
<INPUT type="text" maxLength=100 size=27 name=ponValue>


results.php
----------------
<?
$pon = $_POST['ponValue'];
$telephone = $_POST['phone'];
$order = $_POST['orderID'];

$select = "select * from orderinfo and day <>'' " ;
$whereClause = "";
$res = $db_connection->Execute($select);


?>

<?
//this is what I don't know how to do.
//I need to add the where clause to the sql statement if the user
//..has entered a value into any of the text fields

while(!res->EOF)
{

if($pon !="")
{
whereClause = " and pon = ".'$pon';
echo $res->Fields['$pon']->Value;
}
}
?>


So where would I add the code if it's in a while loop?