mysql to php date function

Question

i have a mysql query like that

SELECT s.end_date_time AS Finishtime
FROM auctions_search AS s
WHERE end_date_time >NOW() + INTERVAL 3 HOUR;

so i need put it into php i try like this

$aToday = date("Y-m-d H:i:s"); 

$aExpireAuctionTime = DBC::$slave->selectAssoc("
    SELECT 
        s.end_date_time AS FinisDate
    FROM
        {$this->sDBName_Auction}.{$this->sTBLName_Auctions_Search}  AS s
    WHERE
        s.end_date_time >{$aToday} + INTERVAL 3 HOUR
");

but giving me error like

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '08:53:24 + INTERVAL 3 HOUR'

end_date_time column store a ending dates of items where is listed on shop

so i need select a items where they will end not more than +3

for example we have records for ending in

2013-08-15 09:51:38
2013-08-15 10:56:40
2013-08-29 18:56:00
2013-08-19 12:56:32

and current time is 2013-08-15 08:51:38

so i need select

2013-08-29 18:56:00
2013-08-19 12:56:32

cuz other one will finish after 3h

maybe query should me like

select
    s.end_date_time
from
    auctions_search AS s
where
    true
    and s.start_date_time <= 3

Quote the date string in the query, or remove the formatting and leave it unquoted in the query: date("YmdHis");, or use Unix timestamps. — symcbean, Aug 14 '13 at 08:12

score 1 · Answer 1 · answered Aug 14 '13 at 07:36

1

I guess you need to enclose your date value:

WHERE s.end_date_time >'{$aToday}' + INTERVAL 3 HOUR

Note, that direct passing values into SQL query (i.e. without escaping them) is a bad idea.

answered Aug 14 '13 at 07:36

Alma Do

37,009
9
76
105

i parse them but in dump reciving null now thats is strange :D – ameldur Aug 14 '13 at 07:39

score 1 · Answer 2 · answered Aug 14 '13 at 07:56

1

You can process NOW() record with the DATE_FORMAT() function. Then you can subtact the (days)/(months)/(years) and make your query.

answered Aug 14 '13 at 07:56

Βασιλης Ιωσηφιδης

769
7
23

("SELECT s.end_date_time AS FinisDate FROM {$this->sDBName_Auction}.{$this->sTBLName_Auctions_Search} AS s WHERE s.end_date_time > (DATE_FORMAT($aToday,'%d') + INTERVAL 3 HOUR ") – Βασιλης Ιωσηφιδης Aug 14 '13 at 08:04
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10:07:04,'%d') + INTERVAL 3 HOUR' at line 3 – ameldur Aug 14 '13 at 08:08
hmmm maybe instead of '%d' try '%k' – Βασιλης Ιωσηφιδης Aug 14 '13 at 08:13
same this is cuz cant be used in php im asking how to do in php :D – ameldur Aug 14 '13 at 08:17

score 1 · Answer 3 · edited May 23 '17 at 10:31

PHP and SQL are different languages. You are using PHP to compose SQL code. The MySQL server will not see your PHP code, just the SQL code you generate. You can have a look if you asign it to a variable and print it:

<?php

$aToday = date("Y-m-d H:i:s"); 

$sql = "
    SELECT 
        s.end_date_time AS FinisDate
    FROM
        {$this->sDBName_Auction}.{$this->sTBLName_Auctions_Search}  AS s
    WHERE
        s.end_date_time >{$aToday} + INTERVAL 3 HOUR
";

var_dump($sql);

That code should run flawlessly in your MySQL client, but it won't because it contains this:

s.end_date_time >2013-08-14 10:19:22 + INTERVAL 3 HOUR

This is obviously invalid SQL. Please compare:

mysql> SELECT 2013-08-14 10:19:22;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10:19:22' at line 1
mysql> SELECT '2013-08-14 10:19:22';
+---------------------+
| 2013-08-14 10:19:22 |
+---------------------+
| 2013-08-14 10:19:22 |
+---------------------+
1 row in set (0.00 sec)

As How can I prevent SQL injection in PHP? explains, you don't normally need to inject raw input in your SQL code (unless you're using a really outdated database library). That's what prepared statements are for.

Last but not least, unless you're trying to generate static queries for a results cache, you can simply use NOW() or CURRENT_TIMESTAMP as synonym for "current date". There's no need to calculate it in PHP.

i have allredy fix that my question is how to add these 3h with php into query? — ameldur, Aug 14 '13 at 08:48
[add 'x' amount of hours to date](http://stackoverflow.com/questions/11386308/php-add-x-amount-of-hours-to-date) — Álvaro González, Aug 14 '13 at 08:56

mysql to php date function

3 Answers3