• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Error when query mySQL table with JSON column

#1
H all,

I have looked everywhere but not able to find solution for this.

I have a table with JSON column.
id -> int -> 11
jsonStuff -> JSON
createdAt -> int -> 11

When i do simple GET query
$sql = 'SELECT * FROM user_account';
$query = $this->db->query($sql);

All i have was connection timeout error and not able to even have proper error. This only occur WHEN my table has JSON column. What is the proper way to GET the result?

Tried the following also
$this->db->select('*');
$this->db->from('user_account');
$this->db->where(array('id' => $id));
1) $this->db->get();
2) $this->db->get()->result();
3) $this->db->get()->result_array();

None of them works. Please help.
Reply

#2
The json data type is only available in MySQL versions 5.7.8 and up.

But here is how it is done:


Code:
CREATE TABLE `e_store`.`products`(
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
   `name` VARCHAR(250) NOT NULL ,
   `brand_id` INT UNSIGNED NOT NULL ,
   `category_id` INT UNSIGNED NOT NULL ,
   `attributes` JSON NOT NULL ,
   PRIMARY KEY(`id`) ,
   INDEX `CATEGORY_ID`(`category_id` ASC) ,
   INDEX `BRAND_ID`(`brand_id` ASC) ,
   CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
   CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

/* Let's sell some televisions */
INSERT INTO `e_store`.`products`(
   `name` ,
   `brand_id` ,
   `category_id` ,
   `attributes`
)

VALUES(
   'Prime' ,
   '1' ,
   '1' ,
   '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);
 
The last line is the json being inserted into the table attributes is the json column
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.