Help with comparing date stamp in PHP to date AND time stamp comingfrom MYSQL db!!!!!!

Hi all-

I've searched for at least an hour tonight before posting this
question into here. It's one of those questions that seemed simple AT
FIRST, but I'm having a hard time filtering the info I get on all the
date formats and how to compare them for my little script.

This script is to disallow a user into an area who has been banned for
a certain amount of time. So when the user logs in again, we compare
the current date to the one the admin put into MYSQL and come up with
an answer (that it is the date they are allowed back in or not).
Also, I have no control over how the data is inserted on my part,
since another company is doing it. So I can't change the date format
on that end.

Here is the code I'm working with so far:

$username = $_GET["username"];
//declare all the db stuff, etc above this line
$q = "SELECT * FROM banned WHERE username = '$username'";
$db->query($q);
$db->next_record();

$banned_user = $db->f("username");
$banned_until = $db->f("banned_until");


if ($db->num_rows() > 0) {


//$date1 = date("Y-m-d",$banned_until); this returns a datestamp
of 1969-12-31 instead of 2008-05-05 so instead I'm back to the line
below which just pulls the banned_until date AND timestamp from the
db.

$date1 = $banned_until;

$date2 = date('Y-m-d'); //this gives me exactly what I want. I
just want the banned_until to match the format

echo "$date1 compare to $date2";

//this returns a date string that currently looks like: 2008-05-05
18:11:19 compare to 2008-05-08. I can't compare this currently.

//Solution??? I want to remove the timestamp and just work with
comparing the date in Y-m-d format to see if they are equal or not.

} else {

echo $banned_until; //simply displays the banned_until stamp which
is 2008-05-05 18:11:19

}

So how do I normalize the two date stamps and then compare them
properly to allow a user back in?

Many thanks for ANY help on this!!!!!!!!

-Oedipa Maas

In our last episode,
<76698201-9d3d-4f13-9f34-a4ca376dc8ce@l64g2000hse.googlegroups.com>,
the lovely and talented oedipa@gmail.com
broadcast on comp.lang.php:

> Hi all-

> I've searched for at least an hour tonight before posting this
> question into here. It's one of those questions that seemed simple AT
> FIRST, but I'm having a hard time filtering the info I get on all the
> date formats and how to compare them for my little script.

> This script is to disallow a user into an area who has been banned for
> a certain amount of time. So when the user logs in again, we compare
> the current date to the one the admin put into MYSQL and come up with
> an answer (that it is the date they are allowed back in or not).
> Also, I have no control over how the data is inserted on my part,
> since another company is doing it. So I can't change the date format
> on that end.

> Here is the code I'm working with so far:

> $username = $_GET["username"];
> //declare all the db stuff, etc above this line
> $q = "SELECT * FROM banned WHERE username = '$username'";
> $db->query($q);
> $db->next_record();

> $banned_user = $db->f("username");
> $banned_until = $db->f("banned_until");

try echo $banned_until here to see if you really are getting a value.

The answer probably is that you are getting a string, not time, which
is expressed in an integer number of seconds.

> if ($db->num_rows() > 0) {


> //$date1 = date("Y-m-d",$banned_until); this returns a datestamp
> of 1969-12-31 instead of 2008-05-05 so instead I'm back to the line
> below which just pulls the banned_until date AND timestamp from the
> db.

This is because $banned_until = 0. I suspect your problem is not
in date but that you are not what you think from the database.

> $date1 = $banned_until;

> $date2 = date('Y-m-d'); //this gives me exactly what I want. I
> just want the banned_until to match the format

of course you could make date2 = date('Y-m-d h:m:s'); which evidently is the
format you are getting from the database. This of course has the
disadvantage of being obviously correct.

> echo "$date1 compare to $date2";

> //this returns a date string that currently looks like: 2008-05-05
> 18:11:19 compare to 2008-05-08. I can't compare this currently.

Aha! The plot thickens!

> //Solution??? I want to remove the timestamp and just work with
> comparing the date in Y-m-d format to see if they are equal or not.

> } else {

> echo $banned_until; //simply displays the banned_until stamp which
> is 2008-05-05 18:11:19

Okay. date wants the second parameter to be a number for time, a big-ass
integer in this case. What you are getting from the database is a string.
How to change a string to time? How? How?

$date1 = date('Y-m-d',strtotime($banned_until));

Note: the above contains *two* hints at *different* solutions. If you do
both of them, it won't work. Pick one.

> }

> So how do I normalize the two date stamps and then compare them
> properly to allow a user back in?

> Many thanks for ANY help on this!!!!!!!!

> -Oedipa Maas
--
Lars Eighner <http://larseighner.com/> usenet@larseighner.com
Countdown: 257 days to go.

oedipa@gmail.com escribió:
> $username = $_GET["username"];
> //declare all the db stuff, etc above this line
> $q = "SELECT * FROM banned WHERE username = '$username'";

What sounds easier to me (not tested):

SELECT username, IF(banned_until>NOW(), 'Y', 'N') AS still_banned
FROM banned
WHERE username = 'foo'

$is_banned = $db->f('still_banned)=='Y';

After this, $is_banned is a PHP boolean with the requested info.


Some general clues if you actually need to compare dates between PHP and
MySQL:

You can use Unix timestamps:
- In PHP, time() and many other time funcionts use/return timestamps
- In MySQL, you have FROM_UNIXTIME() and UNIX_TIMESTAMP() to do convertions
- Timestamps are long integers and can be easily compared:
if($banned_until>$now){ ... }

Or you can pass the PHP date to MySQL as a properly formatted string:
date('Y-m-d H:i:s', $foo). Again, dates can be compared without trouble:
WHERE BANNED_UTIL > NOW



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

On 8 May, 05:21, oed...@gmail.com wrote:
> Hi all-
>
> I've searched for at least an hour tonight before posting this
> question into here. It's one of those questions that seemed simple AT
> FIRST, but I'm having a hard time filtering the info I get on all the
> date formats and how to compare them for my little script.
>
> This script is to disallow a user into an area who has been banned for
> a certain amount of time. So when the user logs in again, we compare
> the current date to the one the admin put into MYSQL and come up with
> an answer (that it is the date they are allowed back in or not).
> Also, I have no control over how the data is inserted on my part,
> since another company is doing it. So I can't change the date format
> on that end.
>
> Here is the code I'm working with so far:
>
> $username = $_GET["username"];
> //declare all the db stuff, etc above this line
> $q = "SELECT * FROM banned WHERE username = '$username'";
> $db->query($q);
> $db->next_record();
>
> $banned_user = $db->f("username");
> $banned_until = $db->f("banned_until");
>
> if ($db->num_rows() > 0) {
>
> //$date1 = date("Y-m-d",$banned_until); this returns a datestamp
> of 1969-12-31 instead of 2008-05-05 so instead I'm back to the line
> below which just pulls the banned_until date AND timestamp from the
> db.
>
> $date1 = $banned_until;
>
> $date2 = date('Y-m-d'); //this gives me exactly what I want. I
> just want the banned_until to match the format
>
> echo "$date1 compare to $date2";
>
> //this returns a date string that currently looks like: 2008-05-05
> 18:11:19 compare to 2008-05-08. I can't compare this currently.
>
> //Solution??? I want to remove the timestamp and just work with
> comparing the date in Y-m-d format to see if they are equal or not.
>
> } else {
>
> echo $banned_until; //simply displays the banned_until stamp which
> is 2008-05-05 18:11:19
>
> }
>
> So how do I normalize the two date stamps and then compare them
> properly to allow a user back in?
>
> Many thanks for ANY help on this!!!!!!!!
>
> -Oedipa Maas

This is so simple:

$q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d') FROM
banned WHERE username = '$username'";

On 8 May, 09:14, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 8 May, 05:21, oed...@gmail.com wrote:
>
>
>
> > Hi all-
>
> > I've searched for at least an hour tonight before posting this
> > question into here. It's one of those questions that seemed simple AT
> > FIRST, but I'm having a hard time filtering the info I get on all the
> > date formats and how to compare them for my little script.
>
> > This script is to disallow a user into an area who has been banned for
> > a certain amount of time. So when the user logs in again, we compare
> > the current date to the one the admin put into MYSQL and come up with
> > an answer (that it is the date they are allowed back in or not).
> > Also, I have no control over how the data is inserted on my part,
> > since another company is doing it. So I can't change the date format
> > on that end.
>
> > Here is the code I'm working with so far:
>
> > $username = $_GET["username"];
> > //declare all the db stuff, etc above this line
> > $q = "SELECT * FROM banned WHERE username = '$username'";
> > $db->query($q);
> > $db->next_record();
>
> > $banned_user = $db->f("username");
> > $banned_until = $db->f("banned_until");
>
> > if ($db->num_rows() > 0) {
>
> > //$date1 = date("Y-m-d",$banned_until); this returns a datestamp
> > of 1969-12-31 instead of 2008-05-05 so instead I'm back to the line
> > below which just pulls the banned_until date AND timestamp from the
> > db.
>
> > $date1 = $banned_until;
>
> > $date2 = date('Y-m-d'); //this gives me exactly what I want. I
> > just want the banned_until to match the format
>
> > echo "$date1 compare to $date2";
>
> > //this returns a date string that currently looks like: 2008-05-05
> > 18:11:19 compare to 2008-05-08. I can't compare this currently.
>
> > //Solution??? I want to remove the timestamp and just work with
> > comparing the date in Y-m-d format to see if they are equal or not.
>
> > } else {
>
> > echo $banned_until; //simply displays the banned_until stamp which
> > is 2008-05-05 18:11:19
>
> > }
>
> > So how do I normalize the two date stamps and then compare them
> > properly to allow a user back in?
>
> > Many thanks for ANY help on this!!!!!!!!
>
> > -Oedipa Maas
>
> This is so simple:
>
> $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d') FROM
> banned WHERE username = '$username'";

Oops missed a bit:
$q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d')
banned_until FROM banned WHERE username = '$username'";

On 8 May, 09:34, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 8 May, 09:14, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 8 May, 05:21, oed...@gmail.com wrote:
>
> > > Hi all-
>
> > > I've searched for at least an hour tonight before posting this
> > > question into here. It's one of those questions that seemed simple AT
> > > FIRST, but I'm having a hard time filtering the info I get on all the
> > > date formats and how to compare them for my little script.
>
> > > This script is to disallow a user into an area who has been banned for
> > > a certain amount of time. So when the user logs in again, we compare
> > > the current date to the one the admin put into MYSQL and come up with
> > > an answer (that it is the date they are allowed back in or not).
> > > Also, I have no control over how the data is inserted on my part,
> > > since another company is doing it. So I can't change the date format
> > > on that end.
>
> > > Here is the code I'm working with so far:
>
> > > $username = $_GET["username"];
> > > //declare all the db stuff, etc above this line
> > > $q = "SELECT * FROM banned WHERE username = '$username'";
> > > $db->query($q);
> > > $db->next_record();
>
> > > $banned_user = $db->f("username");
> > > $banned_until = $db->f("banned_until");
>
> > > if ($db->num_rows() > 0) {
>
> > > //$date1 = date("Y-m-d",$banned_until); this returns a datestamp
> > > of 1969-12-31 instead of 2008-05-05 so instead I'm back to the line
> > > below which just pulls the banned_until date AND timestamp from the
> > > db.
>
> > > $date1 = $banned_until;
>
> > > $date2 = date('Y-m-d'); //this gives me exactly what I want. I
> > > just want the banned_until to match the format
>
> > > echo "$date1 compare to $date2";
>
> > > //this returns a date string that currently looks like: 2008-05-05
> > > 18:11:19 compare to 2008-05-08. I can't compare this currently.
>
> > > //Solution??? I want to remove the timestamp and just work with
> > > comparing the date in Y-m-d format to see if they are equal or not.
>
> > > } else {
>
> > > echo $banned_until; //simply displays the banned_until stamp which
> > > is 2008-05-05 18:11:19
>
> > > }
>
> > > So how do I normalize the two date stamps and then compare them
> > > properly to allow a user back in?
>
> > > Many thanks for ANY help on this!!!!!!!!
>
> > > -Oedipa Maas
>
> > This is so simple:
>
> > $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d') FROM
> > banned WHERE username = '$username'";
>
> Oops missed a bit:
> $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d')
> banned_until FROM banned WHERE username = '$username'";

However, having read Álvaro's reply, I totally agree that this
comparison should be done completely in the SQL.

Hey,

Thanks to everyone who weighed in on this. It's very much
appreciated. And yes, as Lars figured out, I AM getting a value back
from the DB. Unfortunately, I have no way to control how that date
goes in, but am now looking into how to extract it to match to the PHP
date.

So far and very sadly, none of the SQL solutions posed here work
verbatim, but it gives me a point for research. So far I can't get
the time limitation to pull the username out of the DB. Maybe I'll
wander over to the MYSQL group and ask if I'm doing anything wrong
syntactically.

So far these two queried are doing nothing.

$q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d')
banned_until FROM banned WHERE username = '$username'";

OR

$q = "SELECT username, banned_until, IF(banned_until > NOW(), 'Y',
'N') AS still_banned FROM banned WHERE username = '$username'";

