在多个表中使用关键字进行搜索

Question

I am trying to set up my search bar to create a table of results when searching through two tables.

Each table (fresh, salt) has 2 columns (ID and DESCRIPTION).

if(isset($_REQUEST["term"])){
// Prepare a select statement
$term = $_REQUEST['term'];

     $sql = "SELECT * FROM fresh  WHERE 
     description LIKE '%{$term}%' or 
     sku like '%{$term}%'
     union
     SELECT * FROM marine WHERE 
     description LIKE '%{$term}%' or 
     sku like '%{$term}%'";

        if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "s", $param_term);

        // Set parameters
        $param_term = $_REQUEST["term"] . '%';

Searched terms can be anything like, "Neon Tetra", but the search currently looks for that string as is and I'd like for it to search as two+ different keywords separated by space, even if terms are backward such as "Tetra Neon".

Answer 1

which id relatating with both table use that.i m just giving example.don't know which table's have primary key or secondary. try this:

$this->db->join('fresh', 'fresh.id = salt.freshid')
                ->select('fresh.*,salt.id');

//you can all fields from salt table using salt.id,salt.name etc.

$where = '';
    if ($this->input->post('search')['value'] && $this->input->post('search')['value'] != '') {
        $where = "(fresh.sku LIKE '%" . $this->input->post('search')['value'] . "%' OR saly.sku LIKE '%" . $this->input->post('search')['value']. "%')";
    }
    if ($where != '') {
        $this->db->where($where);
    }
    $count = $this->db->get('salt')->num_rows();
    if ($this->input->post('length') != '-1') {
        $this->db->limit($this->input->post('length'), $this->input->post('start'));
    }
    $query = $this->db->get('salt')->result_array();
    return [$query, $count];