CODEWITHSUNDEEP
mysql
Jason SKY
Jason SKY

Reputation: 13

How can I retrieve data from the database when the date > now()

My purpose is since the time I login my page, I want my web to show how many updated data in the database. My code is like this

$current = $_SESSION['date'];
$query2 = "SELECT * FROM gmaptracker1 WHERE datetime >= '$current'";

When I echo the $current, it showed 27/09/14 : 06:53:24, so the $current is correct, however, when I request the number of database where date>='$current', I get zero, although I have inserted to the database the data with datetime 28/09/14 : 06:53:24 and 29/09/14 : 06:53:24.

Can anyone help me to get out of this, please?

Upvotes: 0

Views: 331

Answers (1)

ILikeTacos
ILikeTacos

Reputation: 18696

Few things,

It seems like your code is vulnerable to SQL Injection. Just because you retrieve the content of the date from a session, it doesn't mean that it's safe.

Also, why do you need it to be in a session variable? If you always want to retrieve dates bigger than NOW() you can just write your query this way:

SELECT * FROM gmaptracker1 WHERE datetime >= NOW()

The part that caught my attention was the format you're storing the dates.

You said that when you echo'ed $_SESSION['date'] the value was: 27/09/14 : 06:53:24

Now, that does not look like the date format at all. Is your column actually a datetime or timestampcolumn?

If it's a VARCHAR or any other type other than datetime or timestamp, then there's no way for MySQL to know that you're trying to retrieve dates that occur in the future.

If you already have data stored, then it isn't going to be as easy as changing the data type because you already have data, and your data is in the wrong format. The format that MySQL stores datetime information is as follows:

YYYY-MM-DD HH:MM:SS

Based on the comments you left, you don't need the time > NOW(), you need the time when you log in. Now it makes sense why you're storing that time in a variable.

The problem is the format you're storing it.

Since you're using PHP, then you have to store the time this way:

$time = new DateTime();
$_SESSION['date'] = $time->format("Y-m-d H:i:s");

Upvotes: 2

Related Questions