PHP - How to build multiple input search

Question

I have a form with around 7 inputs and I want to let the user search according to the criteria he choosea.

So for example: Inputs name can be:

• name
• age
• gender
• registration date

But my user wants to pick just first 3, so SQL query will look like:

SELECT * FROM table WHERE name='.$name.' AND age= '.$age.' AND gender = '.$gender.'

Cool this work, but I am facing the problem of how to BUILD always different SQL query based on user input using MVC architecture.

My Idea was like:

// Syntax is from Nette Framework so please don't care about it, I basicly need logic for it in MVC

public function findMatch($name= NULL, $age= NULL, $gender = NULL)
{
    $selection = $this->database->table('table');
    if ($name) {
        $selection = $selection->where('name', $name);
    }
    if ($age) {
        $selection = $selection->where('age', $age);
    }
    if ($gender) {
        $selection = $selection->where('gender', $gender);
    }
    return $selection;
}

But this helps only with "OR" selection not with "AND". Please can someone help me figure out how to solve this?

Answer 1

If you are considering a single SQL SELECT statement, you might want to try:

SELECT *
    FROM Table T
    WHERE (@name IS NULL OR T.Name LIKE '%' + @name + '%')
        AND (@age IS NULL OR T.Age = @age)
        ...

This will search base on the given criteria. Basically, on the WHERE clause, it tells you that:

if @name is NULL

• It will display all records with any name
• Else, it will return values with similar name only