CODEWITHSUNDEEP
phpmysqlfull-text-search
Adam
Adam

Reputation: 339

PHP - MYSQL fulltext search

I have a MYSQL full text search, matching against items in my database, which works great.

$select = "SELECT * from menu WHERE MATCH(item) AGAINST('$this->food') ";

From there I refine the query dependent on what the user wants to filter by. I have 2 filters, vegetarian and breadtype

   if(isset($this->vegetarian)) { 
      echo $select .= " AND vegetarian='$this->vegetarian'"; 
    }

   if(isset($this->bread)) { 
       echo $select .= " AND bread='$this->bread'";
   }

Which appends the fulltext statement, now this works perfectly, although my issue is that if the user doesn't search anything (empty string) I want it to returns all results in the database.

 if(empty($this->food)) { $select = "SELECT * from menu"; }

Which now means I can't append the $select statement with the above if statements, and would have to use a WHERE statement instead - but that would mean adding 2 extra if statements to compensate for that. Is there anyway for MYSQL to do it instead.

 if(isset($this->vegetarian) && empty($this->food)) { 
      echo $select .= " WHERE vegetarian=$this->vegetarian"; 
    }

Upvotes: 2

Views: 633

Answers (2)

Manse
Manse

Reputation: 38147

$select = "SELECT * from menu WHERE MATCH(item) AGAINST('$this->food') ";
if(empty($this->food)) {
  $select = "SELECT * from menu WHERE 1=1"; // if food is empty we overwrite the $select variable
} else {
    if(isset($this->vegetarian)) { 
      $select .= " AND vegetarian='$this->vegetarian'"; 
    }

    if(isset($this->bread)) { 
      $select .= " AND bread='$this->bread'";
    }
}

Or am i missing something ?

Upvotes: 1

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29985

You can simply use WHERE 1=1 and then append the rest. MySQL will simply ignore this part if there's no additional ANDs or ORs.

Upvotes: 1

Related Questions