Can you help me with DATE() formatting

jami3

Yes im stuck on what i thought i knew how to do...
Well my problem is,
I am making a bloging script and i post the date automatically using NOW() function

		  <?php					
require("conn.php");

if($_POST['btnAdd'] != "")
{
$sql = "INSERT INTO blogrollentries (entryTitle, entryText, entryDate) VALUES ('" . $_POST['entryTitle'] . "','" . $_POST['entryText'] . "', NOW())";
if (mysql_query($sql, $conn))
{
$new = true;
}
}

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Jamie Thompson &raquo Blog|Roll</title>
</head>

<body>
<?php if($new == ture)
{?>
<strong>Your New Entry Has Been Added.</strong>
<?php
}?>
<form method="post" action="post.php">
<input name="entryTitle" id"entryTitle"/>
<textarea name="entryText" id="entryText"> </textarea>
<input type="submit" name="btnAdd" value="Add Entry"/>
</form>
</body>
</html>

Thats the code
and then i display it i formatt it and then display it

<?php					
require("conn.php");
$result = mysql_query("SELECT entryID, entryTitle, entryText, Unix_TIMESTAMP(entryDate) AS theDate FROM blogrollentries ORDER By entryDate DESC LIMIT 10");

if ( mysql_num_rows($result) > 0 ) {
while ($row = mysql_fetch_array($result)){
$theDate = date("l jS F Y", $row['theDate']); ?>

Then later on i call the date
<?=$row['theDate'] ?>
But instead of getting 5th of november 2005 i get

1131148800

Any idears what is wrong
Thanks alot
Jamie

devinemke

jami3 wrote:
But instead of getting 5th of november 2005 i get 1131148800

because you are using UNIX_TIMESTAMP() in your SELECT query. you need to use DATE_FORMAT() to format the date any way you want. read up on mySQL's Date and Time Functions.

jami3

devinemke wrote:
because you are using UNIX_TIMESTAMP() in your SELECT query. you need to use DATE_FORMAT() to format the date any way you want. read up on mySQL's Date and Time Functions.

Oh okay, im not the best with mySQL, can i just format it in the exact same way os it looks like

$theDate = date("%D %M %Y", $row['theDate']);

or is that completley wrong?
Thanks

devinemke

the point i was making is that you should do the date formatting with mySQL, not with PHP. follow that link i posted and look at the DATE_FORMAT() function.

jami3

Okie dokie,
I kind of got lost reading that im not very good at mySQL.
But i have manged to get it working 😮

<?php					
require("conn.php");
$result = mysql_query("SELECT entryID, entryTitle, entryText, UNIX_TIMESTAMP(entryDate) AS theDate FROM blogrollentries ORDER By entryDate DESC LIMIT 10");

if ( mysql_num_rows($result) > 0 ) {
while ($row = mysql_fetch_array($result)){
$theDate = mysql_query($$result); 
$EntryUnixDate = $theDate['unixdate']; 
$EntryRealDate = date("jS of F, Y", $EntryUnixDate);
?>

That is my current code, and when i insert the date into entryDate i use the NOW() function. Then i print it out echo "$EntryRealDate".
All fine and dandy BUT
I get the following result
Posted On: 1st of January, 1970
rather than
Posted On: 5th of November, 2005
Now what could my problem be?
Any idears
Regards, Jamie

devinemke

$result = mysql_query("SELECT entryID, entryTitle, entryText, DATE_FORMAT(entryDate, '%D of %M, %Y') AS theDate FROM blogrollentries ORDER By entryDate DESC LIMIT 10") or exit(mysql_error());
if (mysql_num_rows($result))
{ 
	while ($row = mysql_fetch_assoc($result))
	{
		foreach ($row as $key => $value)
		{ 
			echo $key . ' = ' . $value . '<br>';		
		}
		echo '<hr>';
	}
}

jami3

I will rephrase my question,
Can you show me how to change a timestamp set in a mySQL database using the NOW() function
It currently sats 2005-11-05 i think and i would like it to be displated like
5th of November 2005
Thanks

jami3

oh what are the chanced of us both posting at the same time 😛
ill chech your method

jami3

It worked fine thankyou very much, now i will try to implement it into my script 😛
Thanks again
Goodnight
Jamie