How to rewrite the same mysql table row if the item already exists?

Question

Basically, I wanna go through the entire table from top to bottom. If the item already exists, update the row with new data. If not, append the data to the table. How do I go about implementing this? Thank you

Update: That would be simple in raw query, however, Im using it on a MVC architecture.

Seems like a dup of: http://stackoverflow.com/questions/6853190/php-mysql-update-if-exist-or-insert-if-not — Cory Roy, May 01 '14 at 23:48

liyakat · Accepted Answer · 2014-05-02T04:15:08.990

1

Here i can suggest to use magento inbuilt function insertOnDuplicate

 $bind = array(
           'product_link_attribute_id' => $attributeInfo['id'],
           'link_id'                   => $linkId,
           'value'                     => $value
              );

$adapter->insertOnDuplicate($attributeTable, $bind, array('value'));

where $bind is the array or row value you want to insert and third argument in function should be array('value') and this would be list of column you want to check if value is already exist in table with same value.

Also you can find implementaion of this method in below file

lib\Varien\Db\Adapter\Pdo\Mysql.php

hope this will sure help you.

edited May 02 '14 at 04:15

answered May 02 '14 at 04:07

liyakat

11,825
2
40
46

$adapter = Mage::getSingleton('core/resource')->getConnection('core_write'); so something like that would allow me to loop through the database table and insert only if it's a duplicate? well, how does it determine that's a duplicate column? I want to check if Sku column already has the same value then overwrite the entire row with new data – user2700690 May 02 '14 at 14:58
yes you can add this as describe in my answer as `array('value')` change it to `array('sku')` so if you have already exist sku then it will update row as per your data. – liyakat May 03 '14 at 04:06
Any idea how I could truncate a database table for a module? – user2700690 May 05 '14 at 17:35
you can use this `$this->_db->query('TRUNCATE '. $this->_table);` where `$this->_db` your DB connection object. It would be glad for me if u will vote up my ans so some one can use in future – liyakat May 06 '14 at 04:28
thats exactly what I ended up using. I could also loop through the collection and delete each item however it will not reset auto increment like truncate would. – user2700690 May 06 '14 at 13:02
is this helpful to you ? to achieve your solution ? – liyakat May 07 '14 at 04:03
oh yea thats perfect. ive been using truncate. thank you – user2700690 May 07 '14 at 22:02

score 0 · Answer 2 · answered May 01 '14 at 23:51

the REPLACE command will delete the row if the key exists, and insert the same info afterwards.

This means that any auto incremental key will be changed after performing the operation because it's not the same row. It's a new one with the exact info from the previosly deleted row.

score -1 · Answer 3 · edited May 02 '14 at 00:22

Please check this code :

function sendtodb($data) {
    $invoiceid = $data['invoiceid'];
    if($invoiceid != "") {
        $checkinvoice = mysql_query("SELECT * FROM `paypal_ajustment_data_pure` WHERE `InvoiceID`='$invoiceid'") or die(mysql_error());
        if(mysql_num_rows($checkinvoice) > 0) {
            $paypalreferenceid = $data['paypalreferenceid'];
            $checkpreferenceid =  mysql_query("SELECT * FROM `paypal_ajustment_data_pure` WHERE `InvoiceID`='$invoiceid' AND `PayPalReferenceID`='$paypalreferenceid'") or die(mysql_error());  
            if(mysql_num_rows($checkpreferenceid) == 0)
                $updatereferncceid = mysql_query("UPDATE `paypal_ajustment_data_pure` SET `PayPalReferenceID`='$paypalreferenceid' WHERE `InvoiceID`='$invoiceid'") or die(mysql_error());
        }
        else {
            mysql_query("INSERT INTO `nume_mage`.`paypal_ajustment_data_pure` (`id`, `TransactionID`, `InvoiceID`, `PayPalReferenceID`, `TransactionInitiationDate`, `TransactionCompletionDate`, `status`) VALUES (NULL, '$data[TransactionID]', '$data[invoiceid]', '$data[paypalreferenceid]', '$data[transactionintiationdate]', '$data[transactioncompletedate]', '1')") or die(mysql_error());
        }
        mysql_query("update `paypal_ajustment_data` set status=2 where id= '$data[id]'") or die(mysql_error());
    }
    else {
        mysql_query("update `paypal_ajustment_data` set status=3 where id= '$data[id]'") or die(mysql_error());
    }
}

This code is not for you but this is just to understand logic.. i can show logic so you can understand for code let me know your table name — Sandeep Singh, May 02 '14 at 17:37

How to rewrite the same mysql table row if the item already exists?

3 Answers3