So I am trying to learn how to work with databases and php but I have run into some trouble concerning whether I can make a view for what i need or if i would just need to get all the variables into a php function and calculate it there.
Here is the database structure i have decided for the project. (The premise is setup a database and webpage for a truck rental company if it helps.)
Vehicles(vehicleId, size, gas)
Customer(customerId, firstname, lastname, streetaddress, city, state, zipcode, email, company)
Rental(rentId, customerId, vehicleId, startdate, enddate, tank(0=full positive=number of gallons missing from tank), damage(0=none))
price of specific vehicle size * number of days rented (if returned on same day is 1) + (tank * 5) + damage
You can create a view with all information you need:
CREATE VIEW myView AS SELECT rentId, customerId, (price * (CASE DATEDIFF(startdate, enddate) WHEN 0 THEN 1 ELSE DATEDIFF(startdate, enddate) ) + (tank * 5) + damage) as totalCost FROM Rental r JOIN Vehicles v ON v.vehicleId = r.vehicleId JOIN Price p ON p.size = v.size
You probably need to review the
CASE DATEDIFF... to adjust your interval, based on your business rule.