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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts

    editing records and adding a value to current data?

    Hi all,

    i have 4 temp records in my database which i use to hold the data intil the user submits the form with correct validation,

    once this is done i want the values of the temp records to be added to other records depending on some varibles.

    so the 4 temp records are:
    valuetemp,easeofusetemp,deliverytemp,delchargetemp

    and these contain the following
    id storeID total_votes total_value used_ips

    i want to get the values of
    total_votes total_value used_ips

    and add them to the current data where the records equal
    $idReference.value
    $idReference.easeofuse
    $idReference.delivery
    $idReference.delcharge

    so temp record valuetemp will be added to $idReference.value and so on.

    can this be done, ive been playing around with it but never really getting anywhere

    any help would be great
    thanks

  • #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
    show a representative set of data for what you are attempting to do to make your question clearer.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    Hi mate, ok

    Code:
                     total_votes | total_value | used_ips
    valuetemp            1              4       00.9999.88.84           
    easeofusetemp        1              1       00.9999.88.84
    deliverytemp         1              5       00.9999.88.84
    delchargetemp        0              0       00.9999.88.84
    Code:
                       total_votes | total_value | used_ips
    'gamevalue'          11             34     00.9999.88.84           
    'gameeaseofuse'      13             16     00.9999.88.84
    'gamedelivery'       19             35     00.9999.88.84
    'gamedelcharge'      19             20     00.9999.88.84
    i would like the top values added to the bottom values, but instead of it selecting the row gamevalue etc it will get the value of $idReference."value" etc, if you see what i mean

    hope this helps
    Luke
    Last edited by LJackson; 03-13-2009 at 02:52 PM.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    If I were you I would design the database in a way where, in the temp rows, I could store the primary key of the "real" row that will eventually be updated using the temp row values. That way you could simply UPDATE WHERE primaryKey = primary-key-in-the-temp-table.

    This could mean a new table that stores the temp data, or a new column in your table to store the primary key of the real record, etc.

    In fact, why don't you just use a different table to store the temp stuff?

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    hi mate i never thought of that

    would that make it easier to add to the other records?

    cheers mate
    Luke

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Certainly easier than manipulating string values to come up with the primary keys.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    cool i may be calling on your services later on "if" i get stuck

    cheers for your help mate
    Luke

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    hi all,

    ok this is really confusing me

    i have two tables temp and ratings and i want the values of the 4 records to be taken from the temp table and added to the 4 records which match the store id which was passed to my rate page.

    so for example,

    in my temp table i have 4 records

    Code:
    id            storeID 	total_votes 	total_value 	used_ips
    delivery                    1               5          118.67.888.65
    delcharge
    value
    easeofuse
    and i want to add these to the 4 records in the ratings table where the ids are the value of

    $id.delivery
    $id.delcharge
    $id.value
    $id.easeofuse

    which has the same set up as the above temp file

    but im not sure where exactly to start

    this is what ive got
    PHP Code:
        $update="UPDATE ratings
        SET
            used_ips = used_ips + temp_value.used_ips,
            total_value = total_value + temp_value.total_value,
            total_votes = total_votes + temp_value.total_votes
        WHERE
            id = $id.value"

    which would oly update one record and im not sure how to get it to look up a different table, thats what the temp is suppose to indicate i know its a bad attempt

    please help!!!
    Cheers
    Luke
    Last edited by LJackson; 03-15-2009 at 10:26 PM.

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You are fetching through the temp table at some point i assume? And this UPDATE query is inside your fetch loop?

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    I *think* that all you need is this:
    Code:
    UPDATE ratings, temp
    SET
        ratings.used_ips = ratings.used_ips + temp.used_ips,
        ratings.total_value = ratings.total_value + temp.total_value,
        ratings.total_votes = ratings.total_votes + temp.total_votes
    WHERE ratings.id = temp.id
    Though adding two ip addresses together doesn't make any sense at all.
    Adding the total_value and total_votes make sense.

    &&&&&&&&&&&&&&&&

    Naw...reading it again, that doesn't help at all.

    I'm lost.

    But no matter what, adding ip addresses makes no sense.
    Last edited by Old Pedant; 03-16-2009 at 10:18 AM.

  • #11
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by Fumigator View Post
    You are fetching through the temp table at some point i assume? And this UPDATE query is inside your fetch loop?
    HI mate,
    yes i am grabing the values from the temp table as for a fetch loop nope, i dont have a loop in this process

    cheers

  • #12
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by Old Pedant View Post
    I *think* that all you need is this:
    Code:
    UPDATE ratings, temp
    SET
        ratings.used_ips = ratings.used_ips + temp.used_ips,
        ratings.total_value = ratings.total_value + temp.total_value,
        ratings.total_votes = ratings.total_votes + temp.total_votes
    WHERE ratings.id = temp.id
    Though adding two ip addresses together doesn't make any sense at all.
    Adding the total_value and total_votes make sense.

    &&&&&&&&&&&&&&&&

    Naw...reading it again, that doesn't help at all.

    I'm lost.

    But no matter what, adding ip addresses makes no sense.
    ok so if i scrapped the used_ips bit would the above work for adding the relivant values?

    cheers mate

  • #13
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok how do i get it to update where ratings id equals the value of $id plus the word value

    e.g $id = dixons
    so i would want it to look up the id dixonsvalue

    ive got
    PHP Code:
    WHERE ratings.id $id.'value' 
    but its not working

    here my entire query
    PHP Code:
        $update="UPDATE ratings, temp
            SET
                ratings.used_ips = ratings.used_ips + temp.used_ips,
                ratings.total_value = ratings.total_value + temp.total_value,
                    ratings.total_votes = ratings.total_votes + temp.total_votes
            WHERE ratings.id = $id.'value'"

    cheers

  • #14
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by LJackson View Post
    ok how do i get it to update where ratings id equals the value of $id plus the word value

    e.g $id = dixons
    so i would want it to look up the id dixonsvalue
    I am confused. why are you using an 'id' value (digit) and adding a word to it? what do you get if you ad dixons to, say, pc world. garbage, I should think. I can't get a grip on your plans but I think you need to make sure the value you are adding on, is numerical.

    It is possible that you are trying to build the db content such that there is no redundancy. it may bhe better to build a new record for each piece(group) of data and make you query bring it together in one.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #15
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by LJackson View Post
    Hi all,

    i have 4 temp records in my database which i use to hold the data intil the user submits the form with correct validation,
    why do it that way? what about storing in the long-term table with a timestamp. then when they have validated or whatever, change the timestamp value to 'confirmed'.

    once this is done i want the values of the temp records to be added to other records depending on some varibles.
    as I mentioned above, this sounds like a bad plan to me. It seems more like you are trying to minimise the number of records so your query will be quick. normalisation is the key, in that case.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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