Neeraj Neeraj - 10 days ago 7
JSON Question

How to save Json_encode data in database in magento?

I am Working on magento 1.7 version.In this I have data in a array.I encoded this in json using

json_encode
and insert into database. But I an getting error like


connection was reset.


If I insert normal value then it is working fine.
In database I have field type
longtext
.I used
mysql_real_escape_string()
,
base_63_encode()
,
serialize()
but not succeed.
I am using following code

$table = Mage::getSingleton('core/resource')->getTableName('checkout_prescription_details');
$write = Mage::getSingleton('core/resource')->getConnection('core_write');

$custom = json_encode($customoptions);
$query = "insert into {$table} set `data`='$custom';
$write->query($query);


In this when I
echo $query
then It shows encoded data and when I insert this from phpmyadmin then it insert in database but using
$write->query($query);
this is not inserting in database.

Please suggest regarding this.

Answer
  1. you did not escape the query parameter and (SQL injection aside) a JSON string usually contains single quotes, which breaks the query.
  2. you should not have done this with an SQL query in the first place, let Magento do the work for you. Assuming there is a model for this table with the alias checkout/prescription_details:
Mage::getModel('checkout/prescription_details')
    ->setData('data', json_encode($customoptions))
    ->save();

If there is no model, go ahead and create one. You should not have database tables without an according model.