-1

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?

Rohcana
  • 359
  • 4
  • 13
Andurit
  • 5,612
  • 14
  • 69
  • 121

1 Answers1

3

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
KaeL
  • 3,639
  • 2
  • 28
  • 56
  • Thank you KaeL, this is really helpfull so I mark it as "correct" how ever is there aa way how to check this in PHP because I see here some limits like if user add spaces to form. – Andurit Aug 04 '15 at 08:24
  • If you are referring to the white spaces, you can trim the leading and trailing spaces of a string via `javascript`, not sure with `php`. If you need to remove white spaces in between the characters, one good option is to use `match` and `replace` via `regular expressions`, using `javascript` as well. – KaeL Aug 04 '15 at 09:19