Database

Define your tables in the game database model. Access them through Game rather than through a separate action controller. In a state class, call these methods through $this->game.

The framework owns the request's database transaction. A successful request commits its changes; an exception rolls them back. Do not start or commit a separate transaction from game code. Avoid statements that implicitly commit, such as TRUNCATE; use DELETE FROM when clearing game data during play.

Query methods

DbQuery($sql)

Execute a query, including INSERT, UPDATE, and DELETE. Prefer the methods below for reading results.

getUniqueValueFromDB($sql)

Read one scalar value. Return null when no row matches; more than one row is an error.

getCollectionFromDB($sql, $bSingleValue = false)

Return a map keyed by the first selected column. Each value is the whole row. With $bSingleValue = true and two selected columns, return a map from the first column to the second. The first column must uniquely identify rows; otherwise entries overwrite one another. An empty result is [].

$players = $this->getCollectionFromDB(
    'SELECT player_id AS id, player_name AS name, player_score AS score FROM player'
);
// Example shape: [1000 => ['id' => 1000, 'name' => 'Alice', 'score' => 4]]

$scores = $this->getCollectionFromDB(
    'SELECT player_id, player_score FROM player',
    true,
);
// Example shape: [1000 => 4, 2000 => 7]

getNonEmptyCollectionFromDB($sql)

Read the same map of rows, but throw an exception if it is empty. This method has no single-value flag.

getObjectFromDB($sql)

Read one row as an associative array, or null when no row matches. More than one row is an error. Despite its name, the result is an array, not a PHP object.

getNonEmptyObjectFromDB($sql)

Read one row, throwing an exception if no row matches.

$tileId = (int)$tileId;
$tile = $this->getObjectFromDB("SELECT * FROM tile WHERE tile_id = $tileId");

getObjectListFromDB($sql, $bUniqueValue = false)

Return a numerically indexed list of rows, or []. With $bUniqueValue = true, return a list containing the first selected value of each row. Use this method when the first selected column is not a unique key.

$rows = $this->getObjectListFromDB('SELECT player_id, player_score FROM player');
$playerIds = $this->getObjectListFromDB('SELECT player_id FROM player', true);

getDoubleKeyCollectionFromDB($sql, $bSingleValue = false)

Return a nested map keyed by the first and second selected columns. Each leaf is the row, or the third selected value when $bSingleValue = true.

DbGetLastId()

Return the automatically generated ID of the last inserted row.

DbAffectedRow()

Return the number of rows affected by the last operation.

Escaping player-provided strings

escapeStringForDB($string)

Escape a string before including it in SQL. Put the escaped result inside single quotes. Escaping does not quote the value for you or escape the % and _ wildcards used by LIKE.

$playerId = (int)$playerId;
$escapedWord = $this->escapeStringForDB($word);
$this->DbQuery("UPDATE player SET player_word = '$escapedWord' WHERE player_id = $playerId");

Action parameter types do not replace escaping when building a query from player-provided text.