SQL statement based on user input

[smirch]

<i>I am trying to query my DB based on which tickboxes a user selects, this then builds a dynamic WHERE clause, however I am stumped on how to do this SQL statement!!</i>

The form has several tickboxes such as :
"<input type="checkbox" name="rFeat[]" value="1">"
"<input type="checkbox" name="rFeat[]" value="2">"
"<input type="checkbox" name="rFeat[]" value="3">"
"<input type="checkbox" name="rFeat[]" value="4">"

<i>on the receiving page i want to create a SQL statement which performs the following logic:</i>

"SELECT * FROM table FEATURES WHERE rFeat = 1 AND rFeat=2 AND rFeat = 3"

<I>What would be the correct SQL syntax for this query??</i>

<i>thanks in advance!</i>

[Anon]

Try this querry, i think it will resolve your problem :
SELECT * FROM table FEATURES WHERE rFeat In (Value1,Value2,Value3)

Don't do a sub-querry ( In (select ...)
Because it's noy yet supported by mysql

good luck

[smirch]

<PRE>
thanks for your help, this has got me a little closer!
....however, I only want a result to be returned if ALL
the criteria is fullfiled.

So if I explain my objective in more detail maybe this
will help?!?

<B>I have a search form where users can find cars for sale
based on its features. Therefore if a user clicks on the
checkboxes 1)electric windows 2)central locking 3)power steering
... it will return the list of cars that ONLY have all these features.
</b>

<BR>

tFeatLink

| feat_ref | feat_id | car_id |

| 1 | 1 | 201 |
| 2 | 3 | 201 |
| 3 | 5 | 201 |
| 4 | 1 | 202 |
| 5 | 4 | 202 |
-------------------------------------
<BR>

'tFeatureDesc'

| feat_id | feat_detail|

| 1 | e/w |
| 2 | c/l |
| 3 | PAS |
| 4 | Alloys |

| 5 | FSH |

<BR>

'tCarforsale' -

| car_id | car_make | car_reg |

| 201 | HONDA | A1 ABC |
| 202 | BMW | A2 XYZ |
-------------------------------------
<BR>

<P>
my form has several checkboxes so a user can find a car based on the
'features'
<form name ="adv search" action = "results.php">
"<td><input type="checkbox" name="rFeat[]" value="1">e/w </td>"
"<td><input type="checkbox" name="rFeat[]" value="2">c/l </td>"
"<td><input type="checkbox" name="rFeat[]" value="3">PAS </td>"
"<td><input type="checkbox" name="rFeat[]" value="4">Alloys</td>"
"<td><input type="checkbox" name="rFeat[]" value="5">FSH </td>"
</form>
<P>

results.php has the following processing to
handle the user input to build a dynamic SQL query >

if ($rFeat != "") {

<U>$partSQL =" SELECT * SOMETHING WHERE ";</U>

$vFeatCnt = count($rFeat);

//loop for eash feature that has been selected

for ($vI = 0; $vI < $vFeatCnt; $vI++) {
if ($rFeat[$vI] != "") {
// build a dynamic WHERE clause
<u> $filterFeatSQL .= " * = $rFeat[$vI] *";</u>
}
}

<u>$theSQLFeatures= "$partSQL $filterFeatSQL";</u>

$resultSet2=mysql_query($theSQLFeatures, $inDbConn2);

while ($theResult2 = mysql_fetch_row($resultSet2)) {
echo " car_reg= * car_id = has all the feaures u selected";

} 

}

<B>
hmmmm, so can anyone help me fill in the '******'
i.e. what is the syntax of $theSQLFeatures ?
</b>

So in an ideal situation if a user selects "E/W(1 in 'tFeatureDesc'), PAS(3 in 'tFeatureDesc'), FSH(5 in 'tFeatureDesc')" the results page would display the details of car_id = 201 (according to table 'tFeatLink') AND if a user searches for a car with "e/w(1 in 'tFeatureDesc') and alloys(4 in 'tFeatureDesc')" it will only return car_id 202!

Thanks in advance, coz as u can probably tell ..I am desperate ...anybody..umm..err.. pls help !!
</pre>