Search feature with multiple criteria - PHP/MySQL

Question

How can i create a search page with multiple criteria where at least a criteria should be checked.

Table Structure

ID [pk]
Name
Sex
Location

I want to create a search form where user will be able to search by name or by name,sex or by name,sex,location or any such combination among [name,sex,location]

How to design the query ?

Edit
i am not asking for checking atleast a single option has value [js validation], i am asking for the query !
I'll use mysqli prepared statement !

You have two separate questions: "how to require at least one field on a form before submission" (javascript validation) and "how to write a php script to build an SQL statement using dynamic POST values as search criteria". Consider posting two separate questions, one for each of these. — AJ.
– AJ., Commented May 17, 2011 at 19:26
@AJ, nope, i'm looking for only to build an SQL statement using dynamic POST values as search criteria — Sourav
– Sourav, Commented May 17, 2011 at 19:28

Crashspeeder · Accepted Answer · 2011-05-17 19:49:21Z

2

You can check to see if the post for a particular field is empty, if it's not then append the corresponding WHERE clause to the query. Something in the form of the following:

$mysqli = new mysqli(...);
$sql = 'SELECT * FROM table WHERE ';
$where = array();
$values = array();
$types = '';

if (!empty($_POST['name'])) {
    $where[] = 'name = ?';
    $values[] = $_POST['name'];
    $types .= 's';
}

if (!empty($_POST['sex'])) {
    $where[] = 'sex = ?';
    $values[] = $_POST['sex'];
    $types .= 's';
}
...
$sql .= implode(' AND ',$where);
$values = array_unshift($values, $types);

$statement = $mysqli->prepare($sql);
call_user_func_array(array($statement, 'bind_param'), $values);
...

edited May 17, 2011 at 19:49

answered May 17, 2011 at 19:30

Crashspeeder

4,3112 gold badges18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crashspeeder Over a year ago

@Sourav I modified it to use mysqli, it's untested though. Make sure each conditional adds the proper type (integer, string, etc.) and in theory it should bind everything.

Collectives™ on Stack Overflow

Search feature with multiple criteria - PHP/MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related