SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    TimeStamp Question/Problem

    I have a table with two timestamp(14) fields called add_date and updated. When I insert a new entry I want the current date/time set into these two fields. When I update the entry I only want the updated field to change. The problem is that everytime I update the entry, both fields' timestamps are changed.

    When I do an UPDATE query, I set updated = now()

    My settings for both fields are timestamp(14) and fields can be NULL.

    Any ideals on what I'm doing wrong?

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can we see the exact query you are using and you are sure you didn't default the timestamp fields to now()? BTW what part of SD do you live in? I used to live in South Mission beach just south of the roller coaster.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the full SQL statement:

    $sql = "UPDATE articles SET
    copy='$text',
    headline='$head',
    tag_ID='$tag', " .
    about_ID='$about',
    template_ID='$layout',
    super_ID='$type',
    mag_ID='$maga',
    updated=now(),
    idate='$i_date',
    volume='$vols',
    issue='$iss',
    authors_ID='$aid'
    WHERE ID=$id";

    As can see, add_date is not even listed.

    I live in Mission Valley just East of the stadium.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what is the default value for the add_date field?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is no default for either of the timestamp fields. The fields are allowed to NULL. Originally the fileds were set to be NOT NULL but I kept getting a string of 14 zeros (the default value) in the field so I changed it.

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It just doesn't seem logical, is there a chance that you are updating this somewhere else in your code? By the looks of your code, it should only be updating the updated field. Can you post all the pertinent code you are using to update the database? Not just the update statement but the code around it above and below it.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $dbcnx = mysql_connect( "localhost","UserName", "PassWord");
    if (!$dbcnx)
    {
    echo( "<P>Unable to connect to the database server at this time.</P>" );
    exit();
    }
    if (! @mysql_select_db("hot_news") )
    {
    echo( "<P>Unable to locate the News Article database at this time.</P>" );
    exit();
    }
    if ($submit):
    $sql = "UPDATE articles SET
    copy='$text',
    headline='$head',
    tag_ID='$tag',
    about_ID='$about',
    template_ID='$layout',
    super_ID='$type',
    mag_ID='$maga',
    updated=now(),
    idate='$i_date',
    volume='$vols',
    issue='$iss',
    authors_ID='$aid'
    WHERE ID=$id";
    if (mysql_query($sql)) {
    echo("<P>Article has been updated.</p>");
    } else {
    echo("<P>Error updating article: " .
    mysql_error() . "</P>");
    }

    That's the only update/insert query on the page. All other queries are SELECTs.

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is what I would do. I would do

    PHP Code:
    print $sql
    right after the update statement then I would run the script through the browser take the sql statement that was printed to the screen and copy and paste it onto the command line in MySQL nad see if is still updating both fields. I can not for the life of me think of what would cause that to happen.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <screaming obscenites>

    This is soooooo illogical. Its not supose to be doing this, but it is.

    Freddy, I try you suggestion and mysql still changed both fields. Here's the SQL statement I entered at the command line:

    UPDATE articles SET copy='[u]San Diego based Z Microsystems[eu] is at the forefront of breakthrough military technology insertion programs. A major government contractor for NATO, subcontracted to Z Microsystems to develop a COTS rugged, MIL-tailored, flat panel display that would meet tough military standards. Z Microsystems’ selected the all-new 20.1” digital LCD panel from NEC, along with the latest in surface acoustic wave - touch screen technology to design and engineer its Orion 20R model. Although power consumption by the Orion 20R is only 85 Watts, it is 66% brighter than previously available flat panels in the industry; even with the acoustic touch screen overlay. The Orion 20R is destined for use in NATO advanced surveillance aircraft applications, and meets stringent environmental operating temperature specifications of 0º to + 50º C, and -25º to + 70º C for non-operating temperatures. Operating humidity conditions are 5% to 90% non-condensing in altitudes of -1,300 to 15,000 ft., and up to 40,000 ft. for non-operating altitudes. The Orion 20R has passed 50 hours of sinusoidal vibration at levels up to 1.4g’s and is designed to meet MIL-STD-901D Grade A Class II and 20 g’s 11ms shock pulses per MIL-STD-810E Method 516. Electro mechanical emissions are contained within the enviable MIL-STD-461C Class A1B. The Orion 20R display weighs only 23 lbs.; including the surface acoustic wave touch screen and military bayonet style video, power, and serial (touch panel) connectors. Z Microsystems’ manufacturing facility operates according to IPC-6101 electronic assembly requirements, ISO 9002, and applicable section of MIL-HDK-454. ', headline='Z Microsystems Develops New Innovative Flat Panel Display', tag_ID='', about_ID='1', template_ID='1', super_ID='1', mag_ID='', updated=now(), idate='', volume='', issue='', authors_ID='2' WHERE ID=1;

    Is there any other way I can do this?

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is my understanding (and I could be wrong) that the timestamp field in MySQL is automatically updated with the current date everytime an update is done unless there is a statement to update it to some other value. What I do to get around this is preceed my update query with a select query to pull the timestamp value from the field the that I do not want changed and assign it's value to a variable. Then I update it in the update statement to the date that I just assigned to that variable. Might look something like this:

    $dbcnx = mysql_connect( "localhost","UserName", "PassWord");
    if (!$dbcnx)
    {
    echo( "<P>Unable to connect to the database server at this time.</P>" );
    exit();
    }
    if (! @mysql_select_db("hot_news") )
    {
    echo( "<P>Unable to locate the News Article database at this time.</P>" );
    exit();
    }
    if ($submit):

    $sql="Select add_date from articles where ID='$id'";
    $res=mysql_query($sql);
    $row=mysql_fetch_array($res);

    $add_date = $row[add_date];


    $sql = "UPDATE articles SET
    add_date='$add_date',
    copy='$text',
    headline='$head',
    tag_ID='$tag',
    about_ID='$about',
    template_ID='$layout',
    super_ID='$type',
    mag_ID='$maga',
    updated=now(),
    idate='$i_date',
    volume='$vols',
    issue='$iss',
    authors_ID='$aid'
    WHERE ID=$id";
    if (mysql_query($sql)) {
    echo("<P>Article has been updated.</p>");
    } else {
    echo("<P>Error updating article: " .
    mysql_error() . "</P>");
    }
    Marty H.

  11. #11
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From the MySQL manual:

    "The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

    Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

    The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
    The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
    You explicitly set the TIMESTAMP column to NULL.
    TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().

    You can set any TIMESTAMP column to a value different than the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:

    Let MySQL set the column when the row is created. This will initialize it to the current date and time.
    When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value.
    "

    So I was pretty close to right. The code that I gave you above should solve this problem or you could change the field type to datetime.
    Marty H.

  12. #12
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Marty for the info.

    If I understand you correctly, if I change the order of the fields and have updated come before add_date, then there is no need to even mention either field in the UPDATE statement as the first timestamp field will be automatically updated and the second field will be left alone.

    I'll give it a try and report back.

  13. #13
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by CyberFuture
    If I understand you correctly, if I change the order of the fields and have updated come before add_date, then there is no need to even mention either field in the UPDATE statement as the first timestamp field will be automatically updated and the second field will be left alone.
    That is my understanding. Please let me know if that works. I have never tested, but have an application where this will apply and would make life easier.
    Marty H.

  14. #14
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK here's my results

    Moving add_date under updated in the field order of my table cause add_date NOT to be changes (YEAAHHHH). Note no change were made to my existing code.

    If I remove "update=now()," from my code, updated does NOT change. This goes against what the MySQL manual states, but atleast its now doing what I need it to do.

    I also tried changing updated from NULL to NOT NULL. This seemed to have no effect.

  15. #15
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is probably because updated has no idea that it is now the first field that is a timestamp. To dumb to figure out that add_date has moved!
    Marty H.

  16. #16
    SitePoint Evangelist ucahg's Avatar
    Join Date
    Apr 2001
    Location
    Sarnia, Ontario, Canada
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    find out what the add_date was originally via select, and then update everything, include the add_date and just "update" to the current value
    Love it? Hate it? Helpful? Useless?
    Use the rate button to let me know what you think of my post!

  17. #17
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could have swore I just said that!!
    Marty H.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •