John Davidson

Message:

I am designing a site for renting party gear. Basically I am trying to see if an item is available during a certain date range. The user inputs the date in a form and it checks if the item is available during this date range

foreach ($_SESSION['cart'] as $item)

{

    $q = "SELECT quan_on_hand, equip_description FROM equipment where equip_id =".$item['id'];

    $result = $db->query($q);

    $row = mysqli_fetch_assoc($result);

    $quantity_available = $row['quan_on_hand'];

    $equip_description = $row['equip_description'];

    

    $q = "SELECT SUM (quantity) AS 'TotalRented' from orders, order_details";

    $q .= "where equip_id =".$item['id']." AND order_details.order_ID = orders.order_id AND ";

    $q .= "(( start_date >=".$start_date." AND end_date <= ".$end_date.") OR (start_date <= ".$start_date." AND end_date >= ".$end_date;

    $q .= ") OR ( start_date <= ".$end_date." AND end_date >= ".$end_date.") OR (start_date <= ".$start_date." AND end_date >= ".$start_date."));";

    

I think the problem is the variables $start_date and $end_date are strings and thus can't be compared in the mysql query. The data is stored in mysql as YYYY-MM-DD.

Basically how do i change the date strings to date objects in this format so they can be compared in the mysql query

I echoed out the code and put it straight into my mysql database and this is the output I get

SELECT SUM(quantity) AS 'TotalRented' from orders, order_details where equip_id =12 AND order_details.order_ID = orders.order_id AND (( start_date >=2021-03-04 AND end_date <= 2021-03-12) OR (start_date <= 2021-03-04 AND end_date >= 2021-03-12) OR ( start_date <= 2021-03-12 AND end_date >= 2021-03-12) OR (start_date <= 2021-03-04 AND end_date >= 2021-03-04));

When i put this into my mysql database it doesn't work as I need to put quotations around the dates, how do i do this within my php code so it will automatically enter

---

My Answer:
You can use the `date()` function in PHP to format your date strings into the correct format for MySQL. Here's how you can modify your code to do this:

php
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));

$q = "SELECT SUM(quantity) AS 'TotalRented' from orders, order_details";
$q .= " where equip_id = ".$item['id']." AND order_details.order_ID = orders.order_id AND ";
$q .= "(( start_date >= '".$start_date."' AND end_date <= '".$end_date."') OR ";
$q .= "(start_date <= '".$start_date."' AND end_date >= '".$end_date."') OR ";
$q .= "( start_date <= '".$end_date."' AND end_date >= '".$end_date."') OR ";
$q .= "(start_date <= '".$start_date."' AND end_date >= '".$start_date."'));";


By using the `date()` function with the 'Y-m-d' format, you can convert your date strings into the correct format for MySQL. This will allow you to compare dates in your MySQL query.