Hello,
I am creating a database for a delivery company am facing a lot of complex queries. this is the info i have done.
1) I have created a page selection.php, two drop down list of Pick up and Drop off area which are the same, a entire list of locations of major locations in england from aberdeen to Kent. user will choose from two drop down list dropoff and pickup the locations.
i) when user chosen both they will click OK button which will compare data selected to database, SELECT the locations, miles and cost, and then display chosen info to chosen.php
2) selection.php = Within those two drop down menu list is the operation areas (a map of North of england e.g Nottingham, Manchester) as well as major locations around england.
3) i have created some tables in mysql.
i. choices - with columns as follows: locations [this displays entire list of locations including operation areas], pick up [this is the where the info of pick up location from the drop down list pick up will be entered, drop of [drop of list choice from drop of list]
ii) cost - with columns id_picks [to group the pick up and drop of choices together under this ID], from PU [pick up choice], distance PU [how much distance], from DO [ drop off choice], distance DO [mileage from Drop off] , tot_mileage [total miles pickup and drop off].
Now for the complication part.
A) Pick up is always starts from head office (e.g manchester).
😎 Pick up location can be anywhere on list. e.g to Leeds. Manchester to leeds = 40 miles @ 9p per mile (am guessing mileage)
c) Drop off area can be any part of locations on the list e.g Leeds or to headoffice (0 miles, £0 charge)
d) BUT either pick up or drop off must b in the list of operation areas.. e.g as operation areas is in north england can not have choices such as PIck up Wales, Drop off Dover.
What is very difficult is the queries.. how on earth can i give id groupings to any set of pick up and drop off choices when there are so many possible variations? I will need to work out total pick up and drop off mileage and price for that. however, its easy enough for pick up calculations querying as its always from headoffice. but if always starts from headoffice Manchester to pick up at London for instance and then wants to drop off in LEEDS (OK selection as Drop off is in operation area) how on earth can I query for these hundreds of possible area selections?
Calculations are very hard...very confused... please help!!!!
ps. chosen.php will need to display a page with the pick up drop of location chosen, micellaneous cost, net price, mileage, and VAT total. then OK button will send data to database to update.
