MySql query to compare dates php

[MonoConmab]

I'm designing a rental service and am working on a query to see if an item is available in a certain date range

So far my query is as follows

$q = "SELECT SUM(quantity) AS total FROM orders, order_details WHERE orders.order_id = order_details.order_id";
        $q .= " AND equip_ID =".$item['id'];
        $q .= " AND  'start_date' > '".$start_date."'";

$result = $db->query($q);
$row = mysqli_fetch_assoc($result);
$sum = $row['total'];

Say for instance $start_date = 2021-03-03 the output of $q would be 

SELECT SUM(quantity) AS total FROM orders, order_details WHERE orders.order_id = order_details.order_id AND equip_ID =12 AND  'start_date' > '2021-03-03'

This does not work but if i manually insert $q into my mysql database it works.

It comes up with the error shown below when I submit it from php, but not when I do directly into mysql

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''start_date' > '2021-03-03'' at line 1

When I try hard-coding a date in I get the same error. Whats confusing is it works when I directly put it into mysql

Edited March 3, 2021 by MonoConmab
Elaborating

[Barand]

Column names should not be in quotes.

 

[edit...] While I'm here,

• don't put variables into your query strings, use prepared statements,
• and switch to PDO - it's far superior to mysqli.

Edited March 3, 2021 by Barand

[MonoConmab]

Taking the Column Names out of the quotes doesn't work either

 

Also I'm new to PHP, how would I format this in PDO?

Edited March 3, 2021 by MonoConmab

[Barand]

The SQL stays the same for PDO as it was for mysqli. Only the PHP code changes. However I recommend you use explict JOIN .. ON syntax and not the implicit FROM a, b WHERE synatx.

If $db is a PDO connection, then ...

$q = "SELECT SUM(quantity) AS total 
        FROM orders
             INNER JOIN
             order_details ON orders.order_id = order_details.order_id 
        WHERE equip_ID = ? 
          AND start_date > ?
        ";
$stmt = $db->prepare($q);
$stmt->execute( [ $item['id'] , $start_date ] );   // pass the values here, not in the query
$total = $stmt->fetchColumn();

Your use of $item['id'] suggests that you might  be running this query when you are looping through the results of another query. If so, don't. Use one query with JOINs.

[mac_gyver]

based on the error message and the displayed spacing in the posted code, there are probably some non-printing/non-ascii character(s) between the AND and the 's (incorrect quote usage around a column name) or just the s (correct quote usage around a column name), that's breaking the sql query syntax. when you copy/pasted the echoed sql query syntax to run it directly against the database server, you are only getting the printing characters, which eliminates the cause of the sql syntax error. when you alter your code to use @Barand's suggestions, i recommend that you delete and retype everything between the end of the AND and the s.

as to the query 'working' with the quotes around 'start_date', making it string, rather than a column name, a string starting with s is greater than a string starting with a numerical digit, so that (version of the) query may be returning data, but it's not because the date comparison is working.

Edited March 3, 2021 by mac_gyver