which is weird. I made sure the username variable is being passed and
that it correctly matches a value in the DB. So it's something else.
Dunno.

But again, thank you for the input all! If you think of anything else
I could do to twea it, I'm all ears!

-Oedipa Maas


On May 8, 6:40 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 8 May, 09:34, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 8 May, 09:14, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 8 May, 05:21, oed...@gmail.com wrote:
>
> > > > Hi all-
>
> > > > I've searched for at least an hour tonight before posting this
> > > > question into here. It's one of those questions that seemed simple AT
> > > > FIRST, but I'm having a hard time filtering the info I get on all the
> > > > date formats and how to compare them for my little script.
>
> > > > This script is to disallow a user into an area who has been banned for
> > > > a certain amount of time. So when the user logs in again, we compare
> > > > the current date to the one the admin put into MYSQL and come up with
> > > > an answer (that it is the date they are allowed back in or not).
> > > > Also, I have no control over how the data is inserted on my part,
> > > > since another company is doing it. So I can't change the date format
> > > > on that end.
>
> > > > Here is the code I'm working with so far:
>
> > > > $username = $_GET["username"];
> > > > //declare all the db stuff, etc above this line
> > > > $q = "SELECT * FROM banned WHERE username = '$username'";
> > > > $db->query($q);
> > > > $db->next_record();
>
> > > > $banned_user = $db->f("username");
> > > > $banned_until = $db->f("banned_until");
>
> > > > if ($db->num_rows() > 0) {
>
> > > > //$date1 = date("Y-m-d",$banned_until); this returns a datestamp
> > > > of 1969-12-31 instead of 2008-05-05 so instead I'm back to the line
> > > > below which just pulls the banned_until date AND timestamp from the
> > > > db.
>
> > > > $date1 = $banned_until;
>
> > > > $date2 = date('Y-m-d'); //this gives me exactly what I want. I
> > > > just want the banned_until to match the format
>
> > > > echo "$date1 compare to $date2";
>
> > > > //this returns a date string that currently looks like: 2008-05-05
> > > > 18:11:19 compare to 2008-05-08. I can't compare this currently.
>
> > > > //Solution??? I want to remove the timestamp and just work with
> > > > comparing the date in Y-m-d format to see if they are equal or not.
>
> > > > } else {
>
> > > > echo $banned_until; //simply displays the banned_until stamp which
> > > > is 2008-05-05 18:11:19
>
> > > > }
>
> > > > So how do I normalize the two date stamps and then compare them
> > > > properly to allow a user back in?
>
> > > > Many thanks for ANY help on this!!!!!!!!
>
> > > > -Oedipa Maas
>
> > > This is so simple:
>
> > > $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d') FROM
> > > banned WHERE username = '$username'";
>
> > Oops missed a bit:
> > $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d')
> > banned_until FROM banned WHERE username = '$username'";
>
> However, having read Álvaro's reply, I totally agree that this
> comparison should be done completely in the SQL.

