CODEWITHSUNDEEP
phpmysql
user4495602
user4495602

Reputation:

Different price on weekdays and weekends

I have a table Orders that holds information about the rental date range and the price for a car rental.

Table Orders

Table Cars

The price is given by:

SELECT ROUND((points * (SELECT DATEDIFF('$dateTo', '$dateFrom'))) * 0.95) AS price

$dateTo and $dateFrom are PHP variables that holds the value of a given date range by the user. In others words:

$dateTo = $_POST['date-to'];
$dateFrom = $_POST['date-from'];

So for each day you are given 5% off from the rental price, but I also want to check if the date range goes across a Saturday or a Sunday. If the rental is from Friday to Monday, I want to add an extra 10 points to the total price because the date range includes a Saturday.

Is this possible?

Upvotes: 0

Views: 320

Answers (1)

Poiz
Poiz

Reputation: 7617

You could use a stored Procedure for that purpose where your query may be nested inside of a Loop to check if it' s Weekday or not. Alternatively, You could check if the range of Dates has a Weekend within it using PHP like so:

<?php 

    /**
     * FUNCTION THAT CHECKS IF A WEEKEND EXISTS BETWEEN 2 GIVEN DATES
     * @param string $startDate 
     * @param string $endDate 
     * @return boolean
     */
    function dateRangeContainsWeekend($startDate, $endDate) {
        $tsDate  = strtotime($startDate);
        $diff    = date_diff(date_create($startDate), date_create($endDate));
        $dDiff   = $diff->days;

        if ($dDiff > 0) {
            for ($i = 0; $i < $dDiff; $i++) {
                $timeDiff  = 24 * 60 * 60 * $i;
                $sDate     = $tsDate + $timeDiff;
                $weekDay   = date('w', $sDate);

                if (($weekDay == 0 || $weekDay == 6)) {
                    return ($weekDay == 0 || $weekDay == 6);
                }
            }
        }
        return false;
    }

    // TESTS - - - -
    $d1 = "2016-05-02";
    $d2 = "2016-05-10";
    var_dump( dateRangeContainsWeekend($d1, $d2) );  // DUMPS true

    // NOW; IF YOU KNOW THAT THE GIVEN DATES CONTAIN A WEEKEND,
    // YOU MAY MODIFY YOUR QUERY TO REFLECT YOUR DESIRES 
    $factor = "0.95";  //CHOOSE YOUR FACTOR HERE...
    $sql    = "SELECT ROUND((points * (SELECT DATEDIFF('$dateTo', '$dateFrom'))) * $factor) AS price";

Upvotes: 0

Related Questions