#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    60
    Rep Power
    2

    How can I get the time of comment posts?


    Hi there.
    I'm learning PHP and MySQL using MyAdmin.
    I would appreciate if someone can help me solve this problem with data type of timestamp.

    I'm trying to create this simple comment uploading system where users can write comments and post them.
    In my plan, the system will get the uploaded comments inspected and approved by site administrator on CMS side
    so the comments like insults won't be seen in public up online.

    To do that, I made this "Approval" button in CMS page for the site administrator.
    Once the site administrator would click the button it's going to fire up this function to send request to MySQLDB
    so that a string data "Yes" will be stored under the "Approval" column in the corresponding record.
    The comment page then will be able to show only approved comments
    by pulling up the record with only "Yes" under the column "Approval".

    However, when users write some comments and post them, the timestamp will first show on the DB only the time of that moment.
    And once the site administrator approves them to be posted up online, I realized that the timestamp would be re-written
    to be the time of comment approvals.
    But I don't want the timestamp to change after an update is made on a record by the site administrator.
    I would like to keep the time when the original comment was made and it to be posted as it is.

    Besides, the timestamp seems to show the time of server's location, rather than each user's location,
    and I don't know if this is how it's supposed to be..

    So, I basically have 2 questions.

    1. How can the MySQL get the timestamp for the time that is consistent with each user's location?
    Does a blowser send info over http request about the time when a request was made and is there way to get that time on server side??

    I don't want users in NY write comments and post it and the comment turns out to show timestamp
    that's 3 hours before the comments were originally posted in NY just because the server is located somewhere in California.
    In general, do usual comment posting systems out there do anything about it??
    How do they go about it? What's the standard way to go??

    2. Is there any way to keep the initial comment posting time to be reflected as final output
    even after an update to the corresponding record was made with the string "Yes" under the column of "Approval"?
    Should I use javascript to figure out the time of the user's location since it's client side language
    and store the data to DB under the column of different data type?

    I don't want user's comment posted 2pm on 5-19-2013 to be shown to public
    with the time stamp showing "2013-5-20 10:00:00" because administrator approve the comment 10am next day.


    Thank you
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,185
    Rep Power
    9398
    Originally Posted by Ihatephp
    However, when users write some comments and post them, the timestamp will first show on the DB only the time of that moment.
    And once the site administrator approves them to be posted up online, I realized that the timestamp would be re-written
    to be the time of comment approvals.
    Only if you have code telling it to do that. Or are using an ON UPDATE CURRENT_TIMESTAMP.

    Originally Posted by Ihatephp
    Besides, the timestamp seems to show the time of server's location, rather than each user's location,
    and I don't know if this is how it's supposed to be..
    That's the default: the MySQL server's time. You can do a SET time_zone = "timezone" to set it to whatever you want (per connection). But you shouldn't be using the individual user's timezone - just one particular timezone. Any one, just make sure you always use the same one.

    Sounds like you want to use New York time.
    Code:
    SET time_zone = "America/New_York";
    If you control the database server you can set that globally too.

    Originally Posted by Ihatephp
    2. Is there any way to keep the initial comment posting time to be reflected as final output
    even after an update to the corresponding record was made with the string "Yes" under the column of "Approval"?
    Should I use javascript to figure out the time of the user's location since it's client side language
    and store the data to DB under the column of different data type?
    See above comments.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    60
    Rep Power
    2
    Thank you very much!!

    I will try it.

IMN logo majestic logo threadwatch logo seochat tools logo