Peppe,
I have a search form that has multiple fields for the user to enter data as
well as a couple other search limiting radio buttons. Here is the code I use
to handle the situation:
//SAMPLE CODE STARTS HERE
/*****
METADATA SEARCH CODE
*****/
if($_POST["searchType"] == "metadata"){
if($_POST["searchRule"] == "or"){
$cond = "OR";
}
else $cond = "AND";
$sql = "SELECT DISTINCT contentID, partNum, title, subtitle,
printdate, statusID, typeID FROM docmeta ";
//conditional statement to determine whether WHERE clause is
needed (check if there are criteria upon which to perform the WHERE clause)
if($_POST["partnum"] != "" || $_POST["title"] != "" ||
$_POST["subtitle"] != "" || $_POST["printdate"] != "" || $_POST["status"] !=
"" || $_POST["type"] != ""){
$sql .= "WHERE ";
if($_POST["partnum"] != ""){
$sql .= "partnum LIKE
\"".addslashes($_POST["partnum"])."\"";
if($_POST["title"] != "" ||
$_POST["subtitle"] != "" || $_POST["printdate"] != "" || $_POST["status"] !=
"" || $_POST["type"] != ""){
$sql .= " $cond ";
}
}
if($_POST["title"] != ""){
$sql .= "title LIKE
\"".addslashes($_POST["title"])."\"";
if($_POST["subtitle"] != "" ||
$_POST["printdate"] != "" || $_POST["status"] != "" || $_POST["type"] !=
""){
$sql .= " $cond ";
}
}
if($_POST["subtitle"] != ""){
$sql .= "subtitle LIKE
\"".addslashes($_POST["subtitle"])."\"";
if($_POST["printdate"] != "" ||
$_POST["status"] != "" || $_POST["type"] != ""){
$sql .= " $cond ";
}
}
if($_POST["printdate"] != ""){
$sql .= "printdate LIKE
\"".addslashes($_POST["printdate"])."\"";
if($_POST["status"] != "" || $_POST["type"]
!= ""){
$sql .= " $cond ";
}
}
if($_POST["status"] != ""){
$sql .= "statusID LIKE
\"".$_POST["status"]."\"";
if($_POST["type"] != ""){
$sql .= " $cond ";
}
}
if($_POST["type"] != ""){
$sql .= "typeID LIKE \"".$_POST["type"]."\"
";
}
}
$sql .= " ORDER BY ".$_POST["sortRule"]."";
if($_POST["displayRule"] == "desc"){
$sql .= " DESC";
}
//echo $sql;
$searchResults = mysql_query($sql)
or die(mysql_error());
//SAMPLE CODE ENDS HERE
This code works as-is in my application, but you will probably need to
modify parts of it - especially the field names. However, the IF logic with
the $sql .= appends are what really do the work here. Might not be the
prettiest, but it works without fail. Hope it helps.
BTW, you'll probably also benefit from creating indexes on the columns the
query will search.
Rich
> -----Original Message-----
> From: peppe [mailto:peppe1001@hotmail.com]
> Sent: Tuesday, May 13, 2003 6:33 AM
> To: php-db@lists.php.net
> Subject: search mysql
>
>
> I have a search form containing multiple criteria that the
> user can choose
> to search with. For example, the user can search by keywoords, gender,
> temple size, bridge size, etc., or ANY combination.
>
> I'm using PHP/MySQL and having a problem writing the query to
> search the
> proper columns in the table based on which fields or
> combination of fields
> the user filled in on the search form.
> Any help or pointers to tutorials would be appreciated.
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
