Jump to content

MySql query to compare dates php


Recommended Posts

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 by MonoConmab
Elaborating
Link to post
Share on other sites

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 by Barand
Link to post
Share on other sites

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.

Link to post
Share on other sites

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 by mac_gyver
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.