SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    delaware
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    General SQL Query Help

    Hi, I'm still really new at SQL so I have a question. I see all kinds of tutorials about INSERT, UPDATE and DELETE statements, but they're not real world examples. Let's take insert for an example. All the tuts that I have read assume no record already exists for the entry that we're inserting. How do you handle an INSERT when the record already exists? Is this where sub queries come into play? thanks.

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get the same problem when a updating a table record when the user has clicked the submit button without making any changes to any if the input boxes in the html form.

    MySQL throws an error when I try to update a record that doesn't have any new values.

    I haven't been able to find a quick solution so what I do now, and it's not really much extra coding, is run a select query first to see if the record exists exists and if it does then I don't proceed with the update.

    For example..
    Code:
     
     
    $isProductUpdated = $prodMgr->updateProduct($prodId,$prodName);
     
    //and in the class file
     
    public function updateProduct($prodId,$prodName) {
           //check if record exists
           $query = 'select * from tblproducts where fldProdId = "'.$prodId.'" and fldProdname = "'.$prodName.'"';
           if(mysql_num_rows(mysql_query($query,$this->$conn)) > 0) {
                    //nothing to update
                    return true;
           }
     
    //proceed with the update code from here
     
     
    }
    I haven't included any error checking/sanitisation in order to KISS.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by notta View Post
    How do you handle an INSERT when the record already exists?
    there are several scenarios, pick the one you want

    1. an INSERT for a row that already exists is an error
    2. an INSERT for a row that already exists should be ignored
    3. an INSERT for a row that already exists should update only columns values that have changed


    Quote Originally Posted by Kalon View Post
    MySQL throws an error when I try to update a record that doesn't have any new values.
    you should filter this condition with your application language (php?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post

    you should filter this condition with your application language (php?)
    Not sure what you mean

    If you mean I should specify which language I am using to submit the query, then yes I am using PHP.

    But I assumed (maybe incorrectly) that this "feature" was a MySQL thing and not a PHP thing.

    To clarify, I have an "Edit Product" html form which displays by default in each textbox the current data for each column in the products database table. If the user clicks the submit button without changing the values in any of the textboxes then MySQL throws an error when I run the update query. That is why I do a select first to see if a record with all the current values in the form's textboxes exists before doing the update.

    If the user changes the value in say just 1 out 10 textboxes then the update query works fine.

    Hopefully there is a more efficient way of checking if the user has actually changed any values in the form before submitting the form data to update the database.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if the user hasn't made any changes to the values in the form fields, where did those form field values come from? you must've previously queried the database, pulled up a row, and displayed those values

    so you have already retrieved a row, and now all you need to do is use php to check to see whether any of the values has changed, before submitting the update -- you do not need to re-query the database again, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if the user hasn't made any changes to the values in the form fields, where did those form field values come from? you must've previously queried the database, pulled up a row, and displayed those values

    so you have already retrieved a row, and now all you need to do is use php to check to see whether any of the values has changed, before submitting the update -- you do not need to re-query the database again, right?
    The default textbox values in the form all come from the database.

    A typical example could be a user wanting to change the price of a product. After the user clicks the edit button for that product a form is presented with all the current values for that product. But if the user clicks the submit button without actuially changing any of the values in the form then MySQL throws an error because it appears it doesn't like trying to update a record unless at least 1 value in that record is being changed.

    I can either store all the textbox values in variables on page load and onsubmit check to make sure at least one of the values in the textboxes is now different to the original values or I can do a select query to see if any changes to any of the columns for that record are being requested. If mysql_num_rows() returns a value > 0 then I don't run the update query because that tells me no changes have been made to any of the values in the form's textboxes.

    It would be nice if MySQL simply updated all the columns in the record whether the user actually changed any values or not in the form.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Kalon View Post
    MySQL throws an error when I try to update a record that doesn't have any new values.
    It does? Hmm, I never noticed that. I'll have to check it out.

  8. #8
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    It does? Hmm, I never noticed that. I'll have to check it out.
    I'm using XAMPP and it does on my installation.

    When I first noticed the problem some time ago now, I spent some time googling for a solution. All I could find was others experiencing the same problem but no-one had a solution other than the ones already mentioned earlier.

    It's not a big deal really. I just can't understand why MySQL won't update a record unless at least 1 value in that record is being changed by the update command.

    I'm pretty sure that when I was using Oracle, in what seems now to be a previous life time, you could update records even if no values were being changed in a record as described in my earlier scenario.

    Maybe there is something wrong or not configured correctly with my XAMPP.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I don't know. Just tested it, and it gave me no errors.
    Code PHP:
      // Connecting and selecting database 
      $link = mysql_connect(xxx, xxx, xxx) or die("Impossible connecting to DB - error: ".mysql_errno()." - ".mysql_error());
      mysql_select_db(xxx) or die("Impossible selecting DB - error: ".mysql_errno()." - ".mysql_error());
     
      // drop table
      $query = "
        DROP TABLE IF EXISTS test_Employee
      ";
      $result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
     
      // create test table
      $query = "
        CREATE TABLE IF NOT EXISTS test_Employee (
        `idEmployee` VARCHAR(45) NOT NULL ,
        `Name` VARCHAR(255) NULL ,
        `idAddresses` VARCHAR(45) NULL ,
        PRIMARY KEY (`idEmployee`) 
        )
        DEFAULT CHARACTER SET = utf8
        COLLATE = utf8_bin
      ";
      $result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
     
      // add data
      $query = "
        INSERT INTO test_Employee
        VALUES('test', 'test', 'test')
      ";
      $result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
     
      // do an update with the same data
      $query = "
        UPDATE test_Employee
        SET 
            Name = 'test'
          , idAddresses = 'test'
        WHERE         idEmployee = 'test'
      ";
      $result = mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()."<br/>Query:'$query'");
     
      echo "DONE!";

  10. #10
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guido2004.

    I ran your script on my database and it worked as well. So at least I know there is nothing wrong with my XAMPP.

    And.....aaaaahhhhhhhhhhh....after all this time, I think the penny has finally dropped

    I have always used mysql_affected_rows() to check if an update, delete or insert has been successful. If no table values are actually changed by any columns in the update command then mysql_affected_rows() will equal 0 and so I output an error message.

    But I really should just be checking if mysql_query() is returning a value of true or false because whether any column values are actually changed or not by the update doesn't matter because mysql_query() will return true in both cases. It will only return false if a database error occurred.

    Problem solved

    ps....but I'm not sure if this helps the OP at all though

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Kalon View Post
    ps....but I'm not sure if this helps the OP at all though
    At least he knows that updating with the same values doesn't give an error
    If the OP wants/needs more help, he should first respond to Rudy's post:
    Quote Originally Posted by r937 View Post
    there are several scenarios, pick the one you want

    1. an INSERT for a row that already exists is an error
    2. an INSERT for a row that already exists should be ignored
    3. an INSERT for a row that already exists should update only columns values that have changed


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
  •