How to subtract a value

[Dinocanid]

This is a very noobish question, but how do you properly subtract a value from all rows in a table? I've tried this:

PHP Code:

$mysidia->db->update("owned_adoptables", array("mood"  => -2)); 


Which changes the value to -2 rather than subtracting 2. And I've tried this:

PHP Code:

$mysidia->db->update("owned_adoptables", array("mood"  - 2)); 


Which doesn't do anything.

[Kyttias]

You'll have to get the current value(s) first and then subtract. The update function just sets the variable to whatever you tell it to, but cannot math at it.

This would be an example of how to reduce the current user's money:

PHP Code:

$moneyleft = $mysidia->user->money - $cost;
$mysidia->db->update("users", array("money" => $moneyleft), "username = '{$adopt->owner}'"); 


Going off that, I can do this (but I'm not sure where your code is so this example goes over the top - I'm specifying exactly which adoptable's mood is being modified):

PHP Code:

$adopt = new OwnedAdoptable($aid);
$mood = $mysidia->db->select("owned_adoptables", array("mood"), "aid = '{$adopt->getAdoptID()}'")->fetchColumn();
$mysidia->db->update("owned_adoptables", array("mood" => $mood - 2), "aid = '{$adopt->getAdoptID()}'"); 


But, again, that might be overboard. The above should work on 'any' page, theoretically. However, $this->adopt->mood might grab the value just fine in levelup.php, or in class_ownedadoptable.php, $this->mood. I'd personally recommend creating a function in class_ownedadoptable.php and calling that from whatever page you need. As mood is a custom variable, you should list it at the top of class_ownedadoptable.php with the other variables if you haven't yet.

PHP Code:

// using "$this->adopt->" in levelup.php:
$mysidia->db->update("owned_adoptables", array("mood" => $this->adopt->mood - 2), "aid = '{$this->adopt->getAdoptID()}'");

// or perhaps just "$this->" in class_ownedadoptable.php:
$mysidia->db->update("owned_adoptables", array("mood" => $this->mood - 2), "aid = '{$this->aid}'"); 


[IntoRain]

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

[draugluin]

Yeah ... that's great ! Thank you for sharing this

[draugluin]

Ohh ... I have a request.

I would like to install a second requirement. So I would like to have :

mood - 2, but only if "is frozen" no.

How is this going ?

thanks in advance

[Dinocanid]

It should be:

PHP Code:

$mysidia->db->update_decrease("owned_adoptables", array("mood"), 2, "isfrozen = no"); 


Or if you're subtracting multiple things, but only if the pet isn't frozen, you can do this:

PHP Code:

if($adopt->isFrozen){
$mysidia->db->update_decrease("owned_adoptables", array("mood"), 2)
//other stuff you might want to subtract can go here...
}; 


[draugluin]

Ah ... great !
it works, but with '

isfrozen = 'no'

thank you, Dinocanid