'Load More' sql function not loading correct products

Question

I am creating a CMS in CodeIgniter for a e-commerce site where the user can search for a product title, description or SKU and it will show the first 20 results. There is then a "more..." button which loads in the next 20 products. I can't, however, get the query for the "more..." to output the same amount of results. Here are my 2 functions from my model:

public function searchProducts($searchText)
{

    $this->db->select("prod_id, prod_sku, prod_title, prod_desc, prod_main_image");
    $this->db->where("prod_sku LIKE '%{$searchText}%'");
    $this->db->or_where("prod_title LIKE '%{$searchText}%'");
    $this->db->or_where("prod_desc LIKE '%{$searchText}%'");
    $this->db->order_by("prod_id", "desc");
    $query = $this->db->get("products", 20);

    return $this->checkSalesProducts($query->result());

}

public function loadMoreSearchProducts($searchText, $id)
{

    $this->db->select("prod_id, prod_sku, prod_title, prod_desc, prod_main_image");
    $this->db->where("(prod_sku LIKE '%{$searchText}%' OR prod_title LIKE '%{$searchText}%' OR prod_desc LIKE '%{$searchText}%') AND `prod_id` < {$id}");
    $this->db->order_by("prod_id", "desc");
    $query = $this->db->get("products", 20);

    return $this->checkSalesProducts($query->result());

}

In the load more function, the ajax call sends the ID of the last product displayed so that I can use a descending sort on the product ID to get products with lower IDs for the next 20.

Any ideas?

Answer 1

You'd want to approach "pagination" something like this. First only have one function.

public function searchProducts($searchText, $page = 0)
{
    $this->db->select("prod_id, prod_sku, prod_title, prod_desc, prod_main_image")
        ->where("prod_sku LIKE '%{$searchText}%'")
        ->or_where("prod_title LIKE '%{$searchText}%'")
        ->or_where("prod_desc LIKE '%{$searchText}%'")
        ->order_by("prod_id", "desc")
        ->limit(20, $page*20);

    $query = $this->db->get("products");

    return $this->checkSalesProducts($query->result());
}

That way, you will load the next 20 results on every different page. Having $page = 0 will default to the first 20 results, making your function generic and modular.