How to retrieve data from json column using codeigniter?

Question

I’m a beginner with codeigniter. I’m trying to retrieve data from json column of my sql db The db structure is like this

DB NAME : order
——————————————————------------------------------------------
 Id    | description
——————————————————————————————————---------------------------
1      |  {“pc”: [{ “brand”: “name”}], “mouse”: [“LL”, “DC”]}
————————————————————————————————————--------------------------

For example, I want to retrieve all instances that has mouse = dc

$data = $this->db->select($select)->from(‘order’)
            ->where(“mouse”, “DC”) ->get()->result();

Check this. https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql — ascsoftw, Sep 23 '19 at 15:28
using only mysql the query SELECT id FROM `order` WHERE JSON_EXTRACT(description, "$.mouse[1]”) = 'DC', it works, but if I write SELECT id FROM `order` WHERE JSON_EXTRACT(description, "$.mouse[*]”) = 'DC' it doesn't work. In any case, this query has a different syntaxt using codeigniter — Jean, Sep 23 '19 at 17:26

tomgo · Answer 1 · 2019-09-24T03:08:00.150

Well, I've never worked like this before, but with the help of the user ascsoftw answer, I've found this section on the MySQL documentation that might help, I think this is even better for you case.

Then, your query may look something like this (sorry if I'm mistaken, like I said, never worked like this before):

SELECT Id FROM order where description->>"$[1][1]";

From what I understand that would retrieve all the Id's of the mouse (array index 1) with DC's description (array index 1 as well).

You may keep reading the docs of MySQL if that didn't help.

By the way,is worth mention that, you have several ways to do queries in CodeIgniter, if the CI query builders does not adapt to what you want to do, you can always do as the following example:

    $query = "SELECT * FROM table_name";

    $result = $this->db->query($query);

    if($result->num_rows() != 0)
    {
       foreach($result->result() as $row)
      {
        return something
      }
    } 
    else
    {
       return false
    }

Let me know if that helped you so I can edit with the correct answer for anyone running into the same problem.

How to retrieve data from json column using codeigniter?

1 Answers1