Inserting date into mysql table & date format

[JamesCliff]

Hi guys.

I have a mysql table called shout. Its designed in the following way:

CREATE TABLE `shout` (
  `id` int(5) NOT NULL auto_increment,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `message` text NOT NULL,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `message` (`message`)
) TYPE=MyISAM;

I use the following php script to populate the table:

<?php



if(isset($_POST['add']))
{

$host = 'HOST'; 
$user = 'USER'; 
$pass = 'PASS'; 
$db = 'DB'; 

$connection = mysql_connect($host, $user, $pass) or die ("Unable to
connect!");
mysql_select_db($db) or die ("Unable to select database!");

$message = $_POST['message'];
$name = $_POST['name'];

$query = "INSERT INTO shout(id, date, message, name) VALUES('','NOW()','$message','$name')";
mysql_query($query) or die('Error, insert query failed');

header('Location: index.php?page=admin/shout/shout');
}
else
{
?>
<form method="post">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr> 
<td>Message:</td>
</tr>
<tr> 
<td><input name="message" type="text" id="message"></td>
</tr>
<tr> 
                <td>&nbsp;</td>
</tr>
<tr> 
<td>Name:</td>
</tr>
<tr> 
<td><input name="name" type="text" id="name"></td>
</tr>
<tr> 
<td width="100">&nbsp;</td>

</tr>
<tr> 

<td><input name="add" type="submit" id="add" value="Post Message"></td>
</tr>
</table>
</form>
          <?php
}

?>

However when i run the above script all is entered into the table as it should be except for the date. I get the default 0000-00-00 00:00:00 in every single field entry, when i should be getting the actual date and time. Ive used the NOW() function within the insert code as im supposed to, but for some reason it just wont insert the date and time into the table.

Also when i manage to get it working, how would i display the date and time on a php script as in the following way: Sep 23rd, 2002 at 01:09:30 hrs.

How would i go about formatting it in that way and implementing it into a php script to display like that instead of just showing the 0000-00-00 00:00:00 (when it works) format which is called from the database?

Help on both of these issues is greatly appriciated.

Thanks alot

Jim

 

[kenrbnsn]

That's because you're trying to insert the string "NOW()" into the field instead of a formatted time. Try using NOW() without the single quotes or use the date() function to get the time via PHP.

$query = "INSERT INTO shout(id, date, message, name) VALUES('',NOW(),'$message','$name')";

or

$query = "INSERT INTO shout(id, date, message, name) VALUES('','" . date('Y-m-d H:i:s') ."','$message','$name')";

Ken

 

[flubbard]

Another way to do this, if you always want the current time of the insert, would be to make a timestamp. Create the field with type TIMESTAMP. The current time will automatically be stored upon insert. You can specify the format of the timestamp (so that you store just date, or date - hour - minute - second).

I find that this is a little easier since your script will not have to hand the time formatting.

- flubbard

 

[kenrbnsn]

But doesn't the TIMESTAMP typed field change to the current date/time every time you update the record? The DATETIME field only changes when you want it to.

Ken

 

[flubbard]

If you would like to just have the update field, you can define your table with two timestamp fields. The first one (by default) updates whenever you insert or change the record. The second will only update upon command.

Here's how it works.

database:
create table my_table (data varchar(30), update_time timestamp, orig_time timestamp);

insert a record:
insert into my_table (data, orig_time) values ("my data", null);

update a record:
update my_table set data="new data" where data="old data";

In this case, the update_time will always update, but the orig_time field will hold the time of when it was passed null (upon your insert). I just tried this and it seems to work.

- flubbard

 

[JamesCliff]

Ok thanks guys.

I am using the 'datetime' field type in table.

That got it working..... However on my select script where i view data from the table it shows the date and time in the exact format it is in the table. So the date and time are showed like 2005-06-22 15:41:15

How do i alter the script to make the date and time display like Wednesday Sep 23rd, 2002 at 01:09:30 hrs?

Thanks alot

Jim

 

[kenrbnsn]

Look at the date() function

http://www.php.net/date

and the strtotime() function

http://www.php.net/strtotime

Ken

 

[Vragabond]

Or you can look into formatting the date within MySQL. Look here:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id2637996