/**
* Method used to update rows in the DB. Rows are selected based
* on selectCriteria and updated using values in updateValues.
* <p>
* Use this method for performing an update of the kind:
* <p>
* WHERE some_column = some value AND could_have_another_column =
* another value AND so on.
*
* @param $selectCriteria A Criteria object containing values used in where
* clause.
* @param $updateValues A Criteria object containing values used in set
* clause.
* @param $con The Connection to use.
* @return int The number of rows affected by last update statement. For most
* uses there is only one update statement executed, so this number
* will correspond to the number of rows affected by the call to this
* method. Note that the return value does require that this information
* is returned (supported) by the Creole db driver.
* @throws PropelException
*/
public static function doUpdate(Criteria $selectCriteria, Criteria $updateValues, Connection $con)
{
$db = Propel::getDB($selectCriteria->getDbName());
$dbMap = Propel::getDatabaseMap($selectCriteria->getDbName());
// Get list of required tables, containing all columns
$tablesColumns = $selectCriteria->getTablesColumns();
// we also need the columns for the update SQL
$updateTablesColumns = $updateValues->getTablesColumns();
$affectedRows = 0;
// initialize this in case the next loop has no iterations.
foreach ($tablesColumns as $tableName => $columns) {
$whereClause = array();
$selectParams = array();
foreach ($columns as $colName) {
$sb = "";
$selectCriteria->getCriterion($colName)->appendPsTo($sb, $selectParams);
$whereClause[] = $sb;
}
$rs = null;
$stmt = null;
try {
$sqlSnippet = implode(" AND ", $whereClause);
if ($selectCriteria->isSingleRecord()) {
// Get affected records.
$sql = "SELECT COUNT(*) FROM " . $tableName . " WHERE " . $sqlSnippet;
$stmt = $con->prepareStatement($sql);
self::populateStmtValues($stmt, $selectParams, $dbMap);
$rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
$rs->next();
if ($rs->getInt(1) > 1) {
$rs->close();
throw new PropelException("Expected to update 1 record, multiple matched.");
}
$rs->close();
}
$sql = "UPDATE " . $tableName . " SET ";
foreach ($updateTablesColumns[$tableName] as $col) {
$sql .= substr($col, strpos($col, '.') + 1) . " = ?,";
}
$sql = substr($sql, 0, -1) . " WHERE " . $sqlSnippet;
Propel::log($sql, Propel::LOG_DEBUG);
$stmt = $con->prepareStatement($sql);
// Replace '?' with the actual values
self::populateStmtValues($stmt, array_merge(self::buildParams($updateTablesColumns[$tableName], $updateValues), $selectParams), $dbMap);
$affectedRows = $stmt->executeUpdate();
$stmt->close();
} catch (Exception $e) {
if ($rs) {
$rs->close();
}
if ($stmt) {
$stmt->close();
}
Propel::log($e->getMessage(), Propel::LOG_ERR);
throw new PropelException("Unable to execute UPDATE statement.", $e);
}
}
// foreach table in the criteria
return $affectedRows;
}
