www.webdeveloper.com
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Dec 2012
    Posts
    5

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

    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
    PHP Code:
        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;
        } 

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,251
    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.)
    PHP Code:

        
    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...
        

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Dec 2012
    Location
    England
    Posts
    4
    You could try using the LIKE command built into MySQL.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles