Hey guys.I'm working on a new infusion that will be compatible with PiF as well as regular 7.01 ... If anyone can help me I sure would appreciate it. I've run into a problem that I cannot seem to solve. No one at Mods-Net seems to be able (or willing I'm not sure which), to help out and I thought I'd try here. It should be simple and I thought it was but here is my problem:
I need to extract from the database, the usernames that logged on the PREVIOUS day using the 'user_lastvisit' field. Here is a code snippet where the problem lies:
Code
<?php
$yesterday = date("m-d-Y", mktime(0, 0, 0, date("m"), date("d")-1, date("Y")));
$visited = $yesterday;
$result = dbquery("SELECT user_id, user_name, DATE_FORMAT(user_lastvisit, '$visited') AS my_day FROM ".DB_USERS." WHERE user_lastvisit = '$my_day' AND user_status='0' ORDER BY user_lastvisit DESC");
When I preview this in a custom page it displays 88 names from my userbase when in fact I know (at least today), that it should display 3 names as there were 3 logins yesterday. Time/Date conversions have always driven me crazy trying to figure out! I know the problem must be in the way I'm telling MySql to read the data but I just cannot figure it out. I've tried many many different configurations here and none seem to work right.
PS: The echo $visited; at the bottom DOES display yesterdays date correctly i.e.
06-22-2010
keddy wrote:
I'll try to solve your problem tomorrow morning. Like you said... should be something simple but... isn't.
Hey keddy. I appreciate your attempt here but I think I have finally solved it and it WAS very simple actually. I did a LOT of online research and finally found the answer on a phpBB board. This is the final result which DOES work and displays users who logged on yesterday(last 24hrs):
Code
<?php
$result = dbquery("SELECT user_id, user_name, user_lastvisit FROM ".DB_USERS." WHERE user_lastvisit >= (UNIX_TIMESTAMP(CURDATE()) -86400) AND user_status = '0' ORDER BY user_lastvisit DESC");
echo "[";
$i = 0;
if (dbrows($result) != 0) {
while ($data = dbarray($result)) {
$i++;
echo "<a href='".BASEDIR."profile.php?lookup=".$data['user_id']."' title='".$data['user_name']."'>".$data['user_name']."</a>";
if ($i < dbrows($result)) { echo "] ["; }
else { echo ""; }
}
}
echo "]\n";
?>
Turns out there is no need for esoteric/manipulative date/time conversions as the (UNIX_TIMESTAMP(CURDATE()) -86400) function does it all. You simply subtracting 86400 seconds from the 'user_lastvisit' field which is from midnight of the previous day to midnight lastnight. If you preview this in a custom page you'll see that it works correctly.
Thanks again for volunteering to help. I can now finish my center panel.
Thanks; but actually after several days of observation, the above code doesn't quite work right. The following code however does work:
Code
$result = dbquery("SELECT user_id, user_name, user_lastvisit FROM ".DB_USERS." WHERE user_lastvisit > (UNIX_TIMESTAMP(CURDATE()) -86400) AND user_lastvisit < (UNIX_TIMESTAMP(CURDATE())) AND user_status = '0' ORDER BY user_lastvisit DESC");
echo "[";
$i = 0;
if (dbrows($result) != 0) {
while ($data = dbarray($result)) {
$i++;
echo "<a href='".BASEDIR."profile.php?lookup=".$data['user_id']."' title='".$data['user_name']."'>".$data['user_name']."</a>";
if ($i < dbrows($result)) { echo "] ["; }
else { echo ""; }
}
}
I needed to be able to specify a range. Supposedly there is a MySql command called BETWEEN but I could find no reference or examples to it and figured out my own method of range specification.
