Fetch featured product only from product table in Api

Question

Product Table

where i want to fetch only featured product. I have defined a column in database product_type in which four types of products(hot deals, newly listed, deals of the day and featured product)strong text can be listed which is optional.

Blockquote

   Here is my model|query code in codeigniter
    

 public function featured_product()
 {
    $arrResult = array();
    
    $this->db->select('*');
    $this->db->from('product');
    $this->db->where("product_type","featured");
    $result = $this->db->get()->result_array();
    if(!empty($result))
    {
        $arrResult['result'] = $result;
    }
    else
    {
        $arrResult['result'] = '';
    }
    return $arrResult ;
  }

When i try to fetch whole product list in api i get the result but i want to show only featured product.

If its not to late, change the database design so you dont use comma delimited lists in a database column. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — RiggsFolly, May 15 '21 at 17:09
Hey @RiggsFolly thanks for suggestions i will do that i mean modify the database but for knowledge prospects can we use the way i have defined above. my code is wrong here?. If my code is wrong what should be the solution in this way. — Codef, May 15 '21 at 17:19

akrys · Answer 1 · 2021-05-15T17:33:09.527

2

You could try and use find_in_set:

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

I don’t know codeigniter, but I think, this looks useful:

https://forum.codeigniter.com/thread-71337.html

edited May 15 '21 at 17:33

answered May 15 '21 at 17:27

akrys

563
1
3
9

Hey @akrys your post helped me with the function i don't know FIND_IN_SET() which work fine in my code – Codef May 15 '21 at 18:01

score 1 · Answer 2 · answered May 15 '21 at 18:10

I propose a different approach.

This is not an answer to your question, but I hope it will allow you to rethink your table structure.

Instead of storing all the product_types in a single column use a pivot table (also known as a junction table).

You'll want to store all the data that you currently have in product_types column in a separate table(the pivot, lets call it product_to_type) and reference the id of product_table and product_type in the pivot table.

Something like this:

Here's a nice db-fiddle to play around with if you want.

This has multiple advantages:

You have a atomic tables
You can add data to either table without causing trouble in other tables
Foreign key ensure data consistency(you can probably do a better job with that. The keys are used are decent but not great)
You can extract any sort of data with the correct use of joins
You can add indexes to make the query buttery smooth

I'm sure there are other advantages too.

Thanks @Andrei I have done that with a separate table. – Codef May 16 '21 at 08:10 — Codef, May 16 '21 at 08:10

score -1 · Accepted Answer · edited May 15 '22 at 00:37

As I have changed my database to separate table with product_id matching with product_type. Still I find the solution to my question with a function FIND_IN_SET().

Here is my updated modal query:

public function featured_product()
{
    $arrResponse = array();
    $data = 'featured';
    $this->db->select('*');
    $this->db->where('find_in_set("'.$data.'", product_type) <> 0');
    $this->db->from('product');
    $result = $this->db->get()->result_array();
    if(!empty($result))
    {
        $arrResponse['result'] = $result;
    }
    else
    {
        $arrResponse['result'] = '';
    }
    return $arrResponse ;
 }

Fetch featured product only from product table in Api

3 Answers3