You can also create your own database function to add that functionality from mysql to Mysidia, so you avoid having two transactions, select and then update
class_database.php
PHP Code:
//add this function
public function update_decrease($tableName, array $rows, $value, $clause = NULL){
return $this->_query($tableName, $rows, 'update_decrease', $clause, $value);
}
//modify query function - added $value = NULL
private function _query($tableName, array $data, $operation, $clause = NULL, $value = NULL){
if ( ! is_string($tableName)){
throw new Exception('Argument 1 to ' . __CLASS__ . '::' . __METHOD__ . ' must be a string');
}
// added "update_decrease" to this list
if ( ! in_array($operation, array('insert', 'update', 'update_decrease', 'select', 'select_distinct', 'delete'))){
throw new Exception('Unknown database operation.');
}
// <new code>
if(!$value) {
$query = call_user_func_array(array(&$this, '_' . $operation . '_query'), array($tableName, &$data));
}
else {
$query = call_user_func_array(array(&$this, '_' . $operation . '_query'), array($tableName, &$data, &$value));
}
//</new code>
if ( ! empty($clause)){
$query .= ' WHERE ' . $clause;
}
//The comments can be removed for debugging purposes.
//echo $query;
$stmt = $this->prepare($query);
$this->_bind_data($stmt, $data);
if ( ! $stmt->execute()){
$error = $stmt->errorInfo();
throw new Exception('Database error ' . $error[1] . ' - ' . $error[2]);
}
$this->_total_rows[] = $stmt->rowCount();
return $stmt;
}
//add this function, under _update_query() maybe to keep everything together
private function _update_decrease_query($tableName, &$data, &$num){
$setQuery = array();
foreach ($data as $field){
$setQuery[] = '`' . $field . '` = `' . $field . "` -" . $num;
}
return 'UPDATE ' . $this->_prefix . $tableName . '
SET ' . implode(', ', $setQuery);
}
Now you can do stuff like
PHP Code:
//decrease mood of every owned adoptable by 2
$mysidia->db->update_decrease("owned_adoptables", array("mood"), 2);
//decrease mood and totalclicks by 1, when an adoptable has totalclicks = 1
$mysidia->db->update_decrease("owned_adoptables", array("mood", "totalclicks"), 1, "totalclicks = 1");
It depends on your taste. I've been adding some things from mysql to mysidia's database class in the last few days, that's why I'm giving this option xD