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.

Could you clarify "is this the right way to do it"? Are you achieving the results that you want? If not, what results do you want to achieve? — Thomas Owens
– Thomas Owens, Commented Dec 2, 2008 at 12:48
you may want ot add mysql_real_escape_string to you posts to stop injection — Re0sless
– Re0sless, Commented Dec 2, 2008 at 12:55

Tim · Accepted Answer · 2008-12-02 12:59:23Z

6

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.

edited Dec 2, 2008 at 12:59

answered Dec 2, 2008 at 12:51

Tim

8135 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

benlumley Over a year ago

DATE_SUB / DATE_ADD may help with this - just like php's strtotime('+4 days') or using mktime, it lets you forget about how many days in a month.

suitedupgeek · Accepted Answer · 2008-12-02 13:04:45Z

0

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 !!!

answered Dec 2, 2008 at 13:04

suitedupgeek

8853 gold badges10 silver badges19 bronze badges

Comments

Piskvor left the building · Accepted Answer · 2008-12-02 13:07:26Z

0

"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"

answered Dec 2, 2008 at 13:07

Piskvor left the building

93.1k46 gold badges182 silver badges226 bronze badges

Collectives™ on Stack Overflow

php mysql date/time question

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related