Posted by Jochem Maas on 03/13/05 22:30

AN@S wrote:
> Hello,
> I have a query that I'm unable to work it out, each time I run it I get
> some error, I need your help.
>
> I have a webtemplates table which contains some basic information about
> each template (author, price, description, ...etc). I created a search
> form that a user can use to search by type(by description, author,
> ..etc) he chooses the type he want (radio button) then types his
> searchterm, and the following query works very fine:
>
> $query = "SELECT * FROM templates where ".$_POST[searchtype]." LIKE
> '%".$_POST[searchterm]."%'";
>
> But now I need the search to be more advanced, the user may enter a
> price range to find results within a range of two numbers, I have added
> to the form two textfields the first for the low price ($lprice) the
> second for the high price ($hprice).. the 'price' field is located in
> the same table (templates).
>
> Now I want to add to the query to make it does the following:
>
> - if the user doesn't type any price range, the above query will be
> executed.
>
> - if he typed a price range, it'll be taken into consideration with the
> within the cretirea he wanted.
>
> for example, he may search for templates authored by John and their
> prices are between 30 and 50.
>
> I'm a php newbie, so I got error messages each time I try to extend my
> simple query, I hope I can find some assistance here :)

basically no want to add a dynamic number of search criteria (bits of the WHERE
clause) to a base query. lets assume that you just want to AND the search criteria.

<?php

$qry = 'SELECT * FROM templates'; // better to specify the fields you want.
$where = array();

// I leave the error checking out for simplicity...
// you are checking/sanitizing the user submitted values before using them I hope.
// e.g. that searchtype is a field, searchterm has quotes/etc escaped, lprice &
// hprice are actually numeric, greater than zero, etc.

if (isset($_POST['searchtype']) && isset($_POST['searchterm'])) {
$where[] = '{$_POST['searchtype']} LIKE %{$_POST['searchterm']}%'";
}

if (isset($_POST['lprice'])) {
// the following expression is a ternary operator - alternative form of
// an 'if' statement
$where[] = isset($_POST['hprice'])
? "price >= $_POST['lprice'] AND price <= $_POST['hprice']";
: "price >= $_POST['lprice']";
}

if (count($where)) {
$qry .= ' WHERE '.join(' AND ',$where);
}

?>

play with this code. use echo, print(), print_r(), var_dump() etc to find
out what it does. look up functions you don't know (e.g. possibly join())
in the manual.... then if you get stuck, by all means ask questions :-)

have fun.

>
> Thank You,
>

[Back to original message]