CODEWITHSUNDEEP
phpmysqlcodeigniter
Vijay Thulung Rai
Vijay Thulung Rai

Reputation: 1

union in codeigniter my sql error

acutally i want get search with multi field so i decided to union query and i write my code following 

$this->db->select("id, title, job_type");
$this->db->distinct();
$this->db->from("jobs");
$this->db->like('title',$keyword);
$this->db->get();
$query1 = $this->db->last_query();

$this->db->select("company");
$this->db->distinct();
$this->db->from("employers");
$this->db->join('jobs', 'jobs.userId = employers.userId');
$this->db->or_like('company',$company_name);

$this->db->get();
$query2 =  $this->db->last_query();
$query = $this->db->query($query1." UNION ".$query2);

return $query->result();

as above code produce following sql query semes like bellow 

SELECT DISTINCT `id`, `title`, `job_type` FROM (`jobs`) WHERE `title` 
LIKE '%dfsdf%' UNION SELECT DISTINCT `company` FROM (`employers`) JOIN 
`jobs` ON `jobs`.`userId` = `employers`.`userId` WHERE `company` LIKE '%%'

and it shows following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN jobs ON jobs.userId = employers.userId WHERE company LIKE '%%' at line 5

can anyone help me

Upvotes: 0

Views: 367

Answers (1)

nits41089
nits41089

Reputation: 34

First of all, in order to use union the no. of columns and their data types should match in both the queries.

You are trying to do a union between a query which selects three columns with another query which only one column. This will not work.

To know more about mysql unions, you can go through the below link http://dev.mysql.com/doc/refman/5.0/en/union.html

Upvotes: 1

Related Questions