CODEWITHSUNDEEP
phpmysqlcodeigniter
Buğra Kara
Buğra Kara

Reputation: 31

How to use mysql cast json?

I want to sort the prices in Json from small to large, but I think I made something wrong where exactly could I make a mistake?

    public function kitaplik($id)
    {
        $this->db->where('kitaplik_isbn', $id);
        $this->db->order_by("CAST(json_object('kitaplik_ifiyat', kitaplik_json) AS DECIMAL(9,2))");
        $query = $this->db->get('kitaplik');
        return $query->result();
    }

kitaplik->kitaplik_json

{"kitap_fiyat":25.46,"kitap_ifiyat":12}

I have many products in the "kitaplik" table and the prices of these products are in json. I would like to sort these products from small to large using CAST Decimal. But I have to sort by taking the prices in the "kitaplik_ifiyat" section in json. How can I do this?

Upvotes: 0

Views: 78

Answers (1)

Buğra Kara
Buğra Kara

Reputation: 31

this is how I solved the problem


    public function kitaplik($id)
    {
        $this->db->select("kitaplik_json, kitaplik_isbn, JSON_EXTRACT(kitaplik_json, '$.kitaplik_ifiyat') AS fiyat");
        $this->db->where('kitaplik_isbn', $id);
        $this->db->order_by('fiyat ASC');
        $query = $this->db->get('kitaplik');
        return $query->result();
    }

Upvotes: 1

Related Questions