Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.
Reply With Quote
  #2 (permalink)  
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??
Reply With Quote
  #3 (permalink)  
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





Reply With Quote
  #4 (permalink)  
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:59 PM.


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