PHP SQL AJAX get data from before certain datetime

Question

I am comparing two datetime in the SQL statement. This is done in PHP.

$result=mysqli_query($con,"SELECT 
                                * 
                            FROM 
                                join 
                            WHERE 
                                join_date <= '$last_join_dates'
                            ORDER BY 
                                join_date DESC LIMIT 3 OFFSET 3");

$last_join_dates gets update every time, so that I use offset to keep pulling in new data. However, comparing the date is not actually working.

Inside database: structure join_date looks like 2015-10-24 13:30:22 and it is a datetime type.

$last_join_datesI use is the exact same format 2015-10-26 08:23:22.

Since that doesn't work.. I tried the following to convert them.

TRIED FORMAT() the datetime, but does not work:

$result=mysqli_query($con,"SELECT 
                                    * 
                                FROM 
                                    join 
                                WHERE 
                                    FORMAT(join_date, 'yyyyMMddhhmmss') <= FORMAT($last_join_dates, 'yyyyMMddhhmmss')
                                ORDER BY 
                                    join_date DESC LIMIT 3 OFFSET 3");

TRIED Convert() the datetime, but doesn't seem to work either:

$result=mysqli_query($con,"SELECT 
                                    * 
                                FROM 
                                    join 
                                WHERE 
                                    REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, join_date, 112), 126), '-', ''), 'T', ''), ':', '') <= REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, $last_join_dates, 112), 126), '-', ''), 'T', ''), ':', '')
                                ORDER BY 
                                    join_date DESC LIMIT 3 OFFSET 3");

I'm not sure how to debug this.. Any suggestions?

Community · Accepted Answer · 2017-05-23 12:15:40Z

0

well #1, JOIN is a mysql keyword so you need to either put back-ticks around it or better yet call your table something else

#2, this wouldn't be the reason it isn't working, but you should never use string interpolation like that, it is vulnerable to sql injection. Instead use $last_join_date as a bind variable: Bind variables in a mysql_query statement

The SQL you have above is correct, as seen below:

mysql> select * from test order by the_date;
+----+---------------------+
| id | the_date            |
+----+---------------------+
|  1 | 2015-01-02 11:23:21 |
|  2 | 2015-01-03 12:23:21 |
|  3 | 2015-02-03 12:25:21 |
|  4 | 2015-02-05 12:25:21 |
|  5 | 2015-02-05 15:25:21 |
|  6 | 2015-02-05 15:37:21 |
|  7 | 2015-02-05 20:37:21 |
|  8 | 2015-02-05 20:37:40 |
|  9 | 2015-03-05 20:37:40 |
| 10 | 2015-03-06 20:37:40 |
+----+---------------------+
10 rows in set (0.00 sec)

mysql> select * from test where the_date <= '2015-02-10 01:12:24' order by the_date desc limit 3, 3;
+----+---------------------+
| id | the_date            |
+----+---------------------+
|  5 | 2015-02-05 15:25:21 |
|  4 | 2015-02-05 12:25:21 |
|  3 | 2015-02-03 12:25:21 |
+----+---------------------+
3 rows in set (0.00 sec)

So try naming the table something else or use backticks "`". Also try running the query from the mysql command line. Maybe the data you have isn't what you thought but the query is working fine.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Feb 19, 2016 at 18:32

gimbel0893

3823 silver badges10 bronze badges

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

4 Comments

gimbel0893 Over a year ago

Using a string for the date in PHP is fine. Mysql will convert it itself as long as it is a valid datetime format.

SQLNub Over a year ago

Thats interesting.. some reason it isn't working as expected tho.

SQLNub Over a year ago

I think the error comes from using AJAX.. I'm trying to append more data as user "scroll" to the bottom of the screen. Thank you for the input... I'll keep digging into this.

SQLNub Over a year ago

Thank you. The offset screwed me up.. because it was already set using the datetime.. and I offset again causing the "data not show"

user1189882 · Accepted Answer · 2016-02-19 18:23:37Z

0

Try this one:

"SELECT * FROM join WHERE join_date <= '".date("Y-m-d H:i:s", strtotime($last_join_dates))."'ORDER BY join_date DESC LIMIT 3 OFFSET 3"

It converts $last_join_dates to a timestamp using strtotime, then the date function will convert it to the proper format.

answered Feb 19, 2016 at 18:23

user1189882

2 Comments

SQLNub Over a year ago

Thank you. let me give that a try

SQLNub Over a year ago

No more errors, but.. its not pulling the dates correctly for some reason. hardcoded string: 2015-10-24 13:30:50 not pulling data prior to that date.. i.e. 2015-10-23 23:22:50

Gregory R. · Accepted Answer · 2016-02-19 18:26:59Z

0

Try this:

$result=mysqli_query($con,"SELECT 
                            * 
                        FROM 
                            join 
                        WHERE 
                            UNIX_TIMESTAMP(join_date) <= '" . strtotime($last_join_dates) . "'
                        ORDER BY 
                            join_date DESC LIMIT 3 OFFSET 3");

answered Feb 19, 2016 at 18:26

Gregory R.

1,9751 gold badge22 silver badges34 bronze badges

2 Comments

SQLNub Over a year ago

Thank you. Let me give this a try

SQLNub Over a year ago

Issue was caused by the offset.. it was working originally, just offset put in showing empty values. Thank you for the suggestion tho!

Collectives™ on Stack Overflow

PHP SQL AJAX get data from before certain datetime

3 Answers 3

4 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related