MYSQL Time String By Today

[vfetty]

Ok normally I can handle this stuff but really need some help. I insert into mysql table a field called visit_time by using time() in PHP, ok so that is fine. Now how do I select from mysql today's date since the string I insert is so unique. Just want to grab all entries for today. Example of database record, field visit_date 1214088294. How would I use the Select statement? Thanks.

[badone]

This can be dependent on your Mysql version. Have a look here [dev.mysql.com...]

Cheers,
badone

[vfetty]

thanks for the reply. I read through that one before;) MySQL v. 5.0.45. Thanks for any help, I read the manual and every example I try fails:(

[rocknbil]

by using time()

I know this solution is **more** than you're asking, but things always tend to creep up and it's good to develop it to the fullest extent you might need it.

You **could** change this method so it uses the tools in mysql.

create table test (id primary key auto_increment, user_id int(11) not null, last_visit datetime() not null default '0000-00-00 00:00:00');

When someone logs in,

update test set last_visit = now() where user_id='123456';

now() will store both the date and time obviously, so it simplifies both tasks and makes your queries more robust. For example, if you construct date lists on your form - one for month, day, year - you can query any date or range. Create the following in your query form:

- radio buttons for all, on, before, after, or between dates
- Select lists for month, day, and year of start and end date. If you select on, before, or after, you use only the first list (start date).

$startdate = "$startyear-$startmonth-$startday";
$enddate = "$endyear-$endmonth-$endday";

if ($button == 'on') {
$where = " (last_visit >= '$startdate 00:00:00') and (last_visit <= '$startdate 12:59:59')";
}
else if ($button == 'before') {
$where = " last_visit < '$startdate 00:00:00';
}
else if ($button == 'after') {
$where = " last_visit > '$startdate 00:00:00';
}
else if ($button == 'between') {
$where = " (last_visit >= '$startdate 00:00:00') and (last_visit <= '$enddate 12:59:59')";
}

(Of course, if your button is set to ALL this would skip this entire construct of the $where, so $where is blank.)

$select = "select user_id,last_visit from test";
if ($where) { $select .= " where $where"; }
$select .= " order by last_visit desc";

Note the location of spaces in constructing the $where and adding it to the select, they are necessary, and you may want to construct something to add limit to the last bit of the statement.

Take it one step further, apply the same logic to a time list in your form and you can query specific times.

[OutdoorWebcams]

If you don't want to change things, you could try this:

$t = time();
$d = getdate($t);
// get timestamp of today, 12 a.m.
$daymin = $t - $d['hours']*3600 - $d['minutes']*60 - $d['seconds'];

Then your query could be something like
SELECT field FROM table WHERE visit_date>$daymin