Date Issue

id · August 11, 2012

im having a date issue...

In my code, when something is being added to my DB, i use the function...

 date("D M d, Y g:h a" , time());

But what ends up happening is that in the DB, it is only keeping one default time... So instead of the current date, its adding 8/10/2012 10:10 pm...

Pikachu2000 · August 11, 2012

More information would be helpful, such as how you're running the query, what should happen that is not happening, and why you're storing a date and time in that format to begin with . . .

id · August 11, 2012

Ok..

I'm storing the date in my DB because i want to know when a user is registered and when a user posts and replies.

What SHOULD happen :

User is registered on AUG 11, 2012 11:30 pm.

So the DB should record AUG 11, 2012 11:30 pm.

However this is happening:

User is registered on AUG 11, 2012 11:30 pm.

So the DB is recording AUG 10, 2012 10:10 pm (Which is happening to ALL my inputs. SO regardless of the time, its recording that date / time).

Example Code

<?php

session_start();

//ADD TOPIC!
include("../includes/config.php");



if(isset($_POST['submit']))
{

$username = isset($_SESSION['username']) ? $_SESSION['username']: '';
$topic = $_POST['topic'];
$detail = $_POST['detail'];
$date = date("D M d, Y g:h a" , time());


$result_user = mysql_query("SELECT * FROM members WHERE username='$username'");
while($row = mysql_fetch_array($result_user))
{
if(empty($row['post_count']))
{
	$post_count = 1;
}
else
{
$post_count = $row['post_count'] + 1;  //ADDING ONE PER POST!
}
$post_add = mysql_query("UPDATE members SET post_count='$post_count' WHERE username='$username'") or die(mysql_error());
$result = mysql_query("INSERT INTO forum_question(id,username,topic,detail,date_time) VALUES('','$username','$topic','$detail','$date')");

if($result_user && $post_add)
{
	echo "SUCCESS!";
	echo "<br /><a href='index.php'>View Topics.</a>";
}
else if(!$post_add)
echo "result user error!";

else
echo "Result error!";
}
}

?>

scootstah · August 11, 2012

If you are storing the date in a DATETIME column (which you should be), you can simply use the NOW() MySQL function to insert the current date/time in a proper format.

INSERT INTO table (date) VALUES (NOW());

id · August 11, 2012

If you are storing the date in a DATETIME column (which you should be), you can simply use the NOW() MySQL function to insert the current date/time in a proper format.

INSERT INTO table (date) VALUES (NOW());

Ok, so can i use a format inside the now() function? Or would have to convert the format from my DB to the format i want that is on my site?

EX. The format is 2012-08-10 23:29:09 but i want it to this format AUG 11, 2012 11:30 pm.

Pikachu2000 · August 11, 2012

You should be storing the date in YYYY-MM-DD hh:mm:ss format so you can make use of the many date and time functions built in to MySQL. For instance, you could simply use MySQL's NOW() function to insert the date and time instead of the much slower functions in php.

It's also unnecessary to SELECT a record to update it like your first query does. You can simply UPDATE table SET field = field + 1 WHERE etc. and accomplish it in one query. But that brings up another point. You don't really need to store data that can be derived from the table itself, like a post count. You can count the number of records that correspond to the user's id to get that figure.

As for your current problem, look at the formatting string you're using, check it against the values in the manual, and what each of them represents, and you should see why what's happening is happening.

id · August 11, 2012

It's also unnecessary to SELECT a record to update it like your first query does. You can simply UPDATE table SET field = field + 1 WHERE etc. and accomplish it in one query. But that brings up another point. You don't really need to store data that can be derived from the table itself, like a post count. You can count the number of records that correspond to the user's id to get that figure.

What do you mean by i dont really need to store the data that can be derived from the table itself?

Pikachu2000 · August 11, 2012

