best way to insert a value calculated during data entry?

[azw]

Is it possible to both:
--insert a number of values in a table, and
--calculate a new value using 2 of the values just inserted and then add that to the table in the same row?

Well, they're not exactly "inserted" values: the two values are a timestamp and an auto increment index. So I can't know them until the insert is underway or completed, can I?

I can imagine doing this in four steps:

1. insert new values,
2. select the 2 values,
3. calculate the new value from those 2,
4 insert the calculated value

Is there a faster method?

Can I have mySQL do the calculation on the fly in one step:

1. insert new values; using timestamp and index just added to db, calculate new value, insert it in same statement

What do you think?

[DoppyNL]

I fail to see the point in storing a value in you're record that can be contructed from a timestamp and an id field.
It might be easyer to calculate the value when actually needed (viewed by a user)

Greetz Daan

[DutchBen]

Althought i have to agree with the previous post .. its not really necessary it seems, it is possible to use a calculated value in an insert query

eg. INSERT INTO example (field1, field2, field3) VALUES ('10', '20', field1 * field2)

this would fill field 3 with '200'

I might be making a few syntax mistakes here because i dont feel like looking it up but i'm sure its possible.

[azw]

The problem I'm wrestling with is how to scramble the timestamp and index so the user can't guess someone else's entry and then access that other person's data.

But the first response led to to wonder if I could encrypt the timestamp and index, and then test that with the encrypted (but not stored) data from the db. That would save having to store what is essentially the same data.

So, in a roundabout way, that helped!

In reference to the second response, I can see how what you suggest would work if you know the values. I'm trying to use two values that are added by mySQL, so I can't know them (as far as I know) at the time I'm running the insert. Maybe there's a variable that represents them?

[azw]

Darn. When I went to try out that approach, I realized that the data I look for in the dB is the same data that I'm combining and encrypting. So I can't search for the unencrypted data if I only have a combined and encrypted version. (Yes, I could if I could unencrypt it, but mcrypt isn't compiled with my host's php.)

When things get this convoluted, I usually start poking around to see if there isn't a simpler solution!

[DutchBen]

Ok ok ... you made me look it up

Code: Select all

INSERT INTO example (field1, field2, field3) VALUES ('', NOW(), LAST_INSERT_ID() * [b] field2 [/b])


Note you don't actually need to know the value in field2 , mysql fills this in itself. Anyway ... your probably right and there is a better, different, way to fix this problem.

[azw]

That's interesting; thanks for looking it up. I can think of two ways to use it. The first I'm not happy with. Am I right to reject the first method?

FIRST:
I could give the user a "password" which is really an encrypted version of their line in the db's auto incremented id. Then when the user comes back to get their card data, mySQL would select the line where the (unencrypted) id is the same as the user's encrpyted id. That would require temporarily encrypting each line's id before testing.

That strikes me as really bloated, a bad method. Do you agree?

SECOND:
It seems to me the faster, more direct method is simply to store the encrypted id (indexed), and then test against it directly when the user returns. And I can use Last_Insert_Id to create the encrypted id--so I'm still in your debt!