Page 1 of 1

Query from multi select form fields

Posted: Mon May 19, 2008 11:40 pm
by djkomplex
I am trying to write a class that will write a search query using multiple select form fields for the data. Here is the page to see what I am doing...

http://204.9.76.17/job_search.php

And here is the class code...

Code: Select all

 
<?php
 
class JobSearch
{
    public $query = "";
    
    public function __construct()
    {
        $db = new MySQL();
    }
    
    public function __destruct()
    {
        // TODO: add close db
    }
 
    public function job_search($keywords, $jobtype, $category, $state, $country, $sec_clearance)
    {
        // TODO: add search code
        $query = "SELECT jobs.jid, jobs.company, DATE_FORMAT(jobs.postdate, '%b %e, %Y') ";
        $query .= "AS postdate, jobs.jobtitle, jobs.description, jobs.jobtype, jobs.jobcountry, jobs.jobstate, jobs.jobcity, jobs.category, jobs.active, ";
        $query .= "jobs.securityclearance, jobs.companyimageurl FROM jobs WHERE jobs.active = '1' AND ";
        $query .= $this->build_keyword_query($keywords) . " AND ";
        $query .= $this->build_array_query("jobs.jobtype", $jobtype, true) . " OR ";
        $query .= $this->build_array_query("jobs.category", $category, true) . " OR ";
        $query .= $this->build_array_query("jobs.jobstate", $state, true) . " OR ";
        $query .= $this->build_array_query("jobs.jobcountry", $country, true) . " OR ";
        $query .= $this->build_array_query("jobs.securityclearance", $sec_clearance, true);
        $query .= " ORDER BY jobs.postdate DESC";
        return $query;
    }
    
    private function build_keyword_query($keywords)
    {
        $i = 0;
        $key_arr = explode(" ", $keywords); // split keywords into array
        $query .= "jobs.jobtitle='" . $keywords . "'";
        
        return $query;
    } //build_keyword_query
    
    private function build_array_query($table_id, $query_array, $like = true)
    {
        $i = 0;
        $cond = ($like == true) ? (" LIKE ") : ("=");
        if (is_array($query_array))
        {
            foreach ($query_array as $value)
            {
                $query .= $table_id . $cond . "'" . $value . "'";
                if ($i != count($query_array)-1)
                {
                    $query .= " OR ";
                } //if
                $i++;
            } //for
        }
        else
        {
            $query = $table_id . " LIKE '" . $query_array . "'";
        }
        return $query;
    } // build_array_query
} // JobSearch
?>
 

Re: Query from multi select form fields

Posted: Tue May 20, 2008 1:10 am
by Christopher
What is not working?

Re: Query from multi select form fields

Posted: Wed May 21, 2008 11:09 pm
by djkomplex
Sorry, I had been up for about 43 hrs straight. I fixed it, its actually the query that is wrong. I think I need something besides SELECT * FROM table WHERE col1=var OR col1=var OR col1=var AND col2=var OR col2=var OR col2=var AND col3=var OR col3=var AND col4=var ORDER BY table DESC.