public static function prepareInsertOrUpdate(string $table, array $values, array $ignoreForUpdate = []): array
    {
        $cols = [];
        $params = [];
        $marks = [];

        foreach ($values as $k => $v) {
            $cols[] = $k;
            $params[] = $v;
            $marks[] = '?';
        }

        $cols = static::escapeSqlWords($cols);
        $ignoreForUpdate = static::escapeSqlWords($ignoreForUpdate);

        $sql = 'INSERT INTO ' . static::escapeSqlWords($table) . ' (';
        $sql .= implode(', ', $cols);
        $sql .= ') VALUES (';
        $sql .= implode(', ', $marks);
        $sql .= ') ON DUPLICATE KEY UPDATE ';

        $updateArray = [];
        foreach ($cols as $col) {
            if (! in_array($col, $ignoreForUpdate, true)) {
                $updateArray[] = "${col}=VALUES(${col})";
            }
        }
        $sql .= implode(', ', $updateArray);
        $sql .= ', id=LAST_INSERT_ID(id)';

        return [
            'sql' => $sql,
            'params' => $params,
        ];
    }
public static function prepareInsertOrUpdate(string $table, array $values, array $ignoreForUpdate = []): array
    {
        $cols = [];
        $params = [];
        $marks = [];

        foreach ($values as $k => $v) {
            $cols[] = $k;
            $params[] = $v;
            $marks[] = '?';
        }

        $cols = static::escapeSqlWords($cols);
        $ignoreForUpdate = static::escapeSqlWords($ignoreForUpdate);

        $sql = 'INSERT INTO ' . static::escapeSqlWords($table) . ' (';
        $sql .= implode(', ', $cols);
        $sql .= ') VALUES (';
        $sql .= implode(', ', $marks);
        $sql .= ') ON DUPLICATE KEY UPDATE ';

        $updateArray = [];
        foreach ($cols as $col) {
            if (! in_array($col, $ignoreForUpdate, true)) {
                $updateArray[] = "${col}=VALUES(${col})";
            }
        }
        $sql .= implode(', ', $updateArray);
        $sql .= ', id=LAST_INSERT_ID(id)';

        return [
            'sql' => $sql,
            'params' => $params,
        ];
    }
public static function prepareInsertOrUpdate ( string $ table , array $ values , array $ ignoreForUpdate = [ ] ) : array { $ cols = [ ] ; $ params = [ ] ; $ marks = [ ] ; foreach ( $ values as $ k => $ v ) { $ cols [ ] = $ k ; $ params [ ] = $ v ; $ marks [ ] = '?' ; } $ cols = static :: escapeSqlWords ( $ cols ) ; $ ignoreForUpdate = static :: escapeSqlWords ( $ ignoreForUpdate ) ; $ sql = 'INSERT INTO ' . static :: escapeSqlWords ( $ table ) . ' (' ; $ sql .= implode ( ', ' , $ cols ) ; $ sql .= ') VALUES (' ; $ sql .= implode ( ', ' , $ marks ) ; $ sql .= ') ON DUPLICATE KEY UPDATE ' ; $ updateArray = [ ] ; foreach ( $ cols as $ col ) { if ( ! in_array ( $ col , $ ignoreForUpdate , true ) ) { $ updateArray [ ] = "${col}=VALUES(${col})" ; } } $ sql .= implode ( ', ' , $ updateArray ) ; $ sql .= ', id=LAST_INSERT_ID(id)' ; return [ 'sql' => $ sql , 'params' => $ params , ] ; }
prepares "INSERT...ON DUPLICATE KEY UPDATE" sql statement by array of parameters

@param string $table           table name
@param array  $values          values
@param array  $ignoreForUpdate columns that should not be updated

@return array [sql, params] prepared SQL and params

@see http://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id
prepares INSERT ... ON DUPLICATE KEY UPDATE sql statement by array of parameters