Hi Guys,

I need some assistance with the following scenario please. I have some serialized data that I would like to store in a MySQL database. When viewed, the serialized data looks like this:

O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:18: \"\0cart\0c_cart_owner\";s:5:\"owner\";s:22:\"\0ca rt\0c_cart_date_time\";N;s:20:\"\0cart\0c_arr_cont ents\";a:1:{i:0;O:9:\"cart_item\":5:{s:25:\"\0cart _item\0c_cart_item_id\";s:3:\"162\";s:27:\"\0cart_ item\0c_cart_item_name\";s:10:\"30BWL15481\";s:34: \"\0cart_item\0c_cart_item_description\";s:23:\"BO WL,MINIDOT,PRISM,POLY\";s:31:\"\0cart_item\0c_cart _item_quantity\";i:1;s:20:\"\0cart_item\0c_cart_id \";N;}}}

The problem is that I have not been able to successfully persist this data in the database. The Schema I am currently using for the table is:
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| order_id | int(10) | NO | PRI | NULL | auto_increment |
| order_number | varchar(255) | YES | | NULL | |
| user_id | varchar(255) | YES | | NULL | |
| product_data | longtext | YES | | NULL | |
| session_data | varchar(255) | YES | | NULL | |
| date | varchar(255) | YES | | NULL | |
+--------------+--------------+



 When I attempt to insert this data in the table using the following query statement:

SQL query is is: INSERT INTO spares_order (order_number, user_id, product_data, session_data, date) VALUES ('tlx-18954XM', 'ire@gmail.com', 'O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:18 :\"\0cart\0c_cart_owner\";s:5:\"owner\";s:22:\"\0c art\0c_cart_date_time\";N;s:20:\"\0cart\0c_arr_con tents\";a:1:{i:0;O:9:\"cart_item\":5:{s:25:\"\0car t_item\0c_cart_item_id\";s:3:\"162\";s:27:\"\0cart _item\0c_cart_item_name\";s:10:\"30BWL15481\";s:34 :\"\0cart_item\0c_cart_item_description\";s:23:\"B OWL,MINIDOT,PRISM,POLY\";s:31:\"\0cart_item\0c_car t_item_quantity\";i:1;s:20:\"\0cart_item\0c_cart_i d\";N;}}}', '0cae0dd4494d178a04244be03fff68cd', 'Wednesday 12th of November 2008 09:58:29 AM')

 I have no error messages but the table column product_data which ought to hold the serialized data holds only part of the complete string as shown:

+----------------------+
| product_data |
+----------------------+
| O:4:"cart":4:{s:15:" |
+----------------------+


 I understand this might have to do with certain characters within that serialized data that needs to be escaped. I have attempted a number of escape options but none seems to work for me. I would appreciate some assistance with this please. Thanks a lot.

\0 is a NULL character, and a null is the termination of a string in many languages.

Not sure why your serialized data would have nulls in it, but looks like maybe
the easiest solution would be to try using a BLOB column instead. But then
you *MAY* have to find a PHP way to convert a string to a BLOB, because if you
let MySQL do it, it might *still* decide that \0 is a NULL than ends the string.

I hope you won't mind too much if I say that I think it's a poor solution,
storing complex data as a delimited string in a single DB field. Is there
some reason you don't want to store it as a set of associated records in
a separate table??

Hi,

I think “Serialization in Database” article on
http://aspalliance.com/1093_Serializ...n_Database.all
  may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

Thanks to Byapti and Old Pedant for the suggestions/advice given.

Old Pedant: I don't mind your criticism of the application at all, the 'complex data' is actually an object, serialized and needs to be persisted in a database. Thanks again.