Update and select a row in one database request

So far I achieved it by MySQL procedures. At first I needed to create a procedure on MySQL server.

I ran this query on the database server:

Code:

DELIMITER //
CREATE PROCEDURE add_points(IN player_id INT, IN points_num INT)
BEGIN
    UPDATE players SET points = points + points_num WHERE id = player_id LIMIT 1;
    SELECT name, points FROM players WHERE id = player_id LIMIT 1;
END //
DELIMITER ;

Second in my model I added method to call database procedure:

PHP Code:

// ...
public function addPoints($playerId, $pointsNum) {
    $sql = 'CALL add_points(' . (int) playerId . ', ' . (int) $pointsNum . ');';
    $query = $this->query($sql);
    return $query->getRowArray();
}
// ... 


Third from the controller I called a model method:

PHP Code:

// ...
$playerModel->addPoints(1, 100);
// ... 


---

Another useful database queries:

Code:

CALL add_points(1, 100);

Code:

DROP PROCEDURE add_points;

Code:

SHOW PROCEDURE STATUS;