SQL Select: Multiple values

Question

Would somebody mind helping me with this?

I'm trying to select all bookings from my table with a start date of today or in the future. The issue i'm having is that I already have a limit (pagination) and sort.

$today = date("Y-m-d");
$sql = "
  SELECT * 
  FROM `bookings` 
  WHERE startdate >= $today 
  ORDER BY `startdate` ASC 
  LIMIT $offset, $no_of_records_per_page";

Edit: The code above displays all bookings regardless of date, rather than just present/future bookings.

What, specifically, is not working about this? Do you get an error? — Greg Schmidt, Jan 12 '20 at 23:17
Sorry - It ignores the 'where' and displays all bookings regardless of date. — Marcus Griggs, Jan 12 '20 at 23:18

GMB · Answer 1 · 2020-01-12T23:33:18.647

WHERE startdate >= $today

Your immediate problem is that you are not surrounding the date variable with single quotes, so you end up with something like where start_date >= 2020-01-13. MySQL sees an arithmetic operation (2020 minus 1 minus 13 = 2006) and happily executes it. Now it needs to compare column startdate (which is of date datatype or the like) to an integer: for this, it implicitely casts startdate to unsigned: this generates a number like 20200113, which is much bigger than 2006. This is not what you intend.

This woud not happen if you were using parameterized queries.

But bottom line, why bother computing the current date from PHP when MySQL has a built-in for that? Just do: WHERE startdate >= CURRENT_DATE and you are all set.

score 0 · Answer 2 · answered Jan 12 '20 at 23:28

Look at the generated SQL statement -- the current date in the SQL query should be something like '2020-01-02' (including the single quotes). Without the quotes, you'd get weird behavior like the database doing the subtraction and comparing the date against 2020-01-02 = 2017!.

SQL Select: Multiple values

2 Answers2