CODEWITHSUNDEEP
phpmysql
Dan
Dan

Reputation: 997

Passing multiple $_POST fields through MySQL search query

I have a search form with a possible 15 or so fields, however not all are required to carry out a search, for instance;

a user might search for a registered user in 'London' who works in 'Finance' but leave all other fields blank, such as $availability or $salary etc, so $_POST data may look something like:

$location = $_POST['location'];    // Value - London
$sector = $_POST['sector'];        // Value - Finance
$available = $_POST['available'];  // Value - Any
$salary = $_POST['salary'];        // Value - Any

Bearing in mind I may have another 12 or so 'Any' values from other fields, what is the best way to query the database (PHP/MySQL) to return results without looping through what would probably be dozens of queries.

To try and be a bit clearer, what i'd like is a query which would work something like (deliberate pseudo code):

SELECT * FROM table where location = 'location' AND if($availability !='Any') { available = '$available' } etc etc

Is something like this possible?

Or can I create a single string of all $_POST fields that !='Any' and then carry out a search on a row that contains all the words in the string (which I think would work in theory)?

I hope this makes sense to someone and you can point me in the right direction.

P.S. All $_POST is escaped and secured before interacting with database, just not included here :)

Upvotes: 0

Views: 959

Answers (3)

Dan
Dan

Reputation: 997

Thanks to those who offered answers, however I used the suggested answer found in the link above my question as it was clearer to me. Sample code pasted below FYI:

$tmp = "where ";
if($A and $A!="any" and $A!="not used")
$tmp .= "row1 = '".$A."'";
if($B and $B!="any" and $B!="not used")
$tmp .= "AND row2 = '".$B. "'";
if($C and $C!="any" and $C!="not used")
$tmp .= "AND row3 = '".$C."'";
$db_q = "Select * from table $tmp";

Thanks again, don't know where I'd be without SO.

Upvotes: 0

Hydra IO
Hydra IO

Reputation: 1557

Could you for argument sake collect all of the $_POST into a foreach($key=>$val) and then run the key through a switch or if statments that appends "AND x=x " to the statement?

Something like:

$sql = "SELECT * FROM table WHERE required='required'";
foreach($_POST as $key=>$val){
if(!empty($val)){ $sql .= " AND ".$key."='".$val"'"; }
}

Not sure if that works but in theory that is what i thought of first.

Upvotes: 1

MosheK
MosheK

Reputation: 1196

Try this:

$sql = "SELECT * FROM table where 1 ";
foreach ($_POST as $key => $post) {
    if ($post != 'Any') {
        $sql .= " AND $key = '$post' ";
    }
}

// now you can run $sql against the database

Upvotes: 2

Related Questions