build WHERE clause on demand

Hi I have a situation where the user will choose a subset of a list of items
and display by submitting request. For example I have apple, orange and
pear. A user may select apple and pear, or orange and pear or all etc.

How to I contruct the WHERE clause?

SELECT fruit_name, price FROM fruit_table WHERE fruit_name = .....

Can you help?

Thanks!

Shawn

Re: build WHERE clause on demand

//collect vars&values :D
foreach ( $_REQUEST as $key => $value ) {
$$key = $value;

if ($key == "fruit_name") {
$fruit_name0 = ("fruit_name like '$value'");

$sql = ("SELECT * FROM fruit_table where $fruit_name0");

$query = mysql_query($sql);

while ( $row = mysql_fetch_array($query) ) {

$fruit_name = $row["fruit_name"];
$price = $row["price"];

echo("$fruit_name = $price
");//if U wanth to echo selected data
:)
}
}
}
?>

Give name of input or drop down list field like 'fruit_name', and
script will select fruits and price and print it.

Re: build WHERE clause on demand

> //collect vars&values :D
>foreach ( $_REQUEST as $key => $value ) {
> $$key = $value;
>
>if ($key == "fruit_name") {

This doesn't make any sense since $_REQUEST can only have one value
with a key of "fruit_name".

> $fruit_name0 = ("fruit_name like '$value'");
>
> $sql = ("SELECT * FROM fruit_table where $fruit_name0");

I could be wrong, but I think the OP wanted the user input to come from
a well-defined list of fruits rather than use a pattern match.

I'm not sure how you have things setup for the user to choose, so let's
suppose it was a multiple-select list called "fruit." On the page that
processes the form, this will show up as $_REQUEST['fruit'] which is
itself an array (with an element for each fruit the user selected):

$_REQUEST['fruit'] = array('apple', 'pear', 'orange');

So now we need to get them into a query. With lists of items like this
I prefer to use an in-cluase, mainly because it's really easy to come
up the query:

$fruits = $_REQUEST['fruit'];
//Need to put the quotes around them
foreach($fruits as $i => $val)
$fruits[$i] = "'" . $val . "'";

$inList = implode(', ', $_REQUEST['fruit']));
//$inList is now a string that looks like: "'apple', 'pear', 'orange'"
$query = "select * from fruit_table where fruit_name in ($inList)";

Re: build WHERE clause on demand

ZeldorBlat U R right :P
I didn't think about that. Then just del 'if ....'

//collect vars&values :-D
foreach ( $_REQUEST as $key => $value ) {
$$key = $value;

$fruit_name0 = ("fruit_name like '$value'");

$sql = ("SELECT * FROM fruit_table where $fruit_name0");

$query = mysql_query($sql);

while ( $row = mysql_fetch_array($query) ) {

$fruit_name = $row["fruit_name"];
$price = $row["price"];

echo("$fruit_name = $price
");//if U wanth to echo selected data
:)
}
}
?>

Re: build WHERE clause on demand

and give your input fields various names

Re: build WHERE clause on demand

Post removed (X-No-Archive: yes)

Re: build WHERE clause on demand

"Kiza" wrote in message
news:1131536190.540987.216960@g49g2000cwa.googlegroups.com.. .
> and give your input fields various names
>

Thanks to all who replied.

....WHERE fruit IN ("apple", "pear");

is what I was the syntax I was looking for. The way to built the
"name1","name2" separated by comma on the fly was what I was trying to find
out.

I actually found a perfect solution using implode at php site:
http://ca3.php.net/implode the solution by "adrian at foeder doe de" was
just what I was looking for.

Appreciate all your response!

Shawn

Re: build WHERE clause on demand

> ...WHERE fruit IN ("apple", "pear");
>
> is what I was the syntax I was looking for. The way to built the
> "name1","name2" separated by comma on the fly was what I was trying to find
> out.
>
> I actually found a perfect solution using implode at php site:
> http://ca3.php.net/implode the solution by "adrian at foeder doe de" was
> just what I was looking for.


Do NOT use double-quotes to quote strings in SQL statements. It works in
some SQL engines but in most of them it's NOT the proper way. Use
single-quote instead, which is (or should be) valid in all SQL engines
because it comes from SQL standards. So like this:

....WHERE fruit IN ('apple', 'pear')


and NOT like this:

....WHERE fruit IN ("apple", "pear")


Also remember that this one:

....WHERE fruit IN ()

will NOT be accepted by most of SQL engines, so you'll have to
treat this case (when nothing was selected by user) differently.


Hilarion