how do i search the data from two tables, they are not related

I am trying to build a search box to find out the similar title from 2 tables.

Art Table- id Title Type

Grants Table- id Title Type

Right now I can only show the data from grants table, how do I show the result from both of the tables if searched something similar to the title?

Model

function searchResult($keyword)
{
  $this->db->like('title',$keyword);
  $this->db->or_like('open_to',$keyword);
$this->db->from('v_grants');
$query  =   $this->db->get();
  return $query->result();
}

Controller

function searchResult() {
    $keyword    =   $this->input->post('keyword');
    $data['results']    =   $this->products_model->searchResult($keyword);
    $this->load->view('searchresults.php', $data);
}

View

<form class="form-inline my-2 my-md-0" action="<?php echo site_url('starter/searchResult');?>" method 
= "post">
  <input class="form-control" type="text" name = "keyword"/>

<br>
<table>
<?php if (count($results) > 0){
foreach($results as $row){ ?>

<table class="table">
<tr>
    <td><h2><a href="<?=  base_url()?>product/grantindividual/<?=$row->id?>"><?php echo $row->title?> 
</h2></a>  


</tr>

<?php   
}
}
else { ?>

<br>
<br>


<h1> No results found. </h1>

<?php 
}

?>

</table>

Answer

There will be some people who will recommend that you make separate queries and merge the result set because this will cut down on script convolution.

There will other people who will endorse the use of UNION ALL to combine the two table queries because they prefer a single trip to the db or like that you don’t need to alias the columns after the first SELECT (for situations where the column names differ).

I’ll accommodate your request, but understand that there is more than one way to satisfy your requirements.

CodeIgniter doesn’t currently offer a UNION method, so the best (AFAIK) you can do with active record is to compile the two SELECT queries and execute the UNION inside of a query() call.

Actual rendered queries and result sets may slightly differ depending on your database adapter — this versatility is a feature of CodeIgniter’s query building methods.

I am including an additional column (source) so that you can make informed decisions (about hyperlinks, etc.) when displaying the data. Otherwise, you might refer to a id value but not know which table the id refers to.

Code:

$this->db->select($this->db->escape('v_grants') . ' AS source, id, title, type');
$this->db->from('v_grants');
$this->db->like('title', $keyword);
$this->db->or_like('open_to', $keyword);
$vGrantsQuery = $this->db->get_compiled_select();

$this->db->select($this->db->escape('art') . ', id, title, type');
$this->db->from('art');
$this->db->like('title', $keyword);
$this->db->or_like('open_to', $keyword);
$artQuery = $this->db->get_compiled_select();
/*
var_export([
    $this->db->query($vGrantsQuery . ' UNION ALL ' . $artQuery)->result(),
    $this->db->last_query()
]);
*/
return $this->db->query($vGrantsQuery . ' UNION ALL ' . $artQuery)->result();

p.s. The Model method’s result() will either produce an empty array or an array of objects. In your View, you don’t need count(); just check if (!$result) { — this will tell you if the result set is empty.