Josh Gertner Josh Gertner - 5 months ago 19
PHP Question

Can I create a view that needs to calculate its variables using multiple variables from multiple tables?

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)

Price(size, price)

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))


So the view in this example i am trying to create would calculate the total cost for each rental for easy display

total cost is
price of specific vehicle size * number of days rented (if returned on same day is 1) + (tank * 5) + damage


The view would also contain the rentalId, and customerId to make it simple to search for total amount spent.

can i do this in mysql or would it need to be done in php?

Answer

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.