8 replies to this topic

[shub]

hello,

I wanne convert the mysql date to a nice one. I know how mysql saves there dates but they are ugly... I tryed some stuff i found on the net but it all didnt work out... its for a guest book

Here is my code of the display of the guestbook:

<?php
	require_once('db/db_connection.php');

	$sql = "SELECT * FROM guestbook ORDER BY insertdate DESC";
	$result = $MyDb->f_ExecuteSql($sql);
	$recordcount = $MyDb->f_GetSelectedRows();
	
?>
		<h2>Gasten Boek</h2>
		<a href="add.php"><img src="images/reply.gif" height="17" width="107" border="0" /></a>
		<br />
		
<?php while ($row = $MyDb->f_GetRecord($result)) { ?>
		
		<table cellpadding="0" cellspacing="0" class="gb" border="0">
		<tr height="15">
			<td width="100"><?php echo $row['name']; ?></td>
			<td width="500" align="right"><?php echo $row['insertdate']; ?></td>
		</tr>
		<tr>
			<td width="100"></td>
			<td width="500"><?php echo nl2br($row['text']); ?></td>
		</tr>
		</table>
		<br />

<?php } ?>

Now here is the code where the date is saved to the mysql

<?php
	require_once('db/db_connection.php');
	
	if (isset($_POST['submitBtn'])) {
		 $name	 = (isset($_POST['name'])) ? htmlentities($_POST['name']) : '';
		 $comment  = (isset($_POST['comment'])) ? htmlentities($_POST['comment']) : '';
		 $actDate  = date("Y-m-j G:i");
		 
		 //Minimum name and comment length.
		 if ((strlen($name) > 2) && (strlen($comment) > 5)){
			 $sql = "INSERT INTO guestbook (name,text,insertdate) VALUES (";
			 $sql .= "'".$name."','".$comment."','".$actDate."')";
			 $MyDb->f_ExecuteSql($sql);
		 }
		 
		 header("Location: index.php");
	}
	else {

?>

This is the sql file i ran for my database:

CREATE TABLE `guestbook` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `text` text,
  `insertdate` datetime default NULL,
  `location` varchar(100) default NULL,
  `web` varchar(100) default NULL,
  `email` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
);

If somebody can help me please tell me what i have to do and where i have to put what... I really dont know anything from php, total noob at it...

Thnx for the help

~Shub

Edited by shub, 27 July 2007 - 12:44 PM.

[dotbart]

Hey,
I've been struggling with that problem for a long time. The solution i found was really simple:

Instead of saving your time as a DATETIME in mysql, use an INT field.
Then in PHP use the time() function to genereate that INT. Convert it back using date() function when retrieving it!

it's that easy


B

[shub]

Hey,
I've been struggling with that problem for a long time. The solution i found was really simple:

Instead of saving your time as a DATETIME in mysql, use an INT field.
Then in PHP use the time() function to genereate that INT. Convert it back using date() function when retrieving it!

it's that easy


B

Thnx for your help...

But heu what do you change on my code then lol? can you retype my code and then say what i have to change in what? im just started whit php (this code isnt my code, just editing it)

~Shub

[Temple]

Change your query like so:

$sql = "SELECT *, DATE_FORMATE(insertdate, '%Y-%m-%j %T') as insertdate FROM guestbook ORDER BY insertdate DESC";

That's it. You can customize the format, read up on the types: http://www.webfooted...mat-num-75.html

[shub]

Change your query like so:

$sql = "SELECT *, DATE_FORMATE(insertdate, '%Y-%m-%j %T') as insertdate FROM guestbook ORDER BY insertdate DESC";

That's it. You can customize the format, read up on the types: http://www.webfooted...mat-num-75.html

Srry dude didnt work... then my guest book didnt display anything

~Shub

[pirateXcore]

Why don't you just use the date() function and save it in either an int or varchar field(if you want to format it to look pretty...like 7/28/2007)

[Demonslay]

The reason Template's code didn't work is because there is a typo. Its not 'DATE_FORMATE'. Simply drop the 'E', and it would work just fine.

If you want to leave MySQL alone and let it save however it wants with the NOW() function (I personally just find this simpler), you can use this function to format it with PHP again.

// Format Datetime Stamp Function
function formatDate($val, $format = NULL){
	list($date, $time) = explode(' ', $val);
	list($year, $month, $day) = explode('-', $date);
	list($hour, $minute, $second) = explode(':', $time);
	return date((!is_null($format) ? $format : 'l, m/j/y h:iA'), mktime((int)$hour, (int)$minute, (int)$second, (int)$month, (int)$day, (int)$year));
}

Simply use that on the date pulled from the database, and you're good to go. Includes an optional second parameter for customizing the format given to the date() function.

[AvengeX]

Oh come on guys, wake up!! Use the UNIX_TIMESTAMP function in your query. It's much easier than all that crazy function rubbish. Example query:

SELECT name, UNIX_TIMESTAMP(lastseen) AS seenStamp FROM members

Now assuming you did the usual mysql_fetch_assoc stuff, you'd be able to access your timestamp with $member['seenStamp'] and manipulate it like you would with the date function like so:

echo date('d/m/y', $member['seenStamp'])

Oh and...

Why don't you just use the date() function and save it in either an int or varchar field(if you want to format it to look pretty...like 7/28/2007)

Because that method is inefficient and isn't designed for holding dates, whereas using datetime is. Don't EVER tell ANYBODY to use int or varchar for dates, as that is a sin. You can do so much more with datetime it's pretty impressive. Look it up on dev.mysql.com.

Edited by AvengeX, 30 July 2007 - 07:54 PM.

[Meshuggah-X]

Why don't you just use the date() function and save it in either an int or varchar field(if you want to format it to look pretty...like 7/28/2007)

Because that method is inefficient and isn't designed for holding dates, whereas using datetime is. Don't EVER tell ANYBODY to use int or varchar for dates, as that is a sin. You can do so much more with datetime it's pretty impressive. Look it up on dev.mysql.com.

Yes. And what happen if you want to sort the data in the database according to the date. If you saved it as Int or Varchar that isn't possible.
And you can easily change the format of the date with PHP och directly in the SQL-question.