Echoing the variable shows one thing, however the database is showing another

Hi, I have this script below. I echo in there the date, time, and the message. The message matches what is showin my database. However, the echo that I put on this page doesn’t match what it should

EDIT-[URL=“http://ryanreese.us/checkInfo.php”]http://ryanreese.us/checkInfo.php

That page actually has the echoing. The recall.php is taking from the database and is showing a whole bunch of 0’s in place of date/time. The checkInfo.php when you go there shows what the actual date() and time() is, and what SHOULD be entered into the database.

This is my recall.php

<?phpsession_start();
include "config.php";
$link = mysql_connect($host, $username, $password);


if(!$link){echo "link";die(mysql_error());}
$db_selected = mysql_select_db($DbName, $link);
if(!$db_selected){echo "dbselect";die(mysql_error());}




$i=0;
$query="SELECT * FROM userSubmitted";


$result=mysql_query($query) or die(mysql_error());


$num=mysql_num_rows($result);


while ($i <= $num) {


$fieldOne=mysql_result($result,$i,"PostNumber");
$fieldTwo=mysql_result($result,$i,"Date");
$fieldThree=mysql_result($result,$i,"Time");
$fieldFour=mysql_result($result,$i,"Content");


echo $fieldOne;
echo "<br>";
echo $fieldTwo;
echo "<br>";
echo $fieldThree;
echo "<br>";
echo $fieldFour;


echo "<br><br>";


$i++;
}
echo "end of script";
mysql_close();
?>

I have this code for checkInfo.php

<?phpinclude ("config.php");
?>
<!doctype html>
<html>
<head>
<title>check</title>
</head>
<body>
<?php


$contentOfPost=$_POST['contentOfPost'];
$date = date("Y/m/d");
$time = time();


$link = mysql_connect($host, $username, $password);


if(!$link){echo "link";die(mysql_error());}
$db_selected = mysql_select_db($DbName, $link);
if(!$db_selected){echo "dbselect";die(mysql_error());}
$insertionToDatabase="INSERT INTO $tableName (PostNumber, Date, Time, Content) VALUES (NULL, $date, $time, '$contentOfPost')";


$result = mysql_query($insertionToDatabase);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}


echo $contentOfPost;
echo "<br>";
echo $date;
echo "<br>";
echo $time;
echo"<br>";
echo $result;
mysql_close($link);


?>


</body>
</html>



I can describe my database if you wish. Just 4 columns. PostNumber (auto increment), Date, Time, and Content. Time in the database is a Timestamp, the date is set as a Date.

Oh, just went to my page, apparently someone is having fun with my blog.php, because there are somehow 12 blog posts, and ther eis only supposed to be like 2-3 in there (that were my own)…:slight_smile:

that’s redundant

a mysql timestamp includes the date, so the date column isn’t really necessary

i don’t do php, but i’ll bet that $time = time(); is being entered into the timestamp column as a time only, i.e. with a zero date portion

you should really have only one column

TIMESTAMP is okay, but in my opinion DATETIME is better

(TIMESTAMP will update automatically whenever the row is updated, so if you make a post, and then come back and maybe do some editing on the content, the timestamp will be updated, whereas a DATETIME will update only when you actually update it, which you probably wouldn’t do if you were just fixing content typos…)

If I only had one column in my database, would I be able to pick out blog posts that only have dates on a certain day? I don’t know how DATETIME formats their entries.

$time=time(); only. No formatting has been done. What would I need to change in my code now? I’ll change my database accordingly to merge the time/date together into DATETIME

Use the MySQL function FROM_UNIXTIME() to convert the return value of time() to the proper, expected format.


$insertionToDatabase="INSERT INTO $tableName (PostNumber, Date, Time, Content) VALUES (NULL, $date, FROM_UNIXTIME($time), '$contentOfPost')";

Thank you oddz, now when I go to submit a post, the Time section is displaying such as this

[COLOR=#444444][FONT=sans-serif]2012-02-27 20:03:37

However one more issue. The date isn’t showing as expected. The Time column in my database has the date/time, so should I merge those columns into a Date/Time column?

In a perfect world, the date column will disply the 2012-02-27 in that column, while the time column will only have the time.

We’re making progress :).

Edit-to clarify, my date is still showing a string of 0’s. As expected.[/FONT][/COLOR]

yes, that’s my recommendation

that’s far from perfect, i’m afraid

displaying date and time separately is trivially easy, but storing and manipulating them as separate columns is problematic

No, do as @r937 suggested and trash the date column because it is redundant. You don’t need to split the date and time portion between two separate columns. The only reason one might consider that is for optimization reasons (if you needed to join on the date portion only). However, that would probably be a rare case and is much more advanced/complex than what you need here. Also, it can always be added later when it is absolutely necessary. Otherwise your just making things more complex than they really need to be given the time manipulation functions available in both MySQL and PHP.

@RyanReese,

With PHP and MySql you have just opened Pandora’s Box :slight_smile:

Check out http://www.php.net/manual/en/index.php



define('jj', '<br />');


  while ($row = mysql_fetch_object($result))
  {
     echo jj, 'PostNumber: ', $row->PostNumber;
     echo jj, $row->Date;
      
    $tmp = strtotime( $row->Date );    
    echo jj, date("l jS \\of F Y h:i:s", $tmp );
    
    echo jj, '$row->Content';
  }	


#Output

PostNumber: 1
2012-02-17 21:34:21
Friday 17th of February 2012 09:34:21
$row->Content

PostNumber: 2
2012-02-17 21:22:32
Friday 17th of February 2012 09:22:32
$row->Content

PostNumber: 3
2012-02-17 21:24:08
Friday 17th of February 2012 09:24:08
$row->Content

PostNumber: 4
2012-02-11 12:37:28
Saturday 11th of February 2012 12:37:28
$row->Content

In your database, have 1 field say DATED VARCHAR(20) and make your insert command like this:

$insertionToDatabase="INSERT INTO $tableName (PostNumber, Dated, Content) VALUES (NULL, time(), '$contentOfPost')";

Now, when you display use this:

$fieldOne=mysql_result($result,$i,"PostNumber");
$fieldTwo=mysql_result($result,$i,"Dated");
$fieldThree=mysql_result($result,$i,"Content");


echo $fieldOne;
echo "<br>";
echo date("Y-m-d (h:i:s)",$fieldTwo); // this will show 2012-02-27 (12:00:05).          IMP: use php.net for full date / time formats
echo "<br>";
echo $fieldThree;

//You can use other formats like:
echo date("Y-m-d",$fieldTwo); // 2012-02-27
echo date("M/d/Y",$fieldTwo); // Feb/02/2012
echo date("h:i:s a",$fieldTwo); // 12:11:12 am

Above way, your database will have a timestamp via time(). And you can use that to show the date in any format you want.

VARCHAR??? oh, please :nono:

I’ll rewrite my code to compensate for only one column now, and I’ll editm y code accordingly.

@John, that code you just posted, what is that replacing? ALL of my echoing code…?

Ok now it is displaying correctly.

1
2012-02-28 12:38:41

message
[COLOR=#000000][FONT=Times New Roman]Running

[/FONT][/COLOR]