[RESOLVED] mysql update query fails
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: [RESOLVED] mysql update query fails

  1. #1
    Member
    Join Date
    Aug 2006
    Location
    Montgomery, AL
    Posts
    64

    resolved [RESOLVED] mysql update query fails

    Sure wish the "remember me" was the default setting for logging in. Save a lot of time and trouble. Let me do this again.

    I'm running php5 and mysql5 I have edit.php page that takes info from table and populate fields. When submitted it goes to update.php and as the name implies it updates the table with altered info.

    If you change just one tiny thing on edit.php page -> like one number in a phone number -> and hit submit it updates fine. But if you load edit.php and hit update without changing anything the update query fails.

    I use mysql_affected_rows() to show success or failed to update message. Again no change it fails with no query error given with
    PHP Code:
    or die ($query\n<br />mysql_error()); 

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,432
    You didn't ask any questions, nor did you show us any (useful and/or valid) code, so... in case you were expecting us to be able to help you in some way, you might want to do both of those.

    Additionally, note that the entire mysql extension is severely outdated and has been deprecated in favor of newer extensions such as MySQLi or PDO.

  3. #3
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,932
    I wonder if your code might be checking the number of affected rows rather than the success/failure of your query? Remember that an update query which changes nothing will affect no rows.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  4. #4
    Member
    Join Date
    Aug 2006
    Location
    Montgomery, AL
    Posts
    64
    Yeah, I just ordered a book from Amazon that is about Php 5, Mysql 5 and MySQLi. My edit/update pages didn't give me any trouble with Php & MySQL 4, so something in MySQL has changed. I've had Php 5 on my server but MySQL 5 is new so that must have something to do with why all of a sudden my website is misbehaving.

  5. #5
    Member
    Join Date
    Aug 2006
    Location
    Montgomery, AL
    Posts
    64
    This forum must have changed to a wider resolution because I'm having to scroll horizontally now to read the posts.

  6. #6
    Member
    Join Date
    Aug 2006
    Location
    Montgomery, AL
    Posts
    64
    sneakyimp -> The query is like this:

    PHP Code:
    $query "UPDATE table SET f_name='$f_name', l_name='$l_name', phone='$phone', email='$email' WHERE user_id='$user_id'";
    $result mysql_query($query) or die("Query: $query\n<br />MySQL Error: " mysql_error());
    if (
    mysql_affected_rows() == 1)
    {
         echo 
    "Update Successful";
    }
    else
    {
         echo 
    "Update Failed";

    The edit page form is being populated with the existing database information. Even if nothing is changed it should just re-enter the information since there is no script to check and see if the posting information is the same as what is already in the database and if so turn it away. Unless this is a new automatic "feature" in MySQL 5.
    Last edited by David P; 11-10-2012 at 01:11 AM.

  7. #7
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,910
    If nothing changes then mysql_affected_rows() returns 0 - because that's how many rows were affected by the change.

    See what it says about return values when using UPDATE on the mysql_affected_rows manual page.
    Last edited by Weedpacket; 11-10-2012 at 05:17 AM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  8. #8
    Member
    Join Date
    Aug 2006
    Location
    Montgomery, AL
    Posts
    64
    Sure as the world, there it is...
    When using UPDATE, MySQL will not update columns where the new value is the same as the old value.
    So what would be better to use in this instance?

  9. #9
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,910
    Quote Originally Posted by David P View Post
    Sure as the world, there it is...
    So, just like sneakyimp said.
    So what would be better to use in this instance?
    That depends on what you are trying to do - you're the one writing this, after all. Judging from the messages you display, am I right in guessing that you're wanting to check for an error? There may be a function for that.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  10. #10
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,932
    Whether or not you consider it a success or failure is entirely up to what you expect to happen. If a row should be updated but non is, that sounds like failure. If someone "updates" their mailing info without changing anything, I would not consider that a failure -- just obsessive behavior on the part of the user. In the latter case, you should probably, as Weedpacket suggested, check for an error condition when the query runs rather than checking to see if any rows are affected.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  11. #11
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    also consider that checking if the user changed any fields, before querying mysql, could save you the trouble of trying to figure it out afterwards (as well as a round-trip to the DB).

  12. #12
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,432
    Quote Originally Posted by traq View Post
    also consider that checking if the user changed any fields
    ... something which could be done on the client side of things, meaning you could even eliminate the trip to the server altogether.

  13. #13
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    indeed.

  14. #14
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,910
    Quote Originally Posted by bradgrafelman
    ... something which could be done on the client side of things, meaning you could even eliminate the trip to the server altogether.
    And a server-side check can also eliminate the database hit, if you cache the current values in the session at the same time you send them to the client.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  15. #15
    Member
    Join Date
    Aug 2006
    Location
    Montgomery, AL
    Posts
    64
    Lets say that someone has already entered information into the database. They then decide to edit that information. But once they get to the edit form and read what what they had already entered and decide to just leave it as is -> but rather than clicking on a button that takes them back to the starting page, they click on the update button they'll get an error message because PHP 5 will not reenter the same information.

    Checking a single entry like say (cat) is easy, but checking to see if the contents of a POSTed textarea is identical to what is in the mysql field is not.

    PHP Code:
    $duty_id $_POST['duty_id'];
    $_SESSION['duty'] = $_POST['duty'];

    $q SELECT duty FROM duties WHERE duty_id='$duty_id'";
    $r = mysqli_query($dbc$q) or die(mysqli_error($dbc));
    if (
    $r === $_SESSION['duty']) { // also tried ($r == $_SESSION['duty'])

    echo "
    Information is the same so it does not need updating. <a href='start.php'>Return to Starting Point</a>";

    } else {

        
    $qu = "UPDATE duties SET duty='" . $_SESSION['duty'] . "' WHERE duty_id='$duty_id'";
        
    $ru = mysqli_query($dbc$qu) or die(mysqli_error($dbc));
        if (mysqli_affected_rows(
    $dbc) == 1) {

        echo "
    Updated!";

        } else {

        echo "
    ERRORThe same information cannot be re-entered.";

        }


    I've tried this and several other ways of checking to see if the information is the same but it breezes right past them and goes straight for the update query and the error message.

    If the purpose of PHP 5 not re-entering in the same data in an update is to save time and resources, but you have to perform an additional query to test the MySQL data against the posted variable data, then what have you gained? It seems this would take up more time and/or resources than just simply re-entering the same data.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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