Timestamp Not Displaying Correctly

[phpBeginner06]

I am using phpMyAdmin to help me with MySQL and I have a field that I am trying to use time stamp or datetime with. I was getting "0000-00-00 00:00:00" as the default for this field; so I went into phpMyAdmin and set this to a specific date and time. The specific data and time will show up in my datatable structure; but when I recieve data to this field; it still post as "0000-00-00 00:00:00". So I went into my PHP code and tried to set up the variable for this field to display like this:

[code]date("Y-m-d H:i:s", time() - (18000*1));[/code]

but this did not work either. Does anyone know why this might be occuring and how I might get it to stop occuring and actually post the right time and/or date? Will I have to alter mysql_querry also to make this display accurately?

[phpBeginner06]

I tried to put this in mysql_query, but then it just displayed datatable in my web page at  random with "0000-00-00 00:00:00" as my timestamp.

[code]SELECT UNIX_TIMESTAMP(datetime) As Time, datetime FROM table ORDER BY Time[/code]

This still did not seem to help me out any. Does any one know how to fix this problem or why it is even occuring?

[matto]

have you tried the mysql date_format() function ?

[phpBeginner06]

Do I use mysql date_format() instead of UNIX_TIMESTAMP() in mysql_query?

[matto]

Yes.

example:

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

would produce:

'Saturday October 1997'

check out mysql reference [url=http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html]http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html[/url]

[phpBeginner06]

I tried the example you provided, I am still getting the "0000-00-00 00:00:00" when the timestamp is posted.

Below is the mysql_query I adapted from your example; is this the correct way or did I put something in that is wrong?

[code]$result = mysql_query("SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') *  FROM messeges ORDER BY Time DESC");[/code]

[Jessica]

You're spelling messages wrong.

[DarkendSoul]

Slight question about this function... would you not have a changing date, not one that is going to always be [i]'1997-10-04 22:23:00'[/i] so wouldn't you put the value in the table you want to get the date from? I'm not too much in using complex mysql queries so I'm not sure.

[matto]

That would be correct

SELECT DATE_FORMAT(database_field, required_format) ...

[phpBeginner06]

OK - The reason for all these questions is that I am still working on a priviate messege system for a individual business. Basically if someone fills out an HTML form on a website; it will post to database/datatable. Then I have a page with a PHP Login/Password Gate (it is PHP Include from another PHP webpage) script and there is only 1 Login and 1 Password (this page is only set up for a single user; not multi-users). Once the the login occurs; all the data from the datatable; that has stored the form data, is displayed in this page. I am still trying to order by date and time recieved; like you would see in any email inbox. I have tried to set my "Time" field: first to TEXT, then to DATETIME, then to TIMESTAMP. I also have tried to set my default TIMESTAMP with a specific date and time. Thus far I have not been able to get the DATETIME or TIMESTAMP to display anything other then "0000-00-00 00:00:00". Right now I have set the "Time" field back to TEXT. Although I know that I cannot use the "ORDER BY" function properly, because I cannot order the AM/PM with TEXT. I know I can only order by Date and Time (in digits only - not AM/PM) this way. So below I have included the code for the datatable and all three pages that I am using to make this messege system. If anyone can take a look at this and tell me some of their ideas on how I can implement their ideas with this code; that would be great. Please try to tell me as simply as you can; my PHP & MySQL skills are very limited at this point (I am still learning).

[u]Datebase Data Table[/u]

[code]CREATE TABLE `messeges` (
 `Date` text NOT NULL,
 `Time` text NOT NULL,
 `Name` text NOT NULL,
 `Email` text NOT NULL,
 `Phone` text NOT NULL,
 `Comments` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;[/code]

[u]HTML Form Page[/u]

[code]<style>
BODY {font-family:arial}
INPUT {border:solid 1px black}
TEXTAREA {border:solid 1px black}
</style>
</head><body>
<table>
<form action="01-05-07.php" method="post">
<td align=left valign=top>
Name:
</td>
<td align=left valign=top>
<input type="text" name="Name" style="width:275px">
</td>
<tr>
<td align=left valign=top>
Email:
</td>
<td align=left valign=top>
<input type="text" name="Email" style="width:275px">
</td>
</tr>
<tr>
<td align=left valign=top>
Phone:
</td>
<td align=left valign=top>
<input type="text" name="Phone" style="width:275px">
</td>
</tr>
<tr>
<td align=left valign=top>
Comments:
</td>
<td align=left valign=top>
<textarea name="Comments" style="height:100px;width:275px"></textarea>
</td>
</tr>
<tr>
<td align=left valign=top style="padding-top:15px">
<input type="submit" value="Send">
<input type="reset">
</td>
</tr>
</form>
</table>[/code]


[u]PHP Page That Recieves Varaibles[/u]

[code]<script>
function noName()
{
alert("Please Enter Your Name");
history.go(-1);
}
function noEmail()
{
alert("Please Enter Your Email Address");
history.go(-1);
}
function noValid()
{
alert("Please Enter A Valid Email Address");
history.go(-1);
}
function noComments()
{
alert("Please Enter Your Comments");
history.go(-1);
}
function CommentsNotAllowed()
{
alert("You May Only Enter Letters & Numbers In Your Comments");
history.go(-1);
}
function checkedout()
{
alert("Thank You For Contacting Us");
document.location="01-05-07.html";
}
</script>
</head><body>
<?php

// Receiving variables
@$Name = addslashes($_POST['Name']);
@$Email = addslashes($_POST['Email']);
@$Phone = addslashes($_POST['Phone']);
// The Preg_Match Code Below Will Only Allow Letters And Numbers To Be Posted To The Database
if(!preg_match('/^[a-zA-Z0-9,;.!?\s]*$/', $_POST['Comments'])){
echo "<script> window.onload=function() { CommentsNotAllowed() } </script>";
exit;
}
$dated = date("m/d/Y");
$timed = date("g:i:s A", time()-(18000*1));

// Validation
if (strlen($Name) == 0 )
{
echo "<script> window.onload=function() { noName() } </script>";
exit;
}

if (strlen($Email) == 0 )
{
echo "<script> window.onload=function() { noEmail() } </script>";
exit;
}

if (! ereg('[A-Za-z0-9_-]+\@[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+', $Email))
{
echo "<script> window.onload=function() { noValid() } </script>";
exit;
}

if (strlen($Comments) == 0 )
{
echo "<script> window.onload=function() { noComments() } </script>";
exit;
}

//Sending Email to form owner

$db_host = "localhost";
$db_user = "username";
$db_pwd = "password";
$db_name = "CustomerEmails";
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);

