"Date Added" feature question

how to send Date of the moment the user sent information to the MySQL DB?

[cosmoyoda]

<?php echo "Hello World!";?>

You know that little feature we see today almost on every dynamic page we visit, where we can find the date of when the record (video, article etc) has been added to the website? I was wondering how I could do the same thing with PHP and MySQL. I'm new to Web Development, so excuse me for my lack of experience.

I have a table in my database and there is a field called "Date Added". I don't even know which option (TEXT, INT, VARCHAR, etc...) I should choose to achieve what I'm looking for. I'm using Dreamweaver, which is a good thing and its easy to give the date a nice format (for instance, Monday, December 28th 2007). But what I don't know is what PHP function I should use on my code to post AUTOMATICALLY the date of the exact moment the user clicked the buttom (Register) so that way I can look at my Admin Page and see on what day and time the user signed up for the website.

I know this is stupidly easy to do, but I don't know how to. Hopefully you guys can help me out.

Thanks a bunch for your help! It's highly appreciated!

[willybfriendly]

PHP date functions [us3.php.net] and MySQL date functions [dev.mysql.com] handle dates a bit differently from each other, so plan accordingly.

Some like to use the MySQL native functions and convert to PHP formats. Others like to work solely in PHP and store the dates in PHP format.

[FourDegreez]

For this type of thing, I set the column in MySQL to have the default value CURRENT_TIMESTAMP. When you do an insert, don't set any value for this field. It will automatically be set to the current time.

[cosmoyoda]

willybfriendly, thanks for the function library. That will help me work with date/time on PHP

FourDegreez, thats exactly what I need for the little Date Added feature. I guess I can make the "DateAdded field" a hidden field and set it on phpMyAdmin as CURRENT_TIMESTAMP. When the user hits the insert buttom the current date and time will be sent to the database.

Howewer, is the date and time going to be the local time in the user's machine or my local time? Is there a way to make that only ONE local time for everything in my database, so there is no confusion? Also, is it possible to change the format of the date on my recordset using PHP to make the DateAdded field more human friendly, like for instance: December 31th 2007?

Thanks for your help and happy new year!

[brancook]

I believe that it would be the time from the server. I think you would have to use javascript to get the time from the users computer.

Happy new year!

[cosmoyoda]

Great, that makes sense!
A good example is this forum. The date and time of all the posts in this forum are using the time zone of the servers, and not from my local area.

Thank you for the tip, brancook!

[cosmoyoda]

Hello people,

I have recently created a nice script that will convert a TIMESTAMP of the format 2008-03-23 14:00:00, to a more friendly format: March 23rd, 2008 at 2:00pm.

This is pretty cool, I'm using this a lot on my site and I hope someone will find it useful. You're free to improve it. Enjoy it! =)

<?php

// Displays the current time
$date_now = date('Y-m-d G:i:s');

// MySQL's DateTime Format: 2008-03-21 12:42:00
// Needs to be converted to a more friendly format: March 21st, 2008 at 12:42am

function DateTimeConverter($datetime) {

// $your_date_here = '2008-03-21 12:42:00';
// Current Date = date('Y-m-d G:i:s');

$datetime_timestamp = $datetime;

// Separate DATE from TIME.
$datetime_explode = explode(" ", $datetime_timestamp);
$date = $datetime_explode[0]; // Variable of DATE
$time = $datetime_explode; // Variable of TIME

// Separate DATE components ---> 2008-03-21
$date_explode = explode("-", $date);
$year = $date_explode[0]; // Variable of YEAR
$month = $date_explode[1]; // Variable of MONTH
$day = $date_explode[2]; // Variable of DAY

// Separate TIME components ---> 12:42:00
$time_explode = explode(":", $time);
$hour = $time_explode[0]; // Variable of HOURS
$minute = $time_explode[1]; // Variable of MINUTES
$second = $time_explode[2]; // Variable of SECONDS

if ($hour == 24) {
$hour_12 = '00';
} elseif ($hour > 12) {
$hour_12 = $hour - 12;
}

if ($hour <= 12) {
$hour_complete = $hour . ":" . $minute . "am";
} elseif ($hour > 12) {
$hour_complete = $hour_12 . ":" . $minute . "pm";
}

$day_last = substr($day, -1, 1);

if ($day_last == 1) {
$day_end_str = 'st';
} elseif ($day_last == 2) {
$day_end_str = 'nd';
} elseif ($day_last == 3) {
$day_end_str = 'rd';
} elseif ($day_last == 4) {
$day_end_str = 'th';
} elseif ($day_last == 5) {
$day_end_str = 'th';
} elseif ($day_last == 6) {
$day_end_str = 'th';
} elseif ($day_last == 7) {
$day_end_str = 'th';
} elseif ($day_last == 8) {
$day_end_str = 'th';
} elseif ($day_last == 9) {
$day_end_str = 'th';
} elseif ($day_last == 0) {
$day_end_str = 'th';
}

$day_complete = $day . "^{" . $day_end_str . "}";

if ($month == 01) {
$month_complete = 'January';
} elseif ($month == 02) {
$month_complete = 'February';
} elseif ($month == 03) {
$month_complete = 'March';
} elseif ($month == 04) {
$month_complete = 'April';
} elseif ($month == 05) {
$month_complete = 'May';
} elseif ($month == 06) {
$month_complete = 'June';
} elseif ($month == 07) {
$month_complete = 'July';
} elseif ($month == 8) {
$month_complete = 'August';
} elseif ($month == 9) {
$month_complete = 'September';
} elseif ($month == 10) {
$month_complete = 'October';
} elseif ($month == 11) {
$month_complete = 'November';
} elseif ($month == 12) {
$month_complete = 'December';
}

// Output the final Date and Time
echo $month_complete . " " . $day_complete. "," . " " . $year . " at " . $hour_complete;

};
// END of DateTimeConverter Function

echo DateTimeConverter('2008-03-21 12:42:00');

?>

[1][edited by: cosmoyoda at 8:26 pm (utc) on Mar. 23, 2008]

[cosmoyoda]

All you need to do to use the function above is to call:

DateTimeConverter('your_date_time_here');

Your date and time TIMESTAMP must be in the following format: 0000-00-00 00:00:00

[coopster]

You're free to improve it.

I commend you for taking the time and effort to apply what you have read and learn. That, my friend, is awesome. Now let us give something to you. By combining two PHP built-in functions you can accomplish what your user-defined function is doing:

$time = '2008-03-23 14:00:00'; 
print date [php.net]('F j<\s\u\p>S</\s\u\p>, Y \a\t g:ia', strtotime [php.net]($time)) . "\n";

MySQL itself also has built-in Date and Time Functions [dev.mysql.com] that you could use to format your string too.

Server-side scripting languages often offer us many different ways to complete tasks. Learning to write your own functions is going to be worth it's weight in gold when you really get rolling. Knowing what pre-defined functions are available and how they can be used comes with time, with experience.

[coopster]

By the way, here is another neat little function for returning the English ordinal suffix [webmasterworld.com] for numbers. Although you have features available for dates in both MySQL and PHP, you may find at some point you need something beyond dates ...

[cosmoyoda]

Hey coopster, that is so much better! It's probably 30 times shorter than my code. I should have asked you if there was a pre-defined function for my DateTimeConverter() function, but at least I got to try and mess my hands with some PHP and test my skills. I'm creating many functions on my own now and yes it's becoming very very handy.

I'm still a Padawan, but I'm learning a lot thanks to my effort and this wonderful forum!

Thanks again, I'll use your function for now on.