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 11 of 11
  1. #1
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post

    how do i safely store an array in mysql ?

    I have looked around and finding so many different ways to do this that i would like to know the best and safest way to store my array in a single field in mysql.

    my array will look something like this...

    $array['item_id'] = "I1234";
    $array['item_name'] = "Items name";
    $array['item_tax'] = "12.34";
    $array['item_postage'] = "12.34";

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    NEVER NEVER NEVER store an array of values or a list of values into a single field in a relational database table. Period.

    Don't do this.

    Find the right way.

    Read up on NORMALIZATION.

  • #3
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post
    What I was hoping to do is store the POSTS I get from PayPal, the problem is when there is more than one item in the cart as the field name changes and what if cater for 10 items and the customer orders 11 or if I cater for 100 and they order 101 !! This seems a very long way around storing the information.

    How do I get around the change in field names ie.

    a single item would give

    item_name
    item_number
    and so on

    but the multiple items would give

    item_name1
    item_number1

    item_name2
    item_number2

    and so on.

    so I would have to create a table with a stack of fields for many items, enough to cater for all possibilities!! not practical.

    how can I store all the items in a better way other than storing an array in mysql ?

    the tnx_id would of course be in its own field to prevent muliple records of the same order for when paypal send further information about the payment say 'pending' then later on 'completed' or 'failed' or what ever it may be. I am wanting to update(overwrite)/store the rest of the info that is in the POSTS.
    Last edited by jasonpc1; 07-14-2011 at 03:50 AM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    I'd say you've failed to read up on normalization. Spend some time understanding the concept. that is the better way than storing arrays and it doesn't matter how many items are ordered because you aren't storing columns, you are storing rows. one customer orders 8 items, then 8 rows. another orders 123 then 123 rows.

  • #5
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post
    Quote Originally Posted by guelphdad View Post
    I'd say you've failed to read up on normalization. Spend some time understanding the concept. that is the better way than storing arrays and it doesn't matter how many items are ordered because you aren't storing columns, you are storing rows. one customer orders 8 items, then 8 rows. another orders 123 then 123 rows.
    true to a point, but what happens if the payment is pending then the DB needs to be updated 8 times for one customer and for the other customer updated 123 times ! as paypal would be sending the POSTS that the payment is pending the first time around then again when it either failed or is completed which means i would have the DB update each ordered item for each customer.

    i was hoping to have a simpler method say

    DB table:

    tnx_id
    payment_status
    payment_gross
    num_cart_items
    items (item_number1|item_name1|item_qty1|item_number2|item_name2|item_qty2)
    customer_name
    customer_address

    something like this.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Again, NORMALIZATION.

    You will have *TWO TABLES* (at least...better would almost surely be 3 or more).

    One table holds the basic information about an order, one record per order. Another table holds the line item details, one record per item.

    (And the reason you would want, say, three tables: Properly you should only need to store a productID in each item detail record, with the productID being a foreign key to a PRODUCTS table where you have full information--including name for example--on each product.

    NORMALIZATION has been proven to work through lo these last 30 plus years of relational database work. Nothing else works as well. Don't try to reinvent the wheel and end up with a millipede instead.

  • #7
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post
    Ok point taken on board...

    But still wanting to know how I store an array whose data and the id's change. Not only for this project but for something else I am wanting to do for myself.

    I have ben looking around and see some people talking about using the serialize method ? Can this be used ?

    Quote Originally Posted by Old Pedant View Post
    Again, NORMALIZATION.

    You will have *TWO TABLES* (at least...better would almost surely be 3 or more).

    One table holds the basic information about an order, one record per order. Another table holds the line item details, one record per item.

    (And the reason you would want, say, three tables: Properly you should only need to store a productID in each item detail record, with the productID being a foreign key to a PRODUCTS table where you have full information--including name for example--on each product.

    NORMALIZATION has been proven to work through lo these last 30 plus years of relational database work. Nothing else works as well. Don't try to reinvent the wheel and end up with a millipede instead.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Serializing an array will always produce a string result which can be stored in a column. Problem here of course is that a serialized array is much larger space wise than a standard array, so you must accommodate a size of the column that exceeds the maximum size the array can ever be. When you choose to use a text type column, you are looking at 65K+ of storage allocated to this datatype. This is a tremendous waste of storage space for something that is easier to normalize.
    Aside from this, using serialization or any type of array to column technique will instantly destroy your ability to index the data and provide quick searches based on this data. Normalization will allow you the ability to quickly and easily view all clients whom have purchased a specific item. Also, what happens if a product is deleted? How do you intend to update these records in such an event; you will need to go through every record, modify the array stored and then update each record.

  • #9
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post
    Ah thank you you have just reminded me why I wanted to use this method in the first place...

    what happens if a product is deleted? How do you intend to update these records in such an event; you will need to go through every record, modify the array stored and then update each record.
    What if I delete a product, which will happen every few months. How will I know what item was sold to the customer if I have removed it and can not reference the item number anymore.

    Storing all data allows me to know exactly what was sold and how much and everything else about each item the customer has bought.

    Why would I want to update every customers previous order and remove the item that they 'have' bought ? !



    Quote Originally Posted by Fou-Lu View Post
    Serializing an array will always produce a string result which can be stored in a column. Problem here of course is that a serialized array is much larger space wise than a standard array, so you must accommodate a size of the column that exceeds the maximum size the array can ever be. When you choose to use a text type column, you are looking at 65K+ of storage allocated to this datatype. This is a tremendous waste of storage space for something that is easier to normalize.
    Aside from this, using serialization or any type of array to column technique will instantly destroy your ability to index the data and provide quick searches based on this data. Normalization will allow you the ability to quickly and easily view all clients whom have purchased a specific item. Also, what happens if a product is deleted? How do you intend to update these records in such an event; you will need to go through every record, modify the array stored and then update each record.

  • #10
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by jasonpc1 View Post
    What if I delete a product, which will happen every few months. How will I know what item was sold to the customer if I have removed it and can not reference the item number anymore.

    Storing all data allows me to know exactly what was sold and how much and everything else about each item the customer has bought.

    Why would I want to update every customers previous order and remove the item that they 'have' bought ? !
    You wouldn't delete the product from the database. You would "soft" delete it meaning your products table would have a column to indicate if the product is either offered still or if it has been deleted (you can do it either way). That way if you go and look at old orders all the product data is still there and your database is still normalized.

    And don't worry about the products table growing. Unless you are going to have a 10 million products, it isn't a big deal.
    OracleGuy

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Even 10 million products wouldn't be that big a deal. I've worked with MySQL tables that had 30 million rows and MySQL never even hiccupped.

    DON'T DELETE records that have references. Period. If you use INNODB tables and enforce referential integrity, you won't be able to, in fact, and that's all to the good.

    Jason: You seem to be trying to think of a database as an extension of memory. Bad idea. Relational databases are an entity (I almost said "creature" or "beast" which might be truer) unto themselves. Work *WITH* the relational model. Don't fight it.

    Serializing an array is an incredibly bad idea when working with any RDBMS. Serialized arrays are, at most, useful for persisting PROGRAM STATE (and I will be happy to debate whether even that usage is the best of possible choices). Your aim when working with an RDBMS is *NOT* to persist the PROGRAM state but to persist the DATA state, which is an entirely different thing. You simply have hold of the wrong end of the problem. You are trying to let the oil pressure gauge tell the driver which way to turn the wheel. (Okay, crappy analogy...but the better ones are all slightly scatological.)


  •  

    Posting Permissions

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