dynamic SQL query string

pblamire

I want to have a search form to query a mySql database, the problem is I want to form the WHERE part of the query depending on what criteria the user enters into the form (if any).

I was thinking of inserting a variable into the query string, and the variable would only contain something if the user had POSTed the data from the form.

Is this stupid? Is there a tried and tested way? It's a common problem.

Thanks

Paul

kennethl

Here's some code I wrote to build SQL using keywords and an All or Any select box from a form.
Hope this is useful to you.

if (isset($keywords)) {
/ break up keywords separated by blank space /
$keywordsarray = explode(" ",$keywords);
$query = "SELECT article FROM articles";
/ find all articles if no keywords, build WHERE clause if there are /
if ($keywords != "")
$query = $query." WHERE (";
// set DB fields you want to search in
$fields = array("field1","field2","field3");
$i = 0;
/ loop through keywords, building WHERE clause /
while ($i < count($keywordsarray)) {
$keyword = current($keywordsarray);
next($keywordsarray);
$j = 0;
$query = $query."(";
while ($j < count($fields)) {
$field = current($fields);
next($fields);
$query = $query."$field LIKE '%$keyword%'";
if (($j + 1) == count($fields)) {
$query = $query.")";
} else {
$query = $query." OR ";
}
$j++;
}
reset($fields);
if (($i + 1) != count($keywordsarray)) {
/ find articles with all keywords or any keywords, depending on form field /
if ($andor == "OR") {
$query = $query." OR ";
} else {
$query = $query." AND ";
}
}
$i++;
}
if ($keywords != "")
$query = $query.")";
}

Anon

Well there is no need to add any extra fields to your form, since you can just test for the existance of your form variables in $HTTP_POST_VARS. Then use what you find there to generate your where clause, or if nothing exists use a standard one.

Anon

Hi,

I have a form with 10 checkboxes. Visitors can check from 1 to 10 of them. Based on the values they pass on I'd like to build a search query on my DB.

id e_mail section

1 bb@bb.com pijamas

2 cc@cc.com pants

3 gg@gg.com t-shirts

I've tried to use the above mentioned code, but the search displays either 1 e_mail for 1 checkbox checked or 0 if I check more than one. Is there a way to solve my problem?

Witold

Anon

What are you trying to do exactly, and what does the SQL that your script produces look like?
If you're searching the three records you showed above and using "and" to match multiple selections on your form, you should get an empty result set since all your fields contain different values. Just making a guess about what you're trying to do, but try using "or" instead of "and" and see what happens.

Kenneth