sql update,set?

Discussion in 'Web Design and Development' started by Cabbit, Sep 29, 2008.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    Code:
    $query = "UPDATE `database_table` SET `thingie` = '$thingie' WHERE `id` = '$id'";
    
    I have a script to update a datebase, is there any way to make it create a new record if it can find one to update?
     
  2. Dawdles macrumors newbie

    Joined:
    Sep 27, 2008
    Location:
    Aberdeenshire, Scotland
    #2
    You'll need to query to see if there is an entry first:

    Code:
    SELECT ID
    FROM TableName
    WHERE ID = $ID
    Then see if there are any results. If so, you can perform your UPDATE. If not, do an INSERT:

    HTML:
    INSERT INTO TableName
    VALUES (Value1, Value2)
    *d
     
  3. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #3
    i have been reading about REPLACE INTO is that viable?
     
  4. Dawdles macrumors newbie

    Joined:
    Sep 27, 2008
    Location:
    Aberdeenshire, Scotland
    #4
    That appears to be a MySQL 5, and seems to do exactly what you want. However, from the MySQL Reference Manual:

    So it's like a DELETE and INSERT all in one go. Deletes the row if there is one, then INSERTs a new one. Might be what you want, but you'll lose any information in the fields you are not populating in that transaction.

    Personally, I'd got for a more standard approach like I suggested in the previous post. Much simpler. :)
     
  5. plinden macrumors 68040

    plinden

    Joined:
    Apr 8, 2004
    #5
    I've forgotten much of what I knew about MySQL, but if I recall correctly, you have a couple of choices:
    Code:
    IF EXISTS (SELECT * FROM database_table WHERE id='value')
        UPDATE database_table SET (...) WHERE thingie='value2'
    ELSE
        INSERT INTO database_table VALUES (...)
    or
    Code:
    UPDATE database_table SET (...) WHERE id='value'
    IF @@ROWCOUNT=0
        INSERT INTO database_table VALUES (...)
    The second is supposed to be more efficient if you have large tables.

    You should also parameterize your SQL. If that's what you're using in your php scripts, you're possibly leaving yourself open to SQL injection. For instance, if someone manages to pass in "'; DROP table 'database_table'; commit;" (or something like that) as $thingie, you're in trouble.
     
  6. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #6
    php afide should it look like

    PHP:
    $query = ("IF EXISTS (SELECT * FROM `table` WHERE `table_id` = '$id')
        UPDATE `table` SET `item` = '
    $item' WHERE `table_id` = '$id'
    ELSE
        INSERT INTO `table` VALUES `table` = '
    $id', `item` = '$item'");
    if ( !
    mysql_query$query ) ) { 
        echo 
    mysql_error();
        echo 
    "There was an error, please try again."
     
  7. ChrisA macrumors G4

    Joined:
    Jan 5, 2006
    Location:
    Redondo Beach, California
    #7
    That is absolutely HORRIBLE advice. One of the worst things you can do is to (1) querry the DB, (2) Hold the result in local variable then (3) Make a change to the DB based of the held data.

    The reason is that while you are holding the data the DB can change. You have to think about what if 15 copies of the program were all running at once. Yes this happens if you are backing up a web site with a DBMS and you have 15 users.

    You can TRY and solve this problem with locks. Get a lock before the first query then after you make the change release the lock. But then, only one of those 15 users can access the data base and that monster 8-core server you paid for becomes a wast of money while 14 of your users wait in line.

    You see a lot of code like this, that holds DB info in a local varable used for small-time web sites. With low traffic volume these race conditions don't happen much so they think everything is fine. There are lots of ways people who don't understand the theory of databases can get them selves in trouble. The other is with poorly normalized table designs where data are stored redundantly and the copies can get out of sync.
     
  8. belvdr macrumors 603

    Joined:
    Aug 15, 2005
    #9
    In any RDBMS webapp, we always do an UPDATE, and if it errors out, then we INSERT, as in plinden's second option above. This is a much more efficient option than querying, counting, then doing something (update/insert). You might as well try the UPDATE first, and if a record exists, you've done this in 1 step versus at least 2 any other way.
     
  9. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #10
    sorry for the late post, i am still having problems with this :(

    PHP:
    $query = ('INSERT INTO `key` (`id`, `story_id`, `contains`) VALUES (\'\',\''.$id.'\',\''.$cont.'\')
      ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `contains` = '
    .$cont.';');
    This is what i got so far by following the documentation and i used last_insert_id as my id table is a auto increment. but it keeps just making a duplicate of the last row just with a different id.
     
  10. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #11
    This is what I use, it is tested on MySQL 4 and should work fine on 5 also.

    After the transaction this conveniently returns a column named "Action" which is either "Inserted" or "Updated" and another called named "ID" which is either the last record ID if inserted, or current ID if updated. You adjust the insert/update criteria in the below example, run it twice on an empty table and you'll see the insert on the first run, then the update.

    Code:
    SET @previous_id := NULL ;
    INSERT INTO test( id, note ) VALUES ('1', 'new') 
    ON DUPLICATE KEY UPDATE id = IF( (@previous_id := id) <> NULL IS NULL , VALUES (id), NULL ), note = values(note);
    SELECT if( @previous_id IS NULL , 'Inserted', 'Updated' ) 'Action', if( @previous_id IS NULL , id , @previous_id ) 'ID' from test order by id desc limit 1;
    
    Requirement:
    id as a primary key with auto-increment enabled

    Table structure using above example:

    Code:
    DROP TABLE IF EXISTS `test`;
    CREATE TABLE IF NOT EXISTS `test` (
      `id` int(10) NOT NULL auto_increment,
      `note` varchar(60) default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
    Summary:
    What this query does is set a local variable named previous_id, use "on duplicate key" to perform the insert or update and pass the action data (insert vs. update, which ID) via the select statement. Do not change "IF( (@previous_id := id) <> NULL IS NULL , VALUES (id), NULL )," other than the field name if you use something different. This example uses two fields, obviously use the same syntax and add on more as necessary.

    This is all I could possibly ask for in a query - auto update/insert and a return value I can use to determine what happened and the ID. No issues with MySQL 4 returning wrong last insert ID or worrying about tables being changed running multiple queries in your code.

    -jim
     

Share This Page