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

**RyanReese** · Feb 27, 2012 18:31

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-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

Code:

<?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

Code:

<?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)...

**r937** · Feb 27, 2012 19:19

Originally Posted by RyanReese

Time in the database is a Timestamp, the date is set as a Date.

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...)

**RyanReese** · Feb 27, 2012 19:30

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

**oddz** · Feb 27, 2012 20:41

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

Code SQL:

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

**RyanReese** · Feb 27, 2012 21:10

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

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.

**r937** · Feb 27, 2012 21:43

Originally Posted by RyanReese

... so should I merge those columns into a Date/Time column?

yes, that's my recommendation

Originally Posted by RyanReese

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.

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

**oddz** · Feb 27, 2012 21:47

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.

**John_Betong** · Feb 27, 2012 22:35

@RyanReese,

With PHP and MySql you have just opened Pandora's Box

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

PHP Code:



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

**zeeshanhashmi** · Feb 28, 2012 02:57

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

HTML Code:

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

Now, when you display use this:

PHP Code:


$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.

**r937** · Feb 28, 2012 03:41

VARCHAR??? oh, please

**RyanReese** · Feb 28, 2012 11:03

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...?

**RyanReese** · Feb 28, 2012 13:49

Ok now it is displaying correctly.

1
2012-02-28 12:38:41

message
Running

User Tag List

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

Thread Tools

Display

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

Bookmarks

Bookmarks

Posting Permissions