Update Script Data Manipulation

From Cloudrexx Development Wiki
Jump to: navigation, search

Always use the method UpdateUtil::sql() for SELECT / INSERT / UPDATE / DELETE-SQL-statements.

The method UpdateUtil::sql() will automatically interrupt the update process and output a proper error description in case of an SQL error.

By using the method UpdateUtil::sql() there is no need to implement any additionaly error handling in the update scripts.

Data Selection

$objResult = UpdateUtil::sql("SELECT `bar` FROM `".DBPREFIX."module_foo`");
if ($objResult->RecordCount()) {
    while (!$objResult->EOF) {
        // perform some action with $objResult
        $objResult->MoveNext();
    }
}

Data Manipulation

UpdateUtil::sql("UPDATE `".DBPREFIX."module_foo` SET `bar` = '123'");

Data Insertion

// The auto increment value of the record being inserted will be retured by UpdateUtil::insert($sql)
$recordId = UpdateUtil::insert("INSERT INTO `".DBPREFIX."module_foo` SET `bar` = '123'");
UpdateUtil::sql("INSERT INTO `".DBPREFIX."module_foo` SET `bar` = '123'");

Avoid duplicate entries

There are two possibilities to avoid duplicate entries.

INSERT IGNORE

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. (MySQL INSERT Syntax)

UpdateUtil::sql("INSERT IGNORE INTO `".DBPREFIX."module_foo` (`id`, `bar`) VALUES (1, 'foo')");

ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. (MySQL ON DUPLICATE KEY UPDATE Syntax)

UpdateUtil::sql("INSERT INTO `".DBPREFIX."_module_foo` (`id`, `bar`) VALUES (1, 'foo2') ON DUPLICATE KEY UPDATE `bar` = 'foo2'")