Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
| Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 12th, 2008, 07:00 AM
ire ire is offline
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default storing serialized data in MySQL table

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.
 
Old November 12th, 2008, 04:25 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

\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??
 
Old November 13th, 2008, 04:50 AM
Authorized User
 
Join Date: Aug 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





 
Old November 13th, 2008, 06:27 AM
ire ire is offline
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.




Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql is stripping slashes in serialized array harpua PHP Databases 1 December 5th, 2007 07:16 AM
Storing form array data to MySQL using PHP osemollie Beginning PHP 0 February 2nd, 2006 07:32 AM
Storing/Retrieving jpgs from MySQL flippyn5 PHP Databases 1 December 9th, 2005 08:15 AM
storing serialized object in sql server ACE2084 General .NET 0 October 13th, 2005 01:35 PM
Storing PHP Session data in a MySQL db? CFerthorney PHP Databases 1 May 10th, 2004 08:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.