mysql_query("INSERT INTO `messeges` (Date, Time, Name, Email, Phone, Comments) VALUES ('$dated', '$timed', '$Name', '$Email', '$Phone', '$Comments')");


echo "<script> window.onload=function() { checkedout() } </script>";

?>[/code]

[u]PHP Inbox Type Web Page[/u]

[code]<?php include("musthavepassword.php"); ?>
<html>
<head>
       
<title>Untitled</title>

<style>
BODY {font-family:arial bold}
</style>

</head>
<body>

<?php

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","username","password");

//select which database you want to edit
mysql_select_db("CustomerEmails");

//select the table
$result = mysql_query("select * from messeges order by Date, Time desc");

print "<center><table width=100% style='border-top:solid 1px gray;border-bottom:solid 1px gray'>";

if(mysql_num_rows($result) == 0){
print "<td style='border-top:solid 1px gray;border-bottom:solid 1px gray;color:red;font-weight:bold;padding-top:25px;padding-bottom:25px;font-size:110%;font-weight:bold'>\nYour Inbox Is Empty\n</td>";
}else {

//grab all the content
while($r=mysql_fetch_array($result))
{
  //the format is $variable = $r["nameofmysqlcolumn"];
  //modify these to match your mysql table columns

  $dated=$r["Date"];
  $timed=$r["Time"];
  $alapac=$r["SpecificTime"];  
  $Name=$r["Name"];
  $Email=$r["Email"];
  $Phone=$r["Phone"];
  $Comments=$r["Comments"];


//display the row
  print "\n<tr>\n<td style='border-top:solid 1px gray'>\n<div style='border-bottom:solid 1px gray;height:100%;width:100%;padding-top:20px;font-size:110%;font-weight:bold'>Date: $dated<br>\nTime: $timed<br>\nName: $Name<br>\nEmail: $Email<br>\nPhone: $Phone<br>\nComments: $Comments\n<div style='float:right;text-align:right;padding-top:10px' align='right'>\n<a href=\"mailto:$Email?subject=Email Response&body=<html></head><body><br><br><br>**************************************************<br>Orginal Messege from $Name<br>**************************************************<br><br>Name: $Name<br>Email: $Email<br>Phone: $Phone<br>Comments: $Comments</body></html>\" title=\"Click Here Too Reply To This Messege\" hidefocus=true><img src='r2m.gif' border=0></a>\n<span style='width:25px'><!--button spacer--></span>\n<a href=\"01-05-07_v4.0.php?Date=$dated&Time=$timed&Name=$Name&Email=$Email&Phone=$Phone&Comments=$Comments\" onclick=\"return confirm('Are You Sure You Want To Delete This Messege?')\" title=\"Click Here Too Delete This Messege\" hidefocus=true><img src='dmr.gif' border=0></a>\n<span style='width:25px'><!--button spacer--></span>\n<a href=\"mailto:$Email?subject=Email Response&body=%0A%0A%0A**************************************************%0AOrginal Email from $Name%0A**************************************************%0A%0AName: $Name%0AEmail: $Email%0APhone: $Phone%0AComments: $Comments\" title=\"Click Here Too Reply To This Messege\" hidefocus=true><img src='r2m.PNG' border=0></a>\n</div>\n</div>\n</td>\n</tr>\n";

}

}

  print "</table></center>";

?>


</body>
</html>[/code]

[DarkendSoul]

AM / PM Problem... Well go with a 24 hour clock.

Another option would be using the Date field, then it would order then you can explode and use date().

[matto]

You should be able to do what you need with the below structure

[code]
CREATE TABLE `messeges` (
  `Date` datetime NOT NULL,
  `Name` text NOT NULL,
  `Email` text NOT NULL,
  `Phone` text NOT NULL,
  `Comments` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[/code]

[phpBeginner06]

DarkendSoul,

  Please tell me more about your idea for a 24 hour time clock. I tried to set my time to military time (ie H:i:s); but I could not get that to work. Is that the type of 24 hour clock you are talking about?

PS: I also have never used the "explode" function. I am going to go read about it in the manual now.

matto,

  I tried the table structure that you suggested; but I still have the same problems with this structure. My DATETIME still appears as "0000-00-00 00:00:00" and the table would not order.

[phpBeginner06]

I think I might have gotten the 24 hour clock (military time) to work this time. What I think I will do is create a field just for the 24 hour clock (military time) and hide it with CSS. Then have another field that will display civilian time (2:28 AM). "Man I Hope This Works" - LOL!!!

[color=blue]Thank You To All of You Who Have Been Putting Up With Me Bothering You, For The Past Couple Days !!![/color]

[matto]

Good luck.

:)