...

View Full Version : editing records and adding a value to current data?



LJackson
03-12-2009, 10:38 PM
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

guelphdad
03-13-2009, 01:37 AM
show a representative set of data for what you are attempting to do to make your question clearer.

LJackson
03-13-2009, 02:46 PM
Hi mate, ok


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


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

Fumigator
03-13-2009, 04:56 PM
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?

LJackson
03-13-2009, 05:23 PM
hi mate i never thought of that :)

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

cheers mate
Luke

Fumigator
03-13-2009, 06:08 PM
Certainly easier than manipulating string values to come up with the primary keys.

LJackson
03-13-2009, 06:20 PM
cool i may be calling on your services later on "if" i get stuck :)

cheers for your help mate
Luke

LJackson
03-15-2009, 10:22 PM
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


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

$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

Fumigator
03-16-2009, 07:41 AM
You are fetching through the temp table at some point i assume? And this UPDATE query is inside your fetch loop?

Old Pedant
03-16-2009, 10:16 AM
I *think* that all you need is this:


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.

LJackson
03-16-2009, 11:46 AM
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

LJackson
03-16-2009, 11:47 AM
I *think* that all you need is this:


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

LJackson
03-16-2009, 12:23 PM
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

WHERE ratings.id = $id.'value'

but its not working :(

here my entire query

$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

bazz
03-16-2009, 01:25 PM
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

bazz
03-16-2009, 01:28 PM
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

LJackson
03-16-2009, 01:45 PM
Hi bazz,

the reason for doing it the above way is because i have over 50 stores which the users can rate with each store being rated on 4 seperate topics

delivery,delcharge,value,easeofuse

and i have a rate page where the user can rate the store (where the id is passed from a session so it knows which store it is rating)

the main trouble is that the star rating system im using add the data to the database straight after its clicked on so even if the validation is incorrect the data is added, so i thought if i have 4 temp records which the values are passed to when the user clicks on the star ratings then i can have some seperate code to pass on those values and add them to the main ratings table where the id = the session value if the validation is correct when the form is submitted :)

i know this is probably the worst way of doing it but its the only way i can think of getting around it... :)

cheers
Luke

LJackson
03-16-2009, 04:57 PM
ok so whats wrong with this query?


$update="UPDATE ratings, temp
SET
ratings.total_value = ratings.total_value + temp.total_value,
ratings.total_votes = ratings.total_votes + temp.total_votes
WHERE ratings.id = '$id"."value'";
$update_query = mysql_query($update);


it doesnt insert anything :(

any ideas?

cheers
Luke

LJackson
03-16-2009, 05:38 PM
ok 1 problem is that its looking up the first record in the temp table and not a specific one?

how do i get it to get the values from the temp file where the id = value

here is what i tried

$updatevalue="UPDATE ratings, temp
SET
ratings.total_value = (ratings.total_value + temp.value.total_value),
ratings.total_votes = (ratings.total_votes + temp.value.total_votes)
WHERE ratings.id = '$id"."value'";
$updatevalue = mysql_query($updatevalue);

and i also tried this

$updatevalue="UPDATE ratings, temp
SET
ratings.total_value = (ratings.total_value + temp.total_value),
ratings.total_votes = (ratings.total_votes + temp.total_votes)
WHERE ratings.id = '$id"."value' AND temp.id = 'value'";
$updatevalue = mysql_query($updatevalue);

but that doesnt seem to work either :(

any ideas
Cheers

Fumigator
03-16-2009, 05:51 PM
Quit trying to join the two tables in your UPDATE. You said you are fetching data from your temp table, so LOOP your FETCH and use the data you get out of the fetch in your UPDATE query.

LJackson
03-16-2009, 06:52 PM
Quit trying to join the two tables in your UPDATE. You said you are fetching data from your temp table, so LOOP your FETCH and use the data you get out of the fetch in your UPDATE query.

i dont know how to do that :(

but on a plus side i have got my method working :) although its very long winded but the end result are what i was hoping for.

heres the code i used

$updatevalue="UPDATE ratings, temp
SET
ratings.total_value = (ratings.total_value + temp.total_value),
ratings.total_votes = (ratings.total_votes + temp.total_votes)
WHERE ratings.id = '$id"."value' AND temp.id = 'value'";
$updatevalue = mysql_query($updatevalue);


cheers
Luke

bazz
03-16-2009, 07:02 PM
i dont know how to do that :(

but on a plus side i have got my method working :) although its very long winded but the end result are what i was hoping for.



well that code is the same as what you said earlier didn't work :confused:

Use a while loop (for example) and loop through the results of the query. Then use the values in that loop, in an insert statement and put it into the other table.

bazz

LJackson
03-16-2009, 07:14 PM
ah so it is thats typical i probably didnt upload it when i thought i had, GREAT could have saved my self some time there :)

ok i see what you mean about the looping but that will probably take me another hour or so which i'd prefere to spend on another part of my site.

thanks
Luke

bazz
03-16-2009, 07:22 PM
uh-oh. short-term effort for longer term gain is what that is called. moving on too soon can mean short term benefits for long term difficulties. and its only an hour after all.

maybe you should look up 'insert on duplicate key update'. it may be what you need.

bazz

LJackson
03-16-2009, 08:00 PM
:( lol,

i will check it out, thanks mate.

Luke



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum