Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Feb 2012
    Posts
    51
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Storing an array in a SQL column

    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?

  • #2
    Regular Coder
    Join Date
    Jan 2012
    Posts
    134
    Thanks
    0
    Thanked 32 Times in 32 Posts
    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.

  • #3
    New Coder
    Join Date
    Feb 2012
    Posts
    51
    Thanks
    5
    Thanked 0 Times in 0 Posts
    See what I mean people. Its so hard to get a straight answer these days...

  • #4
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by Noonga View Post
    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.

  • #6
    Regular Coder
    Join Date
    Apr 2004
    Posts
    298
    Thanks
    0
    Thanked 23 Times in 23 Posts
    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?

  • #7
    New Coder
    Join Date
    Feb 2012
    Posts
    51
    Thanks
    5
    Thanked 0 Times in 0 Posts
    So there is no one on this board that can show me how its done at the most basic level?

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    It was posted in the very first reply. If you refuse to normalize your database use serialize.

  • #9
    Regular Coder
    Join Date
    Apr 2004
    Posts
    298
    Thanks
    0
    Thanked 23 Times in 23 Posts

    Cool

    Wondering who picks out his attire each day

  • #10
    New to the CF scene
    Join Date
    Mar 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Serialize is what you need

    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

  • #11
    New Coder
    Join Date
    Feb 2012
    Posts
    51
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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.
    Last edited by Noonga; 03-04-2012 at 12:51 AM.

  • #12
    Regular Coder
    Join Date
    Apr 2004
    Posts
    298
    Thanks
    0
    Thanked 23 Times in 23 Posts
    if someone on this board had good social skills.
    Laughed so hard the tears ran down my leg!

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by Noonga View Post
    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.
    Quote Originally Posted by litebearer View Post
    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:
    Quote Originally Posted by Fou-Lu View Post
    ...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.

  • #14
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,352
    Thanks
    61
    Thanked 528 Times in 515 Posts
    Quote Originally Posted by Noonga View Post
    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.
    Quote Originally Posted by litebearer View Post
    Laughed so hard the tears ran down my leg!
    Quote Originally Posted by Fou-Lu View Post
    +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.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •