[RESOLVED] Mysql date search help

[bfulda]

Hey all,

I am using mysql 5.0,apache2 and php. My table, called sdata, looks like this:

mysql> describe sdata;
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | varchar(6) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| server | varchar(25) | YES | | NULL | |
| description | varchar(8000) | YES | | NULL | |
| datetime | datetime | NO | | NULL | |
| event | varchar(25) | YES | | NULL | |
| refnum | mediumint(9) | NO | PRI | NULL | auto_increment |
+-------------+---------------+------+-----+---------+----------------+
7 rows in set (0.04 sec)

the datetime function will spit out dates as 'yyyy-mm-dd hh:mm:ss'


I need to be able to perform a search between two complete dates (between two different datetime's) and pull all data from the table between those two dates.

performing this only pulls up the last 30 records as far as I can tell:

mysql> select * from sdata order by sdata.datetime desc limit 30;

But when I do this, it will pull for 30 days worth of data, but the scope is to limited:

mysql> select * from sdata where name="Brad" order by sdata.datetime desc limit 30;

As was stated earlier between two completely different dates. So as you can see I'm not even close yet.

Im just trying to get a feel for dates in mysql and so far this part of my project is probably the most challenging so far. Any ideas? tia

brad dot fulda at logsa dot army dot mil

thanks,
Brad

[NightShift58]

Instead of:select * from sdata order by sdata.datetime desc limit 30;
// OR //
select * from sdata where name="Brad" order by sdata.datetime desc limit 30;do something like:select * from sdata WHERE sdata.datetime BETWEEN '$date1' AND '$date2' order by sdata.datetime desc;
// OR //
select * from sdata where name='Brad' WHERE sdata.datetime BETWEEN '$date1' AND '$date2' order by sdata.datetime desc;By getting rid of "LIMIT 30", you rid yourself of a 30-record limitation and to select a range of dates, use "BETWEEN x AND y", whereby x and y are included in the selection.

[CarolineBogart]

The first query requests the last 30 records based on date alone. The second query requests the last 30 records involving Brad; this query has no date constraint. "Limit" always refers to the # of records, not "30 days from today." To get all the records for Brad time stamped in the last 30 days say:



<?php
$todayMinus30TS= date("Y-m-d", mktime(0,0,0,date("m"), date("d")-30, date("Y")));

select * from sdata
where
name="Brad"
and datetime<=getdate()
and datetime >= $todayMinus30TS;


?>



- I forget if $todayMinus30TS needs 'ticks'
- try not to call the field datetime as that's a reserved data type word

[NightShift58]

I need to be able to perform a search between two complete dates (between two different datetime's) and pull all data from the table between those two dates.The idea was to get rid of the "30" altogether...

[CarolineBogart]

The idea was to get rid of the "30" altogether...

This?
<?php
select * from sdata
where
name="Brad"
?>

This?
<?php
$todayMinus30TS= date("Y-m-d", mktime(0,0,0,date("m"), date("d")-30, date("Y")));

select * from sdata
where
datetime<=getdate()
and datetime >= $todayMinus30TS;


?>

[bfulda]

WOW! Thanks for all of the great replies!!!! You guys are awesome! I really appreciate this. I will be able to implement these suggestions later on in the day and I'll be sure to let you know the outcome. Thanks again to all!

Brad

[bfulda]

ONE MORE THING

I need to be able to have the input inputed by html, so I am confused as to how exactly I do this. Do I use pull down menu's for the day, month year for date1 and date2 or a field whereby they have to enter it a certain way, ie, yyyy-mm-dd. I guess that gets to just preference, or is there a best practice? TIA

[CarolineBogart]

ONE MORE THING

I need to be able to have the input inputed by html, so I am confused as to how exactly I do this. Do I use pull down menu's for the day, month year for date1 and date2 or a field whereby they have to enter it a certain way, ie, yyyy-mm-dd. I guess that gets to just preference, or is there a best practice? TIA
When you can solve something with a dropdown over a textbox then use a dropdown, because the user can't screw up the input!

I'll bet there are thousands of instances of date picking scripts / select-option HTML/javascript code snippets you could google.

You're very welcome :-)

[bfulda]

I was thinking that exact same thing about the drop down!!, Cool, thanks again you guys are awesome!!!

Brad

[bfulda]

BTW, Manchester is a great little town. I fly in there any time I have to go to Boston. I absolutely hate Logan. I have also been able to drive around there a little bit also. Beware of the police on hwy 3, very strict. And I had no idea moose were so big, geez, big critters they are!!

[CarolineBogart]

actually the moose are undercover cops :-)