Varying my mysql queries based off user input

[LanceT]

So I'm trying to basically trying to make an "advanced search" function in PHP/mysql that will allow users to search by a number of different options like search by zipcode, username, gender, etc.

 

Now my question is how do I vary my mysql query so that it searches for all these things based on whatever the user inputs?

 

For example, the user might need to search zipcode and username but not gender, but in another case, might need to search username and gender, but not zipcode. Obviously I could just do some if/else statements, but that would be increasingly more difficult as there are more fields.

 

What can I do?

[ignace]

Or you write them using an if/else structure or using an OO-structure:

 

class QueryObject
{
    private $where = array();
    
    public function reset() { $this->where = array(); }
    
    public function zipCodeIs($zipCode) {
        $this->where[] = 'zipcode = ' . $zipCode;
    }
    public function usernameIs($username) {
        $this->where[] = 'username = ' . $username;
    }
    public function genderIs($gender) {
        $this->where[] = 'gender = ' . $gender;
    }
    
    public function execute() { return $this->adapter->query('SELECT ' . $this->columns . ' FROM ' . $this->table . ' WHERE ' . $this->_generateWhereClause()); }
    protected function _generateWhereClause() { return implode(' AND ', $this->where); }
}

 

The QueryObject is a pattern defined by Martin Fowler, this is a very loosely based implementation of that pattern as it doesn't abstract the actual query. Doctrine implements this perfectly where you can use your models properties in place-of your column names also called DQL (Doctrine Query Language):

 

SELECT Users.* FROM Users

 

The advantage is that your actual column names can be changed without having to alter the code.

[mckgr]

If you'd prefer not to use an OO method, the concept can be done procedurally. Create an array of where clauses, and the use implode() to put " AND " between them all. Loop through your POST variables and use !empty() to determine whether it needs to be added to the array of where clauses.

[LanceT]

can you give me some sample code with the implode() method, that seems a bit easier for me to understand since oo is a bit complicated. I've never used implode() before I'm just a bit confused about how to implement it.

[LanceT]

can you give me some sample code with the implode() method, that seems a bit easier for me to understand since oo is a bit complicated. I've never used implode() before I'm just a bit confused about how to implement it.

 

Nvm, I figured it out. Thanks a lot this was helpful.