CODEWITHSUNDEEP
phpmysqlcodeigniter
Sachin Sanchaniya
Sachin Sanchaniya

Reputation: 1044

Search Result With Multiple Tables Like Query CodeIgniter

CodeIgniter / PHP / MYSQL

I have Two Tables with this structure.

  1. COUNTRY : id, country_name (1,Indonesia,2,India)
  2. STATE : id, country_id, state_name (1,1,Sulawesi Selatan) (2,1,Sulawesi Tengah) (3,1,Sumatera Utara) (4,2,Gujarat)

I want to make search with multiple tables.

For Example, If I type "si" then it will give results like

Indonesia ------- Sulawesi Selatan

Indonesia ------- Sulawesi Tengah

India ------------- Gujarat

How to find this result in codeingniter query : I am trying this query :

SELECT ts.id,ts.state_name,tc.country_name 
FROM STATE ts 
LEFT JOIN COUNTRY tc 
ON tc.id = ts.country_id 
WHERE ts.state_name LIKE '%si%' || tc.country_name LIKE '%si%'

My Output With This Query :

Sulawesi Selatan ------- Indonesia

Sulawesi Tengah ------- Indonesia

Sumatera Utara ------- Indonesia

Upvotes: 0

Views: 1548

Answers (2)

Ashu
Ashu

Reputation: 1320

Using the or_like() / orlike(), For more info please ref :https://codeigniter.com/userguide2/database/active_record.html

$this->db->select("ts.id,ts.state_name,tc.country_name");
$this->db->from("STATE ts")
$this->db->join("COUNTRY tc","tc.id = ts.country_id","Left");
$this->db->like('ts.state_name', $word);
$this->db->or_like('tc.country_name',$word);

Upvotes: 1

Shabbirhasan Nandoliya
Shabbirhasan Nandoliya

Reputation: 85

you can try this code:

Controller

public function search_general_medicine() {

        ini_set("memory_limit", "512M");
        $keyword = $this->input->post('keyword');
        $medical_id = $this->input->post('medical_id');

        if ($keyword != '' && $medical_id != '') {
            $result_medicine = $this->ws->get_medicine_details($keyword);
            print_r($result_medicine);
    }

Model

function get_medicine_details($keyword)
    {

        $query = "SELECT * FROM (
  SELECT medicine_id, medicine_name,medicine_package,medical_id,'g' FROM tbl_general_medicine
    UNION
  SELECT medicine_id, medicine_name,medicine_package,medical_id,'c' FROM tbl_medicine
  ) AS t
WHERE  medicine_name LIKE '%" . $keyword . "%' limit 20;";

        $result_medicine = $this->db->query($query);


        if ($result_medicine->num_rows() > 0) {
            return $result_medicine->result_array();
        } else {
            return array();
        }
    }

Upvotes: 0

Related Questions