Auto Date & Time.. Search & Display

[stevesoler]

Hello everyone! This is regarding PHP & MySQL.

I'm creating a web form for users to enter their comments and submit the form which will add the comments to a MySQL table. I would then be able to access an admin form that will allow me to retrieve the submitted records of comments.

My Questions:

In the web user's form how can I automatically insert the date & time that they submitted the comment into a column or columns (date & time in one field or separate fields) of the database record?

In the admin form how can I be able to search for submitted comments by specific date and date ranges (using entry fields) or by pre-determined date ranges (last 7 days, last 14 days, last 30 days, last 2 months, etc.. using a pop down menu)? Note: I would not need to search by time, but would like records sorted by date & time.

Finally, when the results are retrieved on both the list as well as the detailed record page, how can I display the date and time that was automatically inserted so that it displays in a readable format (Sat. Jan. 5, 2002, 3:15 PM)?

Many thanks to anyone who can help me with this!
- Steve

Just incase you need to know.. my current setup is:
Solaris 2.7 release 05/99
Apache 1.3.3
MySQL Version 11.15 Distribution 3.23.43
PHP 4.1.0 Apache Module

[Anon]

1. Create a column in your mysql table of type DATETIME. I.e. CREATE TABLE tbl_name (posted DATETIME)

2. Once column is created, insert current time into it using NOW(). I.e. INSERT INTO tbl_name(posted) VALUES(NOW())

3. You can retrieve messages within specific date ranges using mysql's functions TO_DAYS(), DATE_ADD(), DATE_SUB(), INTERVAL, etc. You need to do some homework on that yourself. http://www.mysql.com/doc/D/a/Date_and_time_functions.html

4. Using now() to post current date into mysql will post a date in the format: YYYY-MM-DD hh:mm:ss

So let's say you want to convert 2002-06-30 16:08:49 into the format Sun, 30 Jun 2002 16:08:49, just explode() and rearrange:

$i = explode(" ", $posted);
$j = explode(":", $i[1]);
$k = explode("-", $i[0]);
$posted = date("D, d M Y H:i:s", mktime($j[0],$j[1],$j[2],$k[1],$k[2],$k[0]));

echo "$posted";

Richie.
geocities.com/akindele/