Creating a dynamic search query with PHP and MySQL

Question

I'm trying to create a dynamic search query, based on the user input.

Requirements:

• A user could fill in none, some, or all fields.
• The query searches in a table for a record that matches all the requirements.

Now I have done my research, and I found out multiple ways on doing this. But none of them work, and if they do, they are far from practical.

Attempt:

At the moment I'm creating a query like this:

SELECT * 
FROM assignments 
WHERE (id = $id OR id = '') 
  AND (field1 = $field1 OR field1 = '')

This query works, but only if you fill in all the fields.

I got this from a stackoverflow article, that I can't find anymore, that said:

If the user has filled in an input field it will check the first rule "id = $input" and if the user hasn't specified any input it will check for "id = '' " and when it checks for that, it will just return everything. Because it escapes the empty search rule.

But as you might already know, it doesnt work..

How would you suggest me to approach this?

Answer 1

Try getting all of the post vars and looping through them to see if they are valid, and then build your query

<?php
$id = $_POST[id];
$field1 = $_POST[field1];
$field2 = $_POST[field2];
$field3 = $_POST[field3];

$whereArr = array();
if($id != "") $whereArr[] = "id = {$id}";
if($field1 != "") $whereArr[] = "field1 = {$field1}";
if($field2 != "") $whereArr[] = "field2 = {$field2}";
if($field3 != "") $whereArr[] = "field3 = {$field3}";

$whereStr = implode(" AND ", $whereArr);

$query = "Select * from assignments WHERE {$whereStr}";

Something like that should handle what you need

Answer 2

You should start with a string like yours up to the WHERE statement, then after that you loop through all the fields the user wants to search with and add them to an array, then use the PHP function "implode" to glue the fields together with an AND statement as "glue".

Now add on the glued string to the startquery and voila!

I'd give example but on phone atm!