I have spent a long time trying to get this one - and I am now questioning my data structure
I have the following tables at present (telephone billing system)
TABLE -DATA
ID
ACCOUNT NUMBER
CALL TYPE
COUNTRY
CALL LENGTH
SEVERAL OTHER FIELDS NOT RELEVANT
TABLE - CUSTOMERS
ACCOUNT NUMBER
RATE TABLE
TABLE - RATETABLE
COUNTRY
PEAK
OFFPEAK
WEEKEND
On a row by row basis - I wish to update (or insert into a new table) the rate that I wish to charge for a call - taken from the DATA table.
e.g.
row 1 DATA contains the following information
ID = 1
ACCOUNT NUMBER = ABC123
CALL TYPE = PEAK
COUNTRY CODE = 44
I then get the rate table to be used from customers, and use it to select the correct rate \'where ratetable.country = data.country and ratetable.call type = data.call type\'
Have managed to do this in php - takes a very long time tho\' as it is running the script on a WHILE loop and there is approx 500k rows in data.
It strikes me there must be a more direct way of doing this - if not I will go back and have a look at my php code.
Any thoughts gratefully received - if my data structure (or explanation) could be improved then I will do.