php/mysql query nightmare

[herghost]

Hi all,

 

I have a bit of a query problem, sorry if this should go in the sql section its just I am attempting to write it in php.

Basically I cant get my head round how to do something.

 

I have a script which runs on a cron job once a day. This script updates a numeric value with a random number in each row.

I need this to also update a total field.

 

Basically the values after this script has been run are saved to a coloum called t_val in a table called

stocks.

 

The table looks like this:

 

stock_id stock_name stock_desc stock_code t_val y_val

 

1 a stock desc aaaa 10 20

 

What happens is the cron job basically saves the current value of t_val to y_val then updates t_val with the new random number.

 

 

The total coloum is in a table called users_stocks and looks like this:

 

user_id username total bank

 

1 admin 100 100

 

The total field is the value of all the stocks a user holds.

 

The quantitiy of stocks is found in this table(called users_stocks_details)

 

user_id stock_id quant

1 7 10

 

Basically I need to update the total field with the stocks id quantity x t_val.

 

The table users_stocks_details can also hold more than one stock for a particular user ie

 

user_id stock_id quant

1 7 10

1 5 6

1 4 18

 

all these need to be added together and multiplied by the individual t_vals for that stock_id to get the total

Can anyone help?

 

Thanks