sort by mysql timestamp

[jimisrv]

Hi all- I'm having some trouble with mysql timestamps. I'm trying to sort between 2 dates:

A user selects a month and a year for the start date and a month and a year for the end date. (example: 01/2003 to 10/2003) The page will then return a list of entries that were timestamped between those 2 dates.

The timestamp is timestamp(14) in the mysql db. An example timestamp is: 20031213155111. The month can be passed either as the full name (January, February, etc.) or as a number (01, 02, etc....they may still be treated as characters rather than integers by PHP, I'm not sure). I'm guessing the latter would be better. Both month and year are passed in input fields:

<select name="startmonth"> (or <select name="endmonth"> )
<? while ($monthlist = mysql_fetch_array($query_monthlist)) { ?>
<option value="<?=$monthlist[monthid]?>"><?=$monthlist[monthid]?>
<? } ?>
</select>

<select name="startyear"> (or <select name="endyear"> )
<option value="2003">2003
</select>

What I need is the SQL statement that will use the timestamp(14) field (paydate) to pull all entries between the 2 entered dates.

Thanks...

[tomwerner]

If I understand you correctly you want an SQL statement that returns rows between two timestamps, if so then here is the required code, I found it on google so remember that it is always a resource available, I dare say that this has been asked on this board aswell, anyway here goes:

SELECT * FROM table_name WHERE `timestamp_column` BETWEEN 'timestamp1' AND 'timestamp2';

Remember that you need to make the timestamp before you do the query because obviously

BETWEEN '02-2005' AND '05-2005'; 

wouldn't work. In PHP timestamps are created using mktime(), I think so anyway, the description is 'create a UNIX timestamp'.

hth