0

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

MonoConman
  • 15
  • 5
  • Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – sticky bit Mar 02 '21 at 23:11
  • 1
    And use appropriate data types in the database. Don't store date/times as strings, use a [date/time type](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html). – sticky bit Mar 02 '21 at 23:12
  • 1
    There are lots of problems here. If it was me , I'd start over, and just focus on getting a working query. – Strawberry Mar 02 '21 at 23:19
  • I know it's not perfect but if you could tell me how to convert a string to YYYY-MM-DD format for use in the query that would be nice – MonoConman Mar 02 '21 at 23:24
  • What format are $start_date and $end_date ? It is difficult to suggest how to convert if we do not know the format. – Mark B Mar 02 '21 at 23:28
  • They are Strings – MonoConman Mar 02 '21 at 23:35
  • And the format of the strings is ? – Mark B Mar 02 '21 at 23:35
  • YYYY-MM-DD, Just struggling to put this into the query shown above – MonoConman Mar 02 '21 at 23:39
  • Then they do not need to be converted, they will work in the SQL query. Your problem lies elsewhere, look at the spacing, between order_details and where, there needs to be a space and possibly others as well. Copy the finished $q into an SQL editor like workbench and get it working in that first. – Mark B Mar 02 '21 at 23:42
  • I echoed out he output of $q and put it directly into my mysql database, I've changed the above question if you want to have a second look at it – MonoConman Mar 02 '21 at 23:46

1 Answers1

-1

To add single quotes just put them outside the double quotes.

$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."'));";
Mark B
  • 649
  • 5
  • 11
  • Using string interpolation or concatenation to get values into SQL queries should not be promoted. The right way is to use parameterized queries. – sticky bit Mar 03 '21 at 01:01