It appears you're storing posts in a table, so you can just query the table for a count of the posts by a user instead of storing the value in a separate field and incrementing it for each new post, or decrementing it when a post is deleted. SELECT COUNT(1) AS total_posts FROM table WHERE user_id = 'the_id_of_the_user

id · August 11, 2012

Ok i get what you are saying.

Now for the issue involving the

You can simply UPDATE table SET field = field + 1 WHERE

I tried this, and now my code doesn't work. Example, for post count i would just do

UPDATE members SET post_count = post_count + 1 WHERE username='$username'

However that doesnt work, i also tried

UPDATE members SET post_count = 'post_count + 1' WHERE username='$username'

and it doesnt work either.

id · August 11, 2012

Sorry for bothering you guys soo much about the time thing but this is the update on the issue:

The time is going into the DB on the correct time due to the now() function and i found out how to change the format of the time. However it is not dispaly the correct time on the site.

Example:

The DB time / date is : 2012-08-11 01:06:06

However on the site is saying: Sat Aug 11, 2012 1:01 am.

I tried all of these..

$date     = isset($row['date_time']) ? $row['date_time'] : '';
$date = strtotime($date);
$date = date("D M d, Y g:h a", $date);

$date     = isset($row['date_time']) ? $row['date_time'] : '';
$date = date("D M d, Y g:h a", strtotime($date));

and received no luck. However when i did..

$date     = isset($row['date_time']) ? $row['date_time'] : '';

Without trying to change the format, its works fine.

Barand · August 11, 2012

"g" and "h" both show the hour in a date format string. RTFM

scootstah · August 11, 2012

Just a heads up, you don't have to do this: $date = strtotime($date);. In the query, you can use the UNIX_TIMESTAMP() function to return a timestamp instead of the date/time string. You can also use the DATE_FORMAT.

id · August 11, 2012

Still no luck

I tried using a the query date format, but then the date don't even show up.

scootstah · August 11, 2012

Then why did you mark it as solved?

$result = mysql_query("SELECT UNIX_TIMESTAMP(date_time) AS date_time FROM table");
$row = mysql_fetch_assoc($result);
echo $row['date_time']; // YYYY-MM-DD hh:mm:ss

$result = mysql_query("SELECT DATE_FORMAT(date_time, '%a %b %e, %Y %l:%i %p') AS date_time FROM table");
$row = mysql_fetch_assoc($result);
echo $row['date_time']; // Sat Aug 11, 2012 1:01 AM

id · August 11, 2012

The format works fine.

The issue is that its doing this:

DB TIMES.

First Reply Time = Sat Aug 11, 2012 12:45 AM;

Second Reply Time = Sat Aug 11, 2012 12:50 AM

However instead its doing:

First Reply Time = Sat Aug 11, 2012 12:45 AM;

Second Reply Time = Sat Aug 11, 2012 12:45 AM;

Third Reply Time = Sat Aug 11, 2012 12:45 AM;

So its using the first time for all. However it only does this when i try the date_format query or date function.

//CODE IS EXTREMELY MESSY AND UNORGANIZED

$result1 = mysql_query("SELECT * FROM forum_answer WHERE question_id = '$id'");
$u_datetime = mysql_query("SELECT DATE_FORMAT(a_datetime,'%a %b %d, %Y %l:%i %p') AS a_datetime FROM forum_answer WHERE question_id='$id'");

