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 2 of 2
  1. #1
    New Coder
    Join Date
    Jan 2014
    Posts
    74
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Storing cart items in database

    Hi all,


    I am looking for a way to store items in a database and looking for a good way to do it. So far I have thought of a couple of ideas:

    The first is to serialise items and then store them in a table field 'cart' in the customer table.

    The second is to create a table specifically for the cart system, and every time a user adds an item to their cart it adds it to the cart table and is assigned to them by their 'userid' from their account (the 'userid' field from 'customers' table), and to then something like 'SELECT * FROM cart WHERE userid = 123 AND content = "newcart"' to bring up the users current cart. Upon transaction I would then have my system alter 'newcart' to 'purchased', ready for a new cart for a future sale. The only problem I see here is over time the table might get very bloated.


    I would like to hear others engineering techniques and ideas.
    Last edited by Phaelon; 03-13-2014 at 12:09 AM.

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,036
    Thanks
    2
    Thanked 316 Times in 308 Posts
    Have an 'order' table that holds information about each order/pending order (cart) and an 'order_details' table that holds the items in each order. The 'order' table would have columns for an id (the order id), the user id, date/time, and status (your 'newcart', 'purchased' values.) The order_details table would have one row for each item in each order/cart with an id, order id, item id, quantity, and status (the status field for each item is used, as desired, for things like back-ordered, shipped, returned, canceled...)

    To display the cart contents for any cart (based on the user id and the status = 'newcart') simply join the order and order_details tables using the order id, and join this with your product table using the item id.

    When the cart is actually ordered, you would change the 'order' table's status column from 'newcart' to 'purchased'.

    You would occasionally remove old 'order' rows and the correspond order_detail rows that were never purchased, using the date/time value in the 'order' table, i.e. remove any data that still has a status of 'newcart' that is more than 30 days or so old.

    Tables with a few million rows are common. Once you get to this size for a table, you would start using partitioning ( MySQL :: MySQL 5.6 Reference Manual :: 18.1 Overview of Partitioning in MySQL ) to 'archive' older data while keeping it available.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

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