将一行mysql表中的行复制到另一个表,并修改字段值
I have a tabledaily
and a tablefood
. In food
, I store a database of foods I eat into food
. In daily
, I only store foods I eat for the day (must be selected from food
, modified, and stored in daily
), and at the end of the day, the daily
table is purged (all rows dropped with DELETE FROM daily
).
What I'm trying to figure out is how I can copy one row from table food
and copy it to table daily
while modifying it's values.
Both food
and daily
have an identical structure.
food (nothing in this table ever changes)
name calories grams fat protein
---------------------------------------
apple 50 80 1.2 2
potato 90 45 3.4 5
carrot 10 70 6.2 6
daily (the table after I ate an apple that is 93g as per example)
name calories grams fat protein
---------------------------------------
apple 58.125 93 1.395 2.325
example
I weigh out a food that only exists in my food database, and if it doesn't, I'll have to add it in.
I then pick a food from a dropdown and enter it's weight in grams.
I choose from the list an apple
. I weigh my apple
and it is 93g
.
My 93g
is divided by 80g
which yields 1.1625
. All the stats for the apple
(fat and protein) have to be multiplied by 1.1625
and then inserted into my daily
table.
How can I retrieve a row from food
, modify every value in it by my modifier (1.1625
), and insert it into my daily
table?
I just realized that I can load up whichever food I select into an array and modify each cells values, then pump that array into daily
.
In PHP you would just load the row, modify the values and build a query to insert it into daily
. Nothing fancy there, so I guess you are asking about how to do it in SQL:
INSERT INTO daily (name,
calories,
grams,
fat,
protein)
SELECT name,
calories * 1.1625,
grams * 1.1625,
fat * 1.1625,
protein * 1.1625
FROM food
WHERE name = "apple"
Can I suggest a slight modification to your schema that might make your life simpler? With the one you have, your data is not normalized.
Change your daily table to contain only two columns, one for the name and one for the grams, and this is the only information you need to insert, no need to modify any values from the food table.
Now, to retrieve the values you want to display daily, do this:
SELECT d.name, d.grams, f.fat*(d.grams/f.grams), f.protein*(d.grams/f.grams)
FROM daily d, food f
WHERE f.name=d.name;
Hope it helps