[SOLVED] datetime function in php?

[Infinity99]

Hi,all.

I just started to build a simple forum application.in my postreply.php I set things like this

$postDate = date("D M d,Y H:i:s");
                        $postTime = date("U");
                        $userID = $User["UserID"];

                        // And add the new message to the Post table
                        $query = "INSERT INTO masterpost (parent_thread, in_reply_to, author, body, time_post, time_elapsed, userID) ";
                        $query .= "VALUES( ";
                        $query .= $thread['parent_thread'] . ",";
                        $query .= $_POST['freplyto'] . ",";
                        $query .= "'$author',";
                        $query .= "'$body',";
                        $query .= "'$postDate',";

 

and then I would insert this record to my database and I display it with order by time_post,But when I want to display it,my placement is all messed up,eventhough it was ordered by time_post.

After I read an reread my codes,I think I find the culprit.I think it's because I used date() function.So,it got local time and not my server time.

My question is,is there other ways to record the datetime in php?

[phpQuestioner]

create a field in your data table and set it to a timestamp

[Infinity99]

thanks.

I tried that but it became a new problem for me.

After a couple of posting,I encountered some error.My date in my database is messed up.So,when I select it with order by date,the date itself is ordered,but the conversation isn't making sense.In my desperate move,I use order by ID and it fix.

The one thing I want to ask is how come my date is messed up eventhough I already use mysql tipe timestamp?

Is it because I still have time_post which I generate with php so it take the timestamp based on my date in time_post?

 

here is my sql dump:

CREATE TABLE IF NOT EXISTS `masterpost` (
  `parent_thread` int(10) unsigned NOT NULL,
  `in_reply_to` int(10) unsigned default NULL,
  `id` int(10) unsigned NOT NULL auto_increment,
  `author` varchar(100) NOT NULL,
  `userID` varchar(20) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `body` text,
  `time_post` varchar(100) NOT NULL,
  `time_elapsed` bigint(21) NOT NULL,
  `lock_status` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=191 ;

--
-- Dumping data for table `masterpost`
--

INSERT INTO `masterpost` (`parent_thread`, `in_reply_to`, `id`, `time_post`, `time_elapsed`, `time_elapsed`) VALUES
(36, NULL, 76, '2007-11-14 02:50:22', 'Wed Nov 14,2007 09:50:22', 1195008622),
(36, 76, 93, '2007-11-12 13:12:29', 'Mon Nov 12,2007 20:12:29', 1194873149),
(36, 93, 104, '2007-11-05 14:36:32', 'Mon Nov 05,2007 21:36:32', 1194273392),
(36, 104, 109, '2007-11-12 13:12:29', 'Mon Nov 12,2007 20:12:29', 1194873149),
(36, 114, 116, '2007-11-09 04:19:26', 'Fri Nov 09,2007 11:19:26', 1194581966);

 

date is the one mysql generate and time_post is the one I record using localtime

[axiom82]

Have you tried:

ORDER BY date DESC

[Infinity99]

Have you tried:

ORDER BY date DESC

 

I think you misunderstood,axiom.

when I select it with order by date,it is ordered by date.But,the message in the forum become messed up.I don't get it,In my logic if I select it and order it by date,the message will be ordered by the oldest to the newest one right ?

But the result is,sometimes the newest one is located in the middle not in the last position.That's why I'm confused eventhough if I check the date,It's goin ok

[axiom82]

Your specification for the `date` field may be causing a conflict.  Does this happen only after an update?

[Infinity99]

Yes,but the one thing that make me confused is it happens sometimes,not everytime.

for example :member a posted at saturday 9 november and later on monday member b posted the reply to member a.But, in my database member b posted before member a.

How could that happen?That is the one that make me confused?

Can you specified the conflict you meant,axiom?Can't wait for your reply,I'm really glad there is someone who wants to help

[rajivgonsalves]

what is your select statement to fetch the post ?

[Infinity99]

"SELECT masterpost.id, masterpost.author, masterpost.date, masterpost.body, masterpost.lock_status, masteruser.UserID, masteruser.Gender, masteruser.City, masteruser.ImagePath,masteruser.TotalPoint, masteruser.post_count FROM masterpost, masteruser WHERE masterpost.parent_thread=". $threadID ." AND masterpost.userID = masteruser.UserID ORDER BY masterpost.id asc LIMIT " .$offset.",".$perPage

 

that is my select code.Before,I use ORDER BY masterpost.time_post which is the time I generated with function date().Then,I change to ORDER BY masterpost.date which is TIMESTAMP.Now,I use masterpost.id cause my I used AUTO INCREMENT

[aschk]

Please newline your SQL, it would be so much easier to read. Also use aliases instead of full table names, it's easier to maintain.

"SELECT mp.id
         , mp.author
         , mp.date
         , mp.body
         , mp.lock_status
         , mu.UserID
         , mu.Gender
         , mu.City
         , mu.ImagePath
         , mu.TotalPoint
         , mu.post_count 
FROM masterpost mp
JOIN masteruser mu ON mp.userID = mu.UserID 
WHERE mp.parent_thread = {$threadID}
ORDER BY mp.date ASC
LIMIT {$offset},{$perPage}";

 

Note , i have also performed your JOIN syntax as it should be instead of implicit inner join (using ,) with WHERE clause.

I have also used the {$} variable syntax instead of ".$." because this can get messy and confusing.

[aschk]

Is it me or do you appear to have 3 fields that store the same thing :

date

time_post

time_elapsed

 

Decide which you want (i recommend the TIMESTAMP one) and ONLY use that.

[Infinity99]

Is it me or do you appear to have 3 fields that store the same thing :

date

time_post

time_elapsed

 

Decide which you want (i recommend the TIMESTAMP one) and ONLY use that.

 

thanks for all your correction.In the future,I will try to be more careful.I've learned a lot from your posting.

Yes,it's true.I used 3 fields for time.Maybe this is the cause of my conflict.Ok,I'll try your advice first.