Storing an array in a SQL column

[Noonga]

I have an array of products in $_SESSION['order']['cart']['content']

I have been looking all around the internet for a nice, clean and straight forward example on how to store an array into a SQL column and then retrieving it, but all I am finding is people arguing on what the 'right' or 'wrong' way to do it is.

I am wondering if someone would be kind enough to demonstrate how it can be done at the most basic level?

[KuriosJon]

serialize() will get you what you want.

However...

Storing arrays in a database isn't usually a good idea. You can do what you like, of course. Serializing a PHP array and storing it in the database will work fine, but the better design in this type of situation (generally) is to create a separate table for the array items.

For example, if you have a table for users and you want to store an array of items (the shopping cart) in a column called "cart", you could do so by serializing the cart and saving it to a large varchar column. The better way would be to create an additional table called "cart" and create one row per user per item.

[Noonga]

See what I mean people. Its so hard to get a straight answer these days...

[felgall]

Whenever you have an array of values to go in a column is THE indicator that the column should be in its own table so that each value can be put in a separate row.

The new table will have a key that is the same as the original table but with one extra field added to store the array offset.

[Fou-Lu]

Originally Posted by Noonga

See what I mean people. Its so hard to get a straight answer these days...

This is an awfully arrogant reply to someone clearly trying to give you advice.
There are two major things to look for in your database design: multiple data per record and null. If either exists, there is a high probability you are not normalized to a 3NF level which will cause more headaches in the long run. Storage design is probably the single most important part of programming. Very few instances warrant the use of < 3NF, and at minimum I'd go to BCNF over 3NF.
This is a simple matter of flattening the data into two tables, and is pretty much required if any two or more records can share even a single value of the collection. Failing to normalize to a 3NF level minimum will cause both deletion and modification anomalies as well as make querying for records containing horrendously slow since these values cannot be indexed.
KuriosJon gave an example. I would however provide a cart -> cartitem -> item since the cart itself is a 1:n relationship with user, so the cart can track the user associated with it. Item on the other hand is an n:n with cart, so you need 3 tables to associate the many to many.

[litebearer]

Given that you obviously disagree with the advice offered thus far; how do you envision accomplishing your task? What methods have you tried? What errors have you encountered?

[Noonga]

So there is no one on this board that can show me how its done at the most basic level?

[Fou-Lu]

It was posted in the very first reply. If you refuse to normalize your database use serialize.

[litebearer]

Wondering who picks out his attire each day

[AaronWP]

Had the same issue. I completely understand that having individual SQL column names is better practice. Normalize normalize normalize. But, sometimes you just want to dump a blob of data and retrieve it later.....

PHP Code:

$CartInfo = array('customer_name' => 'john', 'item' => 'apple', 'shipping' => 'fed-ex 2 day');

$DataBlob = serialize($CartInfo);

//To insert assuming RowID is auto_increment
mysql_query("INSERT into OrdersTable (RowID, DataBlob) values ('', '$DataBlob')");

//To retrieve
$order_query = mysql_query("SELECT DataBlob FROM OrdersTable");
while($order_table=mysql_fetch_array($order_query)) 
{
$OrderInfo = unserialize($order_table['DataBlob']);
echo $OrderInfo['customer_name'] . ' ' . $OrderInfo['item'] . ' ' . $OrderInfo['shipping']; 
} 


Cheers

[Noonga]

Yeah, well I ended up figuring it out myself, which took me several hours, which could have taken me 1 minute if someone on this board had good social skills.

Anyhow, as Aaron said, serializing is good when you just CBF and want things to work!... However, you can't modify things when they are serialized.. I mean you can, you would would need to create a specific system to do it.. Serializing does kinda defeat the purpose of a rational database.
I lay in bed last night thinking how I can make what I need rational, but I can't see any other way. This is probably because of the way I designed my whole software system.

6 months ago I knew nothing of PHP and MySQL and feared them both, but took the plunge... Problem is I didn't plan that much and built on the fly. I do have more capability than I originally envisioned though, and to me that makes me satisfied in what I have, even though I know I could make it 100 times better with a re-write, but I have been working 10 hours a day for the last 6 month, and won't throw it away.

I'll start from scratch one day.

[litebearer]

if someone on this board had good social skills.

Laughed so hard the tears ran down my leg!

[Fou-Lu]

Originally Posted by Noonga

Yeah, well I ended up figuring it out myself, which took me several hours, which could have taken me 1 minute if someone on this board had good social skills.

Originally Posted by litebearer

Laughed so hard the tears ran down my leg!

+1.
If you instead would read what people would tell you, your answer was provided 12 minutes within your posting. Instead you were completely blinded by the good alternate advice provided to you.
Your attitude alone though certainly isn't winning you any social awards.

Anyhow, as Aaron said, serializing is good when you just CBF and want things to work!... However, you can't modify things when they are serialized.. I mean you can, you would would need to create a specific system to do it.. Serializing does kinda defeat the purpose of a rational database.

I have already pointed out anomalies involved with non-normalized data:

Originally Posted by Fou-Lu

...Storage design is probably the single most important part of programming. Very few instances warrant the use of < 3NF, and at minimum I'd go to BCNF over 3NF.
...Failing to normalize to a 3NF level minimum will cause both deletion and modification anomalies as well as make querying for records containing horrendously slow since these values cannot be indexed.

And yes it does completely defeat the purpose of a relational database. I won't even suggest a single field of serialized data when two tables can represent any unknown type and provide it in a normalized fashion.
The way I see it is of you accept a single field as un-normalized serialized data, then why not just serialize every piece of information and insert it into a database that has a single table and single field.
So I will iterate once more. Un-normalized = bad.

[tangoforce]

Originally Posted by Noonga

Yeah, well I ended up figuring it out myself, which took me several hours, which could have taken me 1 minute if someone on this board had good social skills.

Originally Posted by litebearer

Laughed so hard the tears ran down my leg!

Originally Posted by Fou-Lu

+1.

+1 here too. I read the ops postings and couldn't believe the arrogance in the tone. Noonga perhaps if you weren't so self-confident then you wouldn't of run into this mess. You clearly don't think well and you don't interact with others terribly well either. Getting help from people is a two way process of negotiation. You might not like their answer immediately so you have to renegotiate not just blast them straight away.

If you can learn to do that you'll be a welcomed member here on codingforums.