Search in two tables with codeigniter

Question

Search in two tables with codeigniter

Asked 6 years, 9 months ago

Viewed 510 times

1

I have the following situation:

When selecting a marca in tabela compatibility, I want to list all products in the Product table, whose code is equal to the code of the selected brand.

Example: when selecting Toyota should list Cabin, Lighthouse and Wheel and when selecting Mazda should return only Cabin

I did the following:

$marca = $this->input->post('marca');
$marca = explode(',', $marca);    
$query = array();
if (count($marcas) > 0) 
{
    $i = 0;         
    foreach ($comp as $row) 
    {
        $i++;
        if ($row !== "") 
        {
            if ($row !== "0") 
            {
                $query[] = $row;
                $this->db->where_in('marca', $query);
            }
        } 
    }
}

But with this code I can only get the products with the following table:

What can I program to make the relation of the two tables through the field codigo?

and then the solution helped you?

– novic

2017/10/01 at 14:58
Good, The solution was very useful, but I could not do what I expected in my script, so I changed the composition of the tables to make my search even easier... instead of using two tables I will use a table... and use the json_decode function

– Gomes

2017/10/17 at 11:19

1 answer

Browser other questions tagged codeigniter quest

You are not signed in. Login or sign up in order to post.

by novic • **35,673** points · Answer 1 · 2017-09-29T22:35:00+00:00

To solve your problem is simple, the code below exemplifies what you need to encode:

$this->db
     ->from('compatibilidade')
     ->join('produto', 'compatibilidade.codigo=produto.codigo')
     ->where('compatibilidade.marca', $marca)
     ->select('produto.*, compatibilidade.marca')
     ->get()
     ->result_array();

Some observations may improve the performance of that SQL

Create an entry in the fields codigo of the two tables, example:

Create an entry in the field marca

As I do not know your business rule well, as your programming could even create a relationship in the two fields, but, what was exemplified enough to solve your problem.

_{Reference: Query Builder Class}