php mysql date/time question

Question

I've built a small application which has User Management, a frontend console to enter data and a backend console to control parts of the frontend. The frontend adds rows to a MySQL database which are timestamped. The backend needs to be able to select rows from the database between X and Y dates.

Everything works so far, except the date part which I'm really struggling with.

The frontend SQL input looks like this (simplified with spurious code removed):

$date = time();
$top_level_category = $_POST['top_level_category'];
$sub_level_category = $_POST['sub_level_category'];
$company = $_POST['company'];
$agent_name = $_POST['agent_name'];
$ticket_id = $_POST['ticket_id'];

$sql = "INSERT INTO dacc_data ("
     .     "id, top_level_category, sub_level_category, "
     .     "agent_name, date, ticket_id, company"
     . ") VALUES ("
     .     "NULL, '$top_level_category', '$sub_level_category', "
     .     "'$agent_name', FROM_UNIXTIME('$date'), '$ticket_id', '$company'"
     . ")"
;

$result = mysql_query($sql) or die (mysql_error());

That seems to work ok, the timestamp is being picked up and added to a DATETIME column in my table. It displays as dd/mm/yyyy hh:mm:ss within the database.

So ... my first question is - is this the right way to do it?

The second question being, what sort of SQL statement would I need to pull out an array of rows between X and Y date.

Apologies if this is rambling a bit, hope it's clear but if you need more information let me know.

Answer 1

MySQL datetime should be formatted with dashes:

YYYY-MM-DD HH:MM:SS

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Then you can query for date ranges a couple of ways:

select *
from table
where date >= '[start date]' and date <= '[end date]';

or

select * 
from table 
where date between '[start date]' and '[end date]';

where "table" is the name of your database table and "date" is the name of your datetime field.

Answer 2

You are correct. I can confirm that the Database has "YYYY-MM-DD HH:MM:SS" - I am using SQLWave editor for browsing the DB quickly, it auto-formats the DATETIME column.

// Initial questions still stand :)

Or not, just noticed you updated the answer - thank you very much! I had actually tried that very same query several times to no avail, mainly because my WHERE was specifying the date format incorrectly. Misled by SQLWave :(

Back to using command line from now on !!!

Answer 3

"what sort of SQL statement would I need to pull out an array of rows between X and Y date?"

SELECT * FROM `dacc_data` where `date` between "2008-11-01" and "2008-12-01"