SQL: Insert only new rows / records into a table?

Question

I regularly process the json channel and should only insert the newest users from the feed and ignore existing users.

I think I need ON DUPLICATE KEY UPDATEor INSERT IGNOREbased on some searches, but I'm not quite sure why I ask - like this:

users
1     John
2     Bob

Partial JSON:

{ userid:1, name:'John' },
{ userid:2, name:'Bob' },
{ userid:3, name:'Jeff' }

From this feed, I only want to insert Jeff. I could make a simple loop for all users and make a simple SELECT query and see if the user ID is already in the table if I do not do INSERT, however I suspect that this will not be an efficient and practical method.

By the way, I use Zend_Db to interact with the database, if someone wants to satisfy a specific answer :) I am not against the general strategic decision, though.

Answer 1

ON DUPLICATE KEY UPDATE :

INSERT INTO table (userid, name) VALUES (2, 'Bobby');
  ON DUPLICATE KEY UPDATE name = 'Bobby';

"Bobby", userid 2 .

INSERT IGNORE, UPDATE:

INSERT INTO table (userid, name) VALUES (2, 'Bobby');
  ON DUPLICATE KEY UPDATE name = name;

, , , INSERT IGNORE.

---

REPLACE:

REPLACE INTO table (userid, name) VALUES (2, 'Bobby');

, 2 . , , .

---

, - MySQL- SQL.

Answer 2

Zend Framework , , try/catch insert :

class Application_Model_DbTable_MyModel extends Zend_Db_Table_Abstract

    public function insert($aData, $bIgnore = false) 
    {

        try 
        {
            $id =  parent::insert($aData);
        } 
        catch(Zend_Db_Statement_Mysqli_Exception $e) 
        {
            // code 1062: Mysqli statement execute error : Duplicate entry
            if($bIgnore && $e->getCode() == 1062) 
            {
                // continue;
            } 
            else 
            {
                throw $e;
            }
        }
        return !empty($id) ? $id : false;
    }
}

Answer 3

userid PRIMARY UNIQUE - :

INSERT IGNORE INTO table (userid, name) VALUES (2, 'Bob');

, .

ON DUPLICATE KEY UPDATE . REPLACE INTO.

REPLACE INTO table (userid, name) VALUES (2, 'Bob');

INSERT, , .

Answer 4

:

/**
 * Perform an insert with the IGNORE word
 *
 * @param $data array
 * @return number the number of rows affected
 */
public function insertIgnore(array $data)
{
    // Start of query
    $sql = sprintf("INSERT IGNORE INTO %s (", $this->getAdapter()->quoteIdentifier($this->info('name')));
    // Retrieve column identifiers
    $identifiers = array_keys($data);
    foreach ($identifiers as $key => $value) {
        // Quote identifier
        $identifiers[$key] = $this->getAdapter()->quoteIdentifier($value);
    }
    // Concat column identifiers
    $sql .= implode(', ', $identifiers);
    $sql .= ") VALUES (";
    foreach ($data as $key => $value) {
        // Quote values
        $data[$key] = $this->getAdapter()->quote($value);
    }
    // Concat values identifiers
    $sql .= implode(', ', $data);
    $sql .= ")";
    // Process the query
    return $this->getAdapter()->query($sql)->rowCount();
}

Answer 5

In the loop, you can perform the update first, if it did not affect the line, it means that this is a new record. Keep track of these “failed updates” and then insert them as new entries.

I am not familiar with Zend_Db, but I assume that it can return if the update affected the number of rows.