Ah never mind about my previous post. Predictably enough, it was the
cause of a small typo thereby preventing me from seeing the correct
data.

Anyway, I used Alvaro's solution and it works like a charm. Fewer
lines of code and no annoying conversions to unix timestamps and
calculating from there. so that is awesome!

So for any newbies out there still following along, the final code the
worked for me is:

$username = $_GET["username"];

$db = new ps_DBnew;
//$q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d') FROM
banned WHERE username = '$username'";
$q = "SELECT username, IF(banned_until > NOW(), 'Y', 'N') AS
still_banned FROM banned WHERE username = '$username'";
$db->query($q);
$db->next_record();

$banned_user = $db->f("still_banned");

if ($db->num_rows() > 0) {
echo "$banned_user";
//this returns a Y or N string which may be used in an "if"
statement if the condition is met or not
}


Hope this thread helps someone else in a similar position.

-oedipa


On May 8, 2:58 pm, Oedipa <oed...@gmail.com> wrote:
> Hey,
>
> Thanks to everyone who weighed in on this. It's very much
> appreciated. And yes, as Lars figured out, I AM getting a value back
> from the DB. Unfortunately, I have no way to control how that date
> goes in, but am now looking into how to extract it to match to the PHP
> date.
>
> So far and very sadly, none of the SQL solutions posed here work
> verbatim, but it gives me a point for research. So far I can't get
> the time limitation to pull the username out of the DB. Maybe I'll
> wander over to the MYSQL group and ask if I'm doing anything wrong
> syntactically.
>
> So far these two queried are doing nothing.
>
> $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d')
> banned_until FROM banned WHERE username = '$username'";
>
> OR
>
> $q = "SELECT username, banned_until, IF(banned_until > NOW(), 'Y',
> 'N') AS still_banned FROM banned WHERE username = '$username'";
>
> which is weird. I made sure the username variable is being passed and
> that it correctly matches a value in the DB. So it's something else.
> Dunno.
>
> But again, thank you for the input all! If you think of anything else
> I could do to twea it, I'm all ears!
>
> -Oedipa Maas
>
> On May 8, 6:40 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > On 8 May, 09:34, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 8 May, 09:14, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 8 May, 05:21, oed...@gmail.com wrote:
>
> > > > > Hi all-
>
> > > > > I've searched for at least an hour tonight before posting this
> > > > > question into here. It's one of those questions that seemed simple AT
> > > > > FIRST, but I'm having a hard time filtering the info I get on all the
> > > > > date formats and how to compare them for my little script.
>
> > > > > This script is to disallow a user into an area who has been bannedfor
> > > > > a certain amount of time. So when the user logs in again, we compare
> > > > > the current date to the one the admin put into MYSQL and come up with
> > > > > an answer (that it is the date they are allowed back in or not).
> > > > > Also, I have no control over how the data is inserted on my part,
> > > > > since another company is doing it. So I can't change the date format
> > > > > on that end.
>
> > > > > Here is the code I'm working with so far:
>
> > > > > $username = $_GET["username"];
> > > > > //declare all the db stuff, etc above this line
> > > > > $q = "SELECT * FROM banned WHERE username = '$username'";
> > > > > $db->query($q);
> > > > > $db->next_record();
>
> > > > > $banned_user = $db->f("username");
> > > > > $banned_until = $db->f("banned_until");
>
> > > > > if ($db->num_rows() > 0) {
>
> > > > > //$date1 = date("Y-m-d",$banned_until); this returns a datestamp
> > > > > of 1969-12-31 instead of 2008-05-05 so instead I'm back to the line
> > > > > below which just pulls the banned_until date AND timestamp from the
> > > > > db.
>
> > > > > $date1 = $banned_until;
>
> > > > > $date2 = date('Y-m-d'); //this gives me exactly what I want.I
> > > > > just want the banned_until to match the format
>
> > > > > echo "$date1 compare to $date2";
>
> > > > > //this returns a date string that currently looks like: 2008-05-05
> > > > > 18:11:19 compare to 2008-05-08. I can't compare this currently.
>
> > > > > //Solution??? I want to remove the timestamp and just work with
> > > > > comparing the date in Y-m-d format to see if they are equal or not..
>
> > > > > } else {
>
> > > > > echo $banned_until; //simply displays the banned_until stamp which
> > > > > is 2008-05-05 18:11:19
>
> > > > > }
>
> > > > > So how do I normalize the two date stamps and then compare them
> > > > > properly to allow a user back in?
>
> > > > > Many thanks for ANY help on this!!!!!!!!
>
> > > > > -Oedipa Maas
>
> > > > This is so simple:
>
> > > > $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d') FROM
> > > > banned WHERE username = '$username'";
>
> > > Oops missed a bit:
> > > $q = "SELECT username, DATE_FORMAT(banned_until,'%Y-%m-%d')
> > > banned_until FROM banned WHERE username = '$username'";
>
> > However, having read Álvaro's reply, I totally agree that this
> > comparison should be done completely in the SQL.