PHP MySQL Where clause

What function do I use to evaluate?

[SundancerKid]

I need help with the where clause (What function do I use to evaluate)

The drop down box contains s_item
=PartNumber value asphericallenses.partnumber
=Diameter1 value asphericallenses.diameter1 +/-5%
=Diameter2 value asphericallenses.diameter2 +/-5%

Text Box conatins s_word

$a_s_item = s_Item
$a_s_word = s_word

// Works fine by it's self with asphericallenses.partnumber

Where $a_s_item like '%$a_s_word%'

Results:
[localhost...]

// Works fine by it's self with asphericallenses.diameter1 or asphericallenses.diameter2 Searches 5% variance + or -

Where $a_s_item between ((($a_s_word * .05) - $a_s_word) * -1) and (($a_s_word * .05) + $a_s_word)

Results:
[localhost...]

I would like a evaluation statement to decide which routine to pass to the WHERE clause. can I use an IF function? or What do I use?

Am I correct? IF(Evaluate, True, False)

Where if($a_s_item='asphericallenses.partnumber', $a_s_item like '%$a_s_word%', $a_s_item between ((($a_s_word * .05) - $a_s_word) * -1) and (($a_s_word * .05) + $a_s_word))

SQL Routine MYSQL & PHP:

$w_sqlstr = "SELECT asphericallenses.PartNumber, asphericallenses.Diameter1, asphericallenses.Diameter2, asphericallenses.FocalLength, asphericallenses.SphericalRadius, asphericallenses.CenterThickness, asphericallenses.Material FROM asphericallenses WHERE (if($a_s_item='asphericallenses.partnumber', $a_s_item like '%$a_s_word%', $a_s_item between ((($a_s_word * .05) - $a_s_word) * -1) and (($a_s_word * .05) + $a_s_word)))";

[coopster]

Welcome to WebmasterWorld, SundancerKid!

You are correct about how the IF CONTROL FLOW functions, but I'm not sure you are getting the result set returned that you are expecting.

Currently, you are saying,
"select this fieldlist from this database WHERE..."

if (the drop-down-box item is equal to the item in the current row) { 
 return rows where the drop-down-box item is like the text-box word 
} else { 
 return rows where the drop-down-box item is between 
  text-box word -5% variance 
   and 
  text-box word +5% variance 
} 

...is that what you want?

[SundancerKid]

No I want to know how to control the Where clause? If the PartNumber is selected in the drop down box then I want to have the following show up in the Where statement
$a_s_item like '%$a_s_word%'

Else then I want the following to show up in the Where Clause

$a_s_item between ((($a_s_word * .05) - $a_s_word) * -1) and (($a_s_word * .05) + $a_s_word)

Thanks in advance

[coopster]

Ah, gotcha. Then, no, don't use an IF control flow in the query, use it in your PHP logic:

$where_clause = 'WHERE '; 
if (isset($_POST['s_item'])) { 
 $where_clause = "$a_s_item like '%$a_s_word%'"; 
} else { 
 $where_clause = "$a_s_item between ((($a_s_word * .05) - $a_s_word) * -1) 
 and (($a_s_word * .05) + $a_s_word)"; 
} 
$sql = "SELECT ... FROM ... $where_clause..."; 

The if statement is checking to see if the form variable for the select box was POSTed. If it was, we know they selected an item (assuming your select box is returning a single value, not multiple).

[SundancerKid]

I appreciate your information regarding the IF statement syntax. I am using Namo Webeditor 5.5 and everythis is generated for me . They are making it impossible to change their code see Source below:

Where to I put the routine
if (isset($s_item)) {
$where_clause = "\$a_s_item like '%\$a_s_word%'"; }
else { $where_clause = "\$a_s_item between ((($\a_s_word * .05) - \$a_s_word) * -1) and ((\$a_s_word * .05) + \$a_s_word)";
}echo $where_clause;

[edited by: jatar_k at 4:41 pm (utc) on Feb. 20, 2004]
[edit reason] too much code see PHP Charter [/edit]

[coopster]

It needs to be within your php parsing tags. I would probably put it right before the sql statement in which it will be used:

// right here! 
$w_sqlstr = "SELECT .... 

You are going to have to change the WHERE clause in your statement there too. Just replace it with your $where_clause variable.