conditional search in PHP using codeigniter

Question

I have this table in my database

I need to select all products belonging to a brand, but if the product code is the same you should not repeat the product, you should use the same product. Example, when selecting Toyota I intend to have the following result:

my controller selects all products by repeating the names

$marca = $this->input->post('marca');
$query = array();

if (count($marca) > 0) {
    $i = 0;
    foreach ($marca as $row) {
        $i++;

        if ($row !== "") {
            if ($row !== "0") {
                $query[] = $marca;
                $this->db->where_in('marca', $query);

            } 
        }
    }

}

Answer 1

Good morning!

1°) Your "Product" table has identical duplicate products. That is, if you had 20,000 Bic blue pens, there shouldn’t be 20,000 records with the same thing "Bic blue pens". For example, there are 2 records of the same name "Wheel", brand "Toyota" and code "0001". The correct is to have a "quantity" column instead of product duplication.

2°) The "Brand" column in the "Product" table is denormalized. That is, there should be a table "Brand" with columns "code" and "brand name", and in the table "Product" only the brand code, without name. And it is on this implementation that the answer below is based.

3°) The "product" column of the "Product" table should be called "name". And it is on top of this implementation that the answer below.

4°) Evaluate count($marca) > 0 is incorrect because $marca is not a array, but it’s a string from a post.

The requested question is a question to be solved in the model. In the controller you only need to sanitize the brand code before passing it to the model.

On the controller:

<?php
    ...
    $id_marca = intval($this->input->post('marca'));
    $array_produtos = this->produto_model->get_produtos_by_marca($id_marca);
    // Só para visualizar o retorno
    print_r($array_produtos);
    ...
?>

And in the model you make the filter:

<?php
    ...
    public function get_produtos_by_marca($id_marca) {
        $this->db->select('produto_id, nome');
        $query = $this->db->get_where('produto',  array('id_marca' => $id_marca));
        // Ou volta um array com resultados ou um array vazio
        return $query->num_rows() ? $query->result_array() : array();
    }
    ...
?>