CODEWITHSUNDEEP
phpmysqlcodeigniter
shyam prasad
shyam prasad

Reputation: 17

how to search a keyword from multiple tables from database and there is no common field to JOIN table

I want to search a keyword in different tables if result available then I need to return that row of data to Codeigniter controller.my model function is below:

public function get_search($match) 
{
$this->db->select('*');
$this->db->like('blog.blog_title',$match);
$this->db->like('casestudy.title',$match);
$this->db->from('blog,casestudy');
$query = $this->db->get();
print_r($this->db->last_query()); exit;
return $query->result();
}

Upvotes: 0

Views: 773

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

Since there is no relation between the two tables, it may not make sense to do a join, and what you are currently doing is a cross join. Instead, consider taking a union of the two tables:

$first = DB::table('blog')
            ->where('blog_title', 'like', '%' . $match . '%')

$second = DB::table('casestudy')
             ->where('title', 'like', '%' . $match . '%')
             ->unionAll($first)
             ->get();

This answer assumes that you want to do SELECT * from each table, which in turn implies that the columns of both tables have the same type and ordering. If this be not the case, then you can refine what I wrote above by selecting particular columns which you want to be a part of the union query.

For the sake of readability, the raw query I have in mind is this:

SELECT * FROM blog WHERE blog_title LIKE '%'.$match.'%'
UNION ALL
SELECT * FROM casestudy WHERE title LIKE '%'.$match.'%';

Upvotes: 1

Related Questions