while($row_5 = mysql_fetch_assoc($u_datetime))
{


while($row = mysql_fetch_array($result1))
{
$a_id       = isset($row['a_id'])       ? $row['a_id']       : '';
$a_username = isset($row['a_username']) ? $row['a_username'] : '';
$a_answer   = isset($row['a_answer'])   ? $row['a_answer']   : '';
$a_datetime = isset($row_5['a_datetime']) ? $row_5['a_datetime'] : '';

$a_post_count = mysql_query("SELECT * FROM members WHERE username='$a_username'");


while($row_2 = mysql_fetch_array($a_post_count))
{
$a_post_count_1 = $row_2['post_count'];

?>

<div class='view_post'>
<div class='view_post_date'>
<p>Published: <?php echo $a_datetime;?></p>
</div>
<div class='view_post_user'>
<p>
Username: <?php echo $a_username;?>
<br />
Post Count: <?php echo $a_post_count_1; ?>
<br />
</p>
</div>
<div class='view_post_content'>
<p><?php echo $a_answer;?></p>
</div>
</div>

scootstah · August 11, 2012

<div class='view_post'>
<div class='view_post_date'>
<p>Published: <?php echo $a_datetime;?></p>
</div>
<div class='view_post_user'>
<p>
Username: <?php echo $a_username;?>
<br />
Post Count: <?php echo $a_post_count_1; ?>
<br />
</p>
</div>
<div class='view_post_content'>
<p><?php echo $a_answer;?></p>
</div>
</div>

Is this code inside the while loop? I can't really tell, because I don't see any closing brackets.

id · August 11, 2012

Yes. Again it the code works without my trying to change the format.

scootstah · August 11, 2012

$result = mysql_query("SELECT DATE_FORMAT(a_datetime,'%a %b %d, %Y %l:%i %p') AS a_datetime FROM forum_answer WHERE question_id='$id'");
echo '<pre>';
while($row = mysql_fetch_assoc($result)) {
print_r($row);
}
echo '</pre>';

Can you run this code, exactly like that, for me? Post the output in your next reply.

id · August 11, 2012

Array
(
    [a_datetime] => Sat Aug 11, 2012 1:07 AM
)

Array
(
    [a_datetime] => Sat Aug 11, 2012 1:07 AM
)

scootstah · August 11, 2012

And those dates are not the same in the database? What does this give you:

$result = mysql_query("SELECT a_datetime FROM forum_answer WHERE question_id='$id'");
echo '<pre>';
while($row = mysql_fetch_assoc($result)) {
print_r($row);
}
echo '</pre>';

id · August 11, 2012

Array

(

[a_datetime] => 2012-08-11 01:07:29

)

Array

(

[a_datetime] => 2012-08-11 10:57:27

)

scootstah · August 11, 2012

Huh, that's odd. I'm not sure what would cause it to do that.

id · August 11, 2012

I know, that is why im confused too.

This is my whole file (Again, my code is sloppy, i just want this forum to work so i can start recording logs for my site.)

<?php session_start(); 

include("../includes/config.php");

?>


<html>
<head>
<link href="../css/main.css" type="text/css" rel="stylesheet"  />
</head>


<div id='header'>
<h1>Home Page</h1>
</div>

<div id='primary_nav'>
<ul>
<li><a href='../index.php'>Home Page</a></li>
<li><a href='index.php'>Forums</a></li>
<li><a href='../index.php?app=members'>Member's Page</a></li>
<li><a href='../index.php?app=members&module=account'>Account</a></li>
<li><a href='../index.php?app=pages&module=extra'>Extras</a></li>
</ul>
</div>


<div id='content'>

<div id='forum_container'>
<?php

$id         = isset($_GET['id']) ? $_GET['id']: '';
$result     = mysql_query("SELECT * FROM forum_question WHERE id='$id'");

while($row = mysql_fetch_array($result))
{
$p_username = isset($row['username'])  ? $row['username']  : '';
$post_count = mysql_query("SELECT * FROM members WHERE username='$p_username'");
$get_date = mysql_query("SELECT DATE_FORMAT(date_time,'%a %b %d, %Y %l:%i %p') AS date_time FROM forum_question WHERE id='$id' ");

while($row_1 = mysql_fetch_array($post_count))
{
while($row_3 = mysql_fetch_array($get_date))
{
$post_count_1 = $row_1['post_count'];	
$topic    = isset($row['topic'])     ? $row['topic']     : '';
$detail   = isset($row['detail'])    ? $row['detail']    : '';
$view     = isset($row['view'])      ? $row['view']      : '';
$reply    = isset($row['replies'])   ? $row['replies']   : '';
$date     = isset($row_3['date_time']) ? $row_3['date_time'] : '';

?>




<div class='view_post'>
<div class='view_post_title'>
<p><?php echo $topic?></p>
</div>
<div class='view_post_date'>
<p>Published: <?php echo $date;?></p>
</div>
<div class='view_post_user'>
<p>
Username: <?php echo $p_username;?>
<br />
Post Count: <?php echo $post_count_1;?>
<br />
</p>
</div>

<div class='view_post_content'>
<p><?php echo $detail; ?></p>
</div>
</div>


<?php
}}}

$result1 = mysql_query("SELECT * FROM forum_answer WHERE question_id = '$id'");
$u_datetime = mysql_query("SELECT DATE_FORMAT(a_datetime,'%a %b %d, %Y %l:%i %p') AS a_datetime FROM forum_answer WHERE question_id='$id'");

while($row_5 = mysql_fetch_assoc($u_datetime))
{


while($row = mysql_fetch_array($result1))
{
$a_id       = isset($row['a_id'])       ? $row['a_id']       : '';
$a_username = isset($row['a_username']) ? $row['a_username'] : '';
$a_answer   = isset($row['a_answer'])   ? $row['a_answer']   : '';
$a_datetime = isset($row['a_datetime']) ? $row['a_datetime'] : '';

$a_post_count = mysql_query("SELECT * FROM members WHERE username='$a_username'");


while($row_2 = mysql_fetch_array($a_post_count))
{
$a_post_count_1 = $row_2['post_count'];
echo "<pre>";
print_r($row);
echo "</pre><br />";

?>

<div class='view_post'>
<div class='view_post_date'>
<p>Published: <?php echo $a_datetime;?></p>
</div>
<div class='view_post_user'>
<p>
Username: <?php echo $a_username;?>
<br />
Post Count: <?php echo $a_post_count_1; ?>
<br />
</p>
</div>
<div class='view_post_content'>
<p><?php echo $a_answer;?></p>
</div>
</div>

<?php
}}}

$result2 = mysql_query("SELECT * FROM forum_question WHERE id='$id'");

while($row = mysql_fetch_array($result2))
{
$view = isset($row['view']) ? $row['view'] : '';

//If the views is empty, give it a number value! (1)
if(empty($view))
{
	$view = 1;
	$result3 = mysql_query("UPDATE forum_question SET view='$view' WHERE id='$id'");
}
else{
//Count more values!
$add_view = $view + 1;
$result4 = mysql_query("UPDATE forum_question SET view='$add_view' WHERE id='$id'");}
}

?>

<br />
<div class='view_post'>
<div class='view_post_title'>
<p>Post Reply...</p>
</div>
<div class='view_post_reply'>
<p>
<form action='add_reply.php' method='post'>
<center>
<textarea name='a_answer' id='a_answer' rows='3' cols='45' style='min-height: 200px; width: 90%;'></textarea>
<input name='id' type='hidden' value='<?php echo $id;?>' /><br />
<input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset">
</center>
</form>
</p>
</div>
</div>

</div> <!-- END CONTAINER TAG!-->


<div id='footer'>
<p>Copyright Empora Tech 2012</p>
</div>


</div><!-- END THE CONTENT TAG! -->


</body>
</html>

Psycho · August 11, 2012

I know, that is why im confused too.

This is my whole file (Again, my code is sloppy, i just want this forum to work so i can start recording logs for my site.)

Perhaps one has something to do with the other. When you write sloppy code you will get sloppy results.

You have three nested while loops that are running queries. For one, you should not have queries nested in loops - learn how to do JOINs in your queries. But the way you have them structure looks completely off.

id · August 11, 2012

I been trying to do JOINS / UNION and im still unsuccessful.

Sign In

Date Issue

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information