I need your help with my website search functionality. I'm developing a members area wherein users can search other registered users based on certain criteria, or combination of criteria.
My problem now is how to build a dynamic mysql query to suit the need of each combination of search criteria, where the number of criteria is variable.
Normally, I can write with a pre-determined set of criteria using
WHERE param1 = '$param1'
AND param2 = '$param2'
AND param3 = '$param3'
How do I solve this problem?
If the issue is that you don't know which of the criteria the user will pick, but want to return results for "blank" criteria, you can use the following:
$criteria_1 = $_POST['criteria_1'];
$criteria_2 = $_POST['criteria_2'];
$criteria_3 = $_POST['criteria_3'];
if(!$criteria_1 && !$criteria_2 && !$criteria_1) {
echo "You must select at least one criteria!";
} else {
// Run query mentioned below and return results.
}
THe query would then look like:
SELECT * from mytable
WHERE
(criteria1 = '$criteria_1' OR '$criteria_1' = '') AND
(criteria2 = '$criteria_2' OR '$criteria_2' = '') AND
(criteria3 = '$criteria_3' OR '$criteria_3' = '')
This will treat any blank (non-selected) parameters as blank and ignore them. Be aware that with the above, if no criteria are given, it will return all results.
Another way to write the above is:
SELECT * from mytable
WHERE
criteria1 IN ('$criteria_1', '') AND
criteria2 IN ('$criteria_2', '') AND
criteria3 IN ('$criteria_3', '')
Again, allowing for no entry at all to return all criteria1 results.
Here's a generic example of what you're asking:
$query = "SELECT * FROM mytable";
if ($_POST['name'] == "Jack") {
$query .= " WHERE name = 'Jack'";
}
if ($_POST['name'] == "Bob") {
$query .= " WHERE name = 'Bob'";
}
if ($_POST['state'] != "") {
$query .= " AND state = '" . mysql_real_escape_string($state) . "'";
}
//So now, in total, your query might look like this
//"SELECT * FROM mytable WHERE name = 'Bob' AND state = '$state'"
$result = mysql_query($query);
You just add to your $query string with if statements, then execute the query once you've checked all $_POST variables.
use your scripting language (php) to loop over the inputs...
then have a structure like this:
WHERE 1=1
then add your
AND paramx = '$px'
to it...
$criteria = array();
//Populate your criteria and parameter arrays with input from the web page here
...
// $criteria should now have stuff in it
$sql = "SELECT * FROM mytable ";//Or whatever your sql query is
$count = 0;
foreach ($criteria as $key => $parameter) {
if ($count == 0) {
$sql = $sql."WHERE ".$key." = ".$parameter;
} else {
$sql = $sql."AND ".$key." = ".$parameter;
}
$count++;
}
That said, this is highly vulnerable to sql injection attack. Try using PHP PDO
An option is also to build the query from php/asp or whatever you working with, like this
$param1 = (isset($searchParam1) ? "param1 = $searchParam2" : "1");
$param2 = (isset($searchParam2) ? "param2 = $searchParam2" : "1");
$param3 = (isset($searchParam3) ? "param3 = $searchParam3" : "1");
and the query would be like
SELECT ... WHERE $param1 $param2 $param3
I've seen queries like this, so that if you don't want to put in a value for a particular column, you pass in NULL for that column:
SELECT *
FROM users
WHERE param1 = :param1
UNION
SELECT *
FROM users
WHERE param2 = :param2
UNION
SELECT *
FROM users
WHERE param3 = :param3
This assumes that you'll have each column indexed and you're performing Boolean AND searches (and using PDO).
would like to share this code to build dynamic mysql query with PHP Thx & regards
$vocabulary = (($page == "vocabulary") ? "image_name <> ''" : "");
$groupcat = (($group != "") ? "group = $group" : "");
$var = array($vocabulary, $groupcat);
$counter = "0";
$param = "";
for ($i=0;$i<count($var);$i++)
{
if ($counter == "0" && $var[$i] != "" ) $param = "WHERE ";
if ($counter > "0" && $var[$i] != "" ) $param = " AND ";
if ($param != "")
{
$condition .= $param . $var[$i];
$param="";
$counter++;
}
}
echo "Condition : ". $condition;