Skip to main content
L
lewiscob
Participating Frequently
January 13, 2009
Question

Outputing from a database

  • January 13, 2009
  • 4 replies
  • 804 views
Hi,

I have a shopping cart system that adds shop items into a database using an array. So when an order is submitted in the items field in the data base it will say something like:

Item 1, Item 2, Item 3

Now I am trying to create a returns (RMA) page and I want the user to be able to slect which items they want to return. So I want to have a form with a checkbox next to each individual item and then they can check the items to return and then submit.

How can I list the items individually as they are just one string at the moment in th DB.

Thanks
    This topic has been closed for replies.

    4 replies

    L
    lewiscobAuthor
    Participating Frequently
    January 14, 2009
    How would I loop through the array to insert the items:

    session.shoppingcart[arrayLen(session.shoppingcart)].name

    from it and inset into the DB?
    -
    -__cfSearching__-
    Inspiring
    January 14, 2009
    Yes, the table should contain whatever additional columns you need. The point was just that you only need one column for itemID.

    > How can I change this so the values are stored seperatley?

    Just loop through your array, extract the fields and run an insert query to insert each set as a separate record. I do not know how your array is structured, but I am assuming it is an array of structures, with each structure containing the item details.
    L
    lewiscobAuthor
    Participating Frequently
    January 14, 2009
    When an item is added to the cart this code is called:
    F
    Fetch
    Participating Frequently
    January 14, 2009
    A good article on database normalization can be found here:

    http://databases.about.com/od/specificproducts/a/normalization.htm

    But, in short, you're storing the data in a poor manner. Instead of saving the shop items as an array in one db field I would have a table of an arbitrarily large number of fields that would contain the data such that there is one field for each item and a field for the customer number, or some other unique marker. For example, if I don't think any client will order more than 50 items I will have a table that has the following fields

    userid
    item1
    item2
    item3
    .
    .
    .
    item50

    And then when they submit an order save the items in the fields above. When they do returns, it could pull from the return data from that table and you could use CF logic to operate on whatever item they select via the checkboxes. I'm not certain that is the absolute most efficient manner in which to do this, but to me it seems simpler than having a table which includes ALL your items, since very few clients would purchase one of everything (unless you have different clients than we do!).

    EDIT: If you don't want to completely recode your database I suppose you could simply parse the items using the comma as a delimiter between items, however that's ultimately a poor solution since your data isn't being stored in a 'good' format right now. The manner in which you're doing it now requires a lot of formatting and re-arranging of data in the output/manipulation phase; if you store it differently you can cut down on that significantly.
    -
    -__cfSearching__-
    Inspiring
    January 14, 2009
    > I would have a table of an arbitrarily large number of fields that would contain the data
    > such that there is one field for each item and a field for the customer number, or some
    > other unique marker.

    Only two columns are needed. One to store the UserID and the other the selected Item. Each selection should be entered as a separate record:

    UserID, ItemID
    1 (User A), 1 (Item1)
    1 (User A), 2 (Item2)
    1 (User A), 3 (Item3)

    Then a simple select on userID and cfoutput would display the items in whatever format you choose.
    L
    lewiscobAuthor
    Participating Frequently
    January 14, 2009
    Would I also need an orderID column to so it would be:

    UserID, OrderID, ItemID
    1 (User A), 1 (Order A), 1 (Item1)
    1 (User A), 1 (Order A), 2 (Item2)
    1 (User A), 1 (Order A), 3 (Item3)

    Then I can select the items for a users particular order?

    Also, at the moment the items are stored as an array in the shopping cart and then loaded into the one database field. How can I change this so the values are stored seperatley?
    D
    Dan_Bracuk
    Inspiring
    January 13, 2009
    Normalize your database so that your data will be useable. If you don't know what that means, I have heard good things about the book, Database Design for Mere Mortals.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices