PHP - MYSQL fulltext search

Question

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"; 
    }

Tom van der Woerdt · Accepted Answer · 2011-12-08 16:59:40Z

1

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.

answered Dec 8, 2011 at 16:59

Tom van der Woerdt

30.1k7 gold badges76 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Over a year ago

Ah brilliant, thank you very much. I'll accept your answer as soon as I can :)

Manse · Accepted Answer · 2011-12-08 17:01:12Z

1

$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 ?

answered Dec 8, 2011 at 17:01

Manse

38.1k11 gold badges88 silver badges112 bronze badges

Collectives™ on Stack Overflow

PHP - MYSQL fulltext search

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related