1 Reply Latest reply: Feb 8, 2013 8:00 PM by bregent RSS

    What's wrong with this update code?

    matthew stuart Community Member

      I am trying to update a record in my table with a counter and a current date and time.

       

      For the date I can't use a default update for a timestamp for the date and time as this will have an affect on ORDER BY date, so I need to update this particular date with a manual process.

       

      For the page counter, I have pretty much the same code working on another page which updates the record as soon as the page is loaded, but I need this counter to be updated when a form is submitted or insert button pressed. Here's the code:

       

      <?php

      if (isset($_POST["Insert"]) || isset($_POST["Insert_x"]))

      {

      $replycounterID = $row_rs_replyID['fld_fID'];

      mysql_query("UPDATE tbl_forumPOSTS SET (fld_fREPLYCOUNT, fld_fREPLYDATE) VALUES (fld_fREPLYCOUNT = fld_fREPLYCOUNT + 1,NOW()) WHERE fld_fID = '".$replycounterID."'");

      }

      ?>

       

      I als tried this:

       

      <?php

      if (isset($_POST["Insert"]) || isset($_POST["Insert_x"]))

      {

      $replycounterID = $row_rs_replyID['fld_fID'];

      mysql_query("UPDATE tbl_forumPOSTS SET fld_fREPLYCOUNT = fld_fREPLYCOUNT + 1 AND fldfREPLYDATE = NOW() WHERE fld_fID = '".$replycounterID."'");

      }

      ?>

       

      I know the right record for updating is being selected because I have echoed $row_rs_replyID['fld_fID'] on the page, and the correct info is showing.

       

      I've even got two hidden fields with fld_fREPLYCOUNT = fld_fREPLYCOUNT + 1 and NOW() as values, but nothing is working!

       

      I am trying to update using the above code, but I am also inserting a new record within the same table with a separate INSERT statement. I am wondering if this is not possible. Is it possible to update one record in a table while update an existing one in the same table? This code isn't even registering with the record that I have tried to update even though the form is submitting!

       

      Thanks in advance for your advice.

        • 1. Re: What's wrong with this update code?
          bregent CommunityMVP

          >For the date I can't use a default update for a timestamp for the date and

          >time as this will have an affect on ORDER BY date, so I need to update this

          >particular date with a manual process.

           

          I don't understand this. AFAIK, you can have a timestamp column automatically init/update to the current date/time, or set it manually by passing it a value.

           

          Next, I don't really know MySQL but neither of your UPDATE statements look valid. Your first looks like you are using INSERT statement syntax, and the second has an AND keyword. I'm surprised you're not getting an error message.

           

          Please refer to the manual for the correct syntax.

          http://dev.mysql.com/doc/refman/5.0/en/update.html