I have an ecommerce coded in codeIgniter3 with a search bar. Problem is caused for example when i search for ‘notebook’ as keyword, because there are thousand of result.
In my controller i have:
if (!$this->input->get('search')) {
switch ($type) {
case '1':
$category = $this->categoria->findBread($id_category);
$category->id_subcategory = 0;
break;
case '2':
$category = $this->subcategoria->findDetail($id_category);
break;
case '3':
case '8':
$menu = [];
$tipoBarra = 'search';
// $filtroProd = $products;
$search = $cadenaPaginacion;
$filtroProd = $this->producto->GetProductsgLike($search);
$uniqueIds = [];
$uniqueCategories = [];
foreach ($filtroProd as $item) {
if (!in_array($item->id_category, $uniqueIds)) {
$uniqueIds[] = $item->id_category;
$uniqueCategories[] = [
'id_category' => $item->id_category,
'name' => $item->name,
'id_padre' => $item->id_padre,
'cadenaBusqueda' => $search,
];
}
}
break;
default:
$tipoBarra = 'def';
$uniqueCategories = $this->categoria->menu();
}
} else {
$menu = [];
$tipoBarra = 'search';
$search = $this->input->get('search');
$filtroProd = $this->producto->GetProductsgLike($search);
$uniqueIds = [];
$uniqueCategories = [];
foreach ($filtroProd as $item) {
if (!in_array($item->id_category, $uniqueIds)) {
$uniqueIds[] = $item->id_category;
$uniqueCategories[] = [
'id_category' => $item->id_category,
'name' => $item->name,
'id_padre' => $item->id_padre,
'cadenaBusqueda' => $search,
];
}
if ($id_category != '') {
$category = $this->categoria->findBread($item->id_category);
$category->id_subcategory = 0;
}
}
$data = array(
'categoria_sesion' => $uniqueCategories,
);
$this->session->set_userdata($data);
//// var_dump($data);
$menu = $this->categoria->buscarcategoria($filtroProd, false, true, $search);
}
$vista_interna = array(
'tipoBarra' => $tipoBarra,
'load_Products' => $products,
'categories' => $categories,
'subcategories' => $subcategories = [],
'links' => $links,
'category' => $category,
'menu' => $uniqueCategories,
);
if ($search == "0") {
// $vista_interna['search'] = '';
if ($id_category != 0) {
$cate = $this->categoria->find($id_category);
$vista_interna['search'] = $cate->name;
} else {
// $vista_interna['search'] = $search;
}
} else {
if ($id_category != 0) {
$cate = $this->categoria->find($id_category);
$vista_interna['search'] = '<br>' . $cate->name . ' - ' . $search;
} else {
$vista_interna['search'] = $search;
}
}
$vista_config = array(
'metadata' => '',
'title' => '',
'description' => '',
);
$vista_externa = array(
'contenido_main' => $this->load->view('frontend/public/products', $vista_interna, true),
'configuracion' => $this->frontend_lib->configuraciones($vista_config),
'configurations' => $this->codegen_model->get('configurations', '*', 'id_configuration > 0'),
);
and the function getProductsgLike you could see aboveis like that:
public function GetProductsgLike($text)
{
$this->db->select('products.id_product AS id, products.name AS text,categories.id_category, categories.name as name,categories.id_padre');
$this->db->join('categories categories', 'products.id_category = categories.id_category');
$this->db->where('stock >', 0);
$this->db->where('products.active', ACTIVE);
$this->db->where('categories.id_padre!=',"0");
$this->db->like('products.name', $text);
$this->db->order_by('name', 'asc');
$this->db->order_by('products.name', 'asc');
$query = $this->db->get($this->table);
return $query->result();
}
Altough i have pagination of 6 records per page, the query give up to 5000 records.
Probably i can afford it with MySQL, but what technique i can use?