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
– symcbean, Commented Aug 14, 2013 at 8:12

Alma Do · Accepted Answer · 2013-08-14 07:36:37Z

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, 2013 at 7:36

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ameldur Over a year ago

i parse them but in dump reciving null now thats is strange :D

Βασιλης Ιωσηφιδης · Accepted Answer · 2013-08-14 07:56:08Z

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, 2013 at 7:56

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

7697 silver badges23 bronze badges

4 Comments

Βασιλης Ιωσηφιδης Over a year ago

("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 ")

ameldur Over a year ago

"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

Βασιλης Ιωσηφιδης Over a year ago

hmmm maybe instead of '%d' try '%k'

ameldur Over a year ago

same this is cuz cant be used in php im asking how to do in php :D

Community · Accepted Answer · 2017-05-23 10:31:53Z

1

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.

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Aug 14, 2013 at 8:24

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

2 Comments

ameldur Over a year ago

i have allredy fix that my question is how to add these 3h with php into query?

Álvaro González Over a year ago

add 'x' amount of hours to date

Collectives™ on Stack Overflow

mysql to php date function

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related