is there an easier method to write search class than this code that i think it isn't

[mikha]

Hi all
i have written this search class, i want user to be able to search by title or category or company , or any
combination of these fields, e.g if user puts category and title, get search by those only, or if user puts
category and date he gets results be these fields only and so on, i have used in my class several if and elseif statments with all of possibilties, i just want to be sure if this is the optimal way, i think i can some thing more optimum than this, here is my class

    function jobs_by_title_category_company($job_title, $category_id
                                            , $company_name, $date, $salary_satrt, $salary_end)
    {
        $job_title         =     mysql_real_escape_string($this->job_title);
        $category_id     =     mysql_real_escape_string($this->category_id);
        $company_name     =     mysql_real_escape_string($this->company_name);
        $date             =     mysql_real_escape_string($this->date);
        $salary_satrt     =     mysql_real_escape_string($this->salary_start);
        $salary_end     =     mysql_real_escape_string($this->salary_end);

        
        if (!empty($job_title) && !empty($company_name) && !empty($date))
        {// search by title & category & company & date
            $database = new Mysql_database();
            $query = "SELECT * FROM jobs j ";
            $query .= "LEFT JOIN categories c ";
            $query .= "ON j.category_id = c.id ";
            $query .= "LEFT JOIN company_owners co ";
            $query .= "ON j.company_id = co.id ";
            $query .= "WHERE (job_title = '{$job_title}' ";
            $query .= "AND category_id = '{$category_id}' ";
            $query .= "AND company_title = '{$company_name}' ";
            $query .= "AND job_date = '{$date}')";
            
            $result = $database->database_query($query);
            
        }
        
        elseif ($job_title != "" && $company_name != "" && $date == "" && $salary_satrt != "" && $salary_end != "")
        {// search by title & category & company & sallary
            $database = new Mysql_database();
            $query = "SELECT * FROM jobs j ";
            $query .= "LEFT JOIN categories c ";
            $query .= "ON j.category_id = c.id ";
            $query .= "LEFT JOIN company_owners co ";
            $query .= "ON j.company_id = co.id ";
            $query .= "WHERE (job_title = '{$job_title}' ";
            $query .= "AND category_id = '{$category_id}' ";
            $query .= "AND company_title = '{$company_name}' ";
            $query .= "AND (job_salary > {$salary_start} AND job_salary < {$salary_end}))";
            
            $result = $database->database_query($query);
            
        }
        
        elseif ($job_title != "" && $company_name == "" && $date == "")
        {// search by title & category
            $database = new Mysql_database();
            $query = "SELECT * FROM jobs j ";
            $query .= "LEFT JOIN categories c ";
            $query .= "ON j.category_id = c.id ";
            $query .= "LEFT JOIN company_owners co ";
            $query .= "ON j.company_id = co.id ";
            $query .= "WHERE (job_title = '{$job_title}' ";
            $query .= "AND category_id = '{$category_id}')";
            
            $result = $database->database_query($query);
            
        }
        
        elseif (!empty($category_id) && !empty($company_name))
        {// search by category & company
            $database = new Mysql_database();
            $query = "SELECT * FROM jobs j ";
            $query .= "LEFT JOIN categories c ";
            $query .= "ON j.category_id = c.id ";
            $query .= "LEFT JOIN company_owners co ";
            $query .= "ON j.company_id = co.id ";
            $query .= "WHERE category_id = '{$category_id}' ";
            $query .= "AND company_title = '{$company_name}'";
                
            $result = $database->database_query($query);
        }
        

        elseif (!empty($category_id))
        {// search by category
            $database = new Mysql_database();
            $query = "SELECT * FROM jobs j ";
            $query .= "LEFT JOIN categories c ";
            $query .= "ON j.category_id = c.id ";
            $query .= "LEFT JOIN company_owners co ";
            $query .= "ON j.company_id = co.id ";
            $query .= "WHERE category_id = '{$category_id}'";
            
            $result = $database->database_query($query);
            
        }
        
        return $result;
    }
    
    function search_results()
    {
        $result = $this->jobs_by_title_category_company($this->job_title, $this->category_id
                                             , $this->company_name, $this->date, $this->salary_start, $this->salary_end);
        
        while ($row = mysql_fetch_array($result))
        {
            $job_info[] = $row;
        }
        return $job_info;
    } 

[NogDog]

totally untested, but should give you the general idea. (Note the use of double "$$" for "variable variables", which required me to change some of the function parameters to match the corresponding field names.)

    function jobs_by_title_category_company(
        $job_title,
        $category_id,
        $company_title,
        $job_date,
        $salary_start,
        $salary_end
    )
    {
        $where = array();
        $equalFields = array('job_title', 'category_id', 'company_name', 'date');
        foreach($equalFields as $fld) {
            if(!empty($$fld)) {
                $where[] = $fld.'='.mysql_real_escape_string($$fld)."\n";
            }
        }
        if(!empty($salary_start) and !empty($salary_end)) {
            $where[] = 'job_salary>='.mysql_real_escape_string($salary_start).' AND '.
                       'job_salary<='.mysql_real_escape_string($salary_end)."\n";
        }
        $sql = "<everything before the where clause here, then...>
        WHERE ".implode(' AND ', $where)."
        <everything after the where clause here>";
        // rest of function...
    } 

[cpowell89]

You could try using the LIKE command built into MySQL.