1

I have a MySQL database SELECT with a variable that is a date. It does not return a row with an equal value, however when I hardcode the value, it does return a row: 

$q is a UNIX timestamp 
$timeslotDate = date("Y-m-d", $q + 60 * 60 * 4); // 0000:00:00 format

DOES NOT WORK:

$results = $mysqli->query("SELECT timeslot_time FROM wp_pickup_timeslots WHERE timeslot_date = $timeslotDate");

DOES WORK - Hardcoded date

$results = $mysqli->query("SELECT timeslot_time FROM wp_pickup_timeslots WHERE timeslot_date = '2020-05-23'");

MySQL table:
Fields: timeslot_ID (int), timeslot_date (date), timeslot_time (time)

Values: 0000001, 2020-05-23, 10:30:00.000000

I tried a few things, figuring this is a string, integer, time issue. Just can't figure it out. If anyone has and input into my problem, I would be appreciative!

Bill Bingham
  • 13
  • 2
  • 5
    Don’t write values into SQL strings, use parameters in the queries. If you write out what your query with the variable looks like you’ll easily see the difference. But parameters will fix this and a lot of other problems you will run into. – Sami Kuhmonen May 18 '20 at 05:22
  • Sami, thank you for responding. Can you give me an example of what a parameter is and how I'd use it in my code. Thanks in advance! – Bill Bingham May 18 '20 at 05:36
  • @BillBingham The timestamp that you are storing in $q, is it the current timestamp or not? – Hardood May 18 '20 at 05:54
  • I personally store EPOCH numeric timestamp as int(16). Beware the default int(11) that will truncate your entry so make sure you have the proper length. Over the year i have found it is way faster to sort int(16) timestamp that DATETIME in mysql. Please check https://stackoverflow.com/questions/2805074/mysql-datetime-performance as TIMESTAMP is also available. You will need to convert back N forth in php if requiered – Louis Loudog Trottier May 18 '20 at 06:03
  • @BillBingham What is the logic of adding 4 hours to your timestamp? do you really need this four hours or you meant something else? – Hardood May 18 '20 at 06:06
  • 1
    @LouisLoudogTrottier You're mistaken. INT(16) isn't a thing in MySQL. And if it was, INT(11) wouldn't truncate it. – Strawberry May 18 '20 at 06:07
  • I only meant Interger 16 charaters long, but you are right it won't truncate at 11. – Louis Loudog Trottier May 18 '20 at 06:19
  • 1
    The problem is that you don't have quotes around `$timeslotDate`. But switching to prepared statements with parameters will solve the problem better. See https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Barmar May 18 '20 at 06:21
  • Does this answer your question? [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) – Dharman May 18 '20 at 11:42
  • Just to clarify some of the questions asked, the 4 hour time difference is a starting point for the time slot. My client did not want to start the time slots until 4 hours later. – Bill Bingham May 18 '20 at 15:17
  • Thanks everyone for their replies. I did eventually throw quotes around it, and it worked. I thought because it wasn't a string, just a variable, that it did not need them. I unfortunately don't code enough to be proficient and knowledgable, just dangerous! – Bill Bingham May 18 '20 at 15:20

0 Answers0