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 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Updating an array

    Any chance anyone could give me a pointer on how to update the following?

    I have a string of my data in one row of a table:

    2011-12-13;;1;;0;;3,2011-12-14;;1;;0;;1,2011-12-15;;1;;0;;5,2011-12-16;;1;;0;;1;

    (Year-Month-Day;;Availability;;Price;;Season,)

    I also have 5 variables I've created. I need to update the Price of each of these strings with a var based on the season id.

    So update Price with $high where Season = 1

    So update Price with $mid where Season = 2

    So on and so on. I can work this type of thing out when I have the dat in separate rows but not when all of the data is in one array like this.

    Make sense?

    Cheers
    Rich

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Redesign your table.
    Any time you store multiple values in a table you are violating first normal form of database normalization.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Opinion? You *SHOULD* have the "dat[a] in separate rows."

    You should *NEVER* have a delimited list of values in a single field in *ANY* relational database.

    You really should redesign this database and follow proper design principles.

    If this is somebody else's design, go whack them up side the head and make them change it. If it's your boss's design, and you are stuck with it...

    You *could* do this. Just put all the data in one string and then split the string on the semicolons. Then loop through the array, processing 3 elements at a time. Then use logic to change the proper element of this now-15-element array. When finishied, JOIN the array back into a single string (using semicolon as separator) and finally update the DB. That's a truly crappy solution compared to doing it right with a separate table. But it could work.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I hear you loud and clear and understand, but its not my code and with limited knowledge and time I have ti use what I can get my hands on.

    Thanks for the replies.

    Cheers
    Rich

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Okay, so do what I said. With minor mod.

    Read the string into PHP.
    Split it on the commas.

    Process it by looping through the resultant array.
    For each element of the array, split on ";;" to create an inner array.
    If the last element of this inner array (which should be the Season) matches the value(s) you are looking for, then change the next-to-last element of the inner array (which should be the Price). Re-join the inner array and put it back in the outer array.
    When the loop is done, rejoin the outer array with a comma and then, finally, go UPDATE the record in the table.

    It's going to be really slow and really tedious, but it should work just fine.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    meadsy25 (01-13-2012)

  • #6
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorted thank 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
    •