A problem with date and mysql query

[masterOFwww]

Hello. I'm trying to select users from database that were last active today or yesterday...but there is a problem...

When a user logs in, it stores both date and the exact time. So when i want to select user from db that logged in today or yesterday i must use the exact same form of time:
F j, Y - g:i a

But it doesn't work cause g:i a is chaning every minute.

E.g. i want to select user that last logged in yesterday and if i use:

$yesterday = date('F j, Y - g:i a', time()-86400);

It will select just users that logged in yesterday at the current time. I just need those that logged in anytime but yesterday.

Here is what i have in login.php:

$nicke=$row['username'];
$lastlogdate = date("F j, Y - g:i a");
.
.
.
$querybt = "UPDATE users SET lastlogdate='$lastlogdate' WHERE username='$nicke'";
mysql_query($querybt) or die(mysql_error());

And i tried to use this in the file where i select users that logged in today or yesterday:

$yesterday = date('F j, Y - g:i a', time()-86400);
$today = date('F j, Y - g:i a');
.
.
.
$checkpemail = mysql_query("SELECT * FROM users WHERE referer='' AND lastlogdate='$yesterday' OR lastlogdate='$today'");

As i said it will select user that logged in yesterday or today but at the current time...

I want it to select users that logged in yesterday or today no matter at what time...

I know it is possible on other ways, but i don't know how to do it.
I really hope someone will help a noob :)

Thanks for reading this!

[Will Gresham]

Make another column in the database and store a timestamp in it when the user logs in (much better than a formatted date anyway) and then work out this time yesterday

<?php
// Find out the timestamp for yesterday
$yesterday = time()-86400;
if($timestamp_from_db >= $yesterday) {
  // User logged in within the last 24 hours
} else {
  // User has not logged in within the last 24 hours
}

If you want to go further than 24 hours, add another 3600 to the 86400 for each 24 hours.

[masterOFwww]

Thanks so much xan! i didn't think of that!

[almostbob]

Most database problems are because the designer is trying to store the data in a Human readable form not a machine readable form
the database is for the computer, not for people
store last login

$lastlogdate = time();

UPDATE users SET lastlogdate=time() WHERE username=%S, $nicke

or have the sql update the timestamp as the sql record is being updated not as the php is being processed
no formatting or anything else, you can make it look however you want on the output string without screwing up the storage.
then you can simply find dates by comparing them with

select * where date(lastlogin) = date(now()) /*today*/
select * where date(lastlogin) = date_add(now(), interval -1 day) /*yesterday*/
select * where date(lastlogin) >= date_add(now(), interval -7 day) /*anytime this week*/

update table
change the database design model to store the timestamp, its smaller and faster, and simpler to do a

update table lastlogin = strtotime(lastlogin)

once than mess with string handling it for every entry

[masterOFwww]

Wow thanks for this, i will do that in the future...but now ill use xan's solution cause my site already has 3000 users...and i would lose all the data of inactive members etc.

Thanks again, i really learned something new :)

[almostbob]

to edit the data entry scripts about 2 minutes
to run a table update on live users and inactive users for 3000 members ~1.5 seconds
to do strtotime manipulation on every data entry for 3000 users ( 3million users (plan on growth)) just getting longer every day,
You can kludge it together with spit and wire like the wright brothers plane,
and it will fly,
a little, once, for 300yards
or you can design a plane that will fly
You will have to correct the design eventually, the processing bottleneck creating and comparing timestrings will mess you up.
fix it while the DB is small

[masterOFwww]

This is a script for a paid to click site, where i sell referrals, and i want to sell only those that were active last 48h...

And $lastlogdate is used accross all the script (10 times in admin panel, 5 times in main site)...but if this is better ill have to change it...

One question: Does date(lastlogin) displays hours and minutes too? Cause script has a feature to detect double accounts by last log in time e.g. if ten accounts log in one after another it displays them and i can investigate it further...

BTW i always add +rep for help :)

[Will Gresham]

Best way to convert the dates:

// Get the data from the database
$new_date = strtotime($date_from_db);
// Insert the date back into the database

It would be worth checking the date is converted correctly before updating the db, if all your dates are in the same format then you should check a random 2 or 3.

[almostbob]

This is a script for a paid to click site, where i sell referrals, and i want to sell only those that were active last 48h...

And $lastlogdate is used accross all the script (10 times in admin panel, 5 times in main site)...but if this is better ill have to change it...

One question: Does date(lastlogin) displays hours and minutes too? Cause script has a feature to detect double accounts by last log in time e.g. if ten accounts log in one after another it displays them and i can investigate it further...

BTW i always add +rep for help :)

Datelastlogin if stored as the timestamp, stores all the way to milliseconds (precision +-3milliseconds in sql (I think))

used multiple times in the site, a lot of lookups makes timestamp even more promising,

have you considered session or cookie to store the datetime so you only access the DB once

you just extract the part you need to analyse using php inbuilt date time handling functions
where you use F j, Y - g:i a
you can use date(F j, Y - g:i a, $datelastlogin)
or date_add or date_diff or time
the timestamp is much more versatile
you could update your table layout so the date field is a 10digit numeric instead of text,blob,varchar, or whatever it is now, and gain speed and smaller DB size

Not so much for 3000, but 300000 * 250bytes(blob) smaller is a V.smaller db
besides when you're rich n famous I want to be able to bask in reflected glory,

"I helped him get started" -slinks away giggling (insanely)

"Fun with Sig blocks"