1. Atlas Database Framework for PHP
  2. Cassini (v3)
  3. Query System
  4. UPDATE

2.4.5. UPDATE

2.4.5.1. Building The Query

2.4.5.1.1. Table

Use the table() method to specify the table to update.

$update->table('foo');

2.4.5.1.2. Columns

You can set a named placeholder and its corresponding bound value using the column() method.

// UPDATE foo SET bar = :bar
$update->column('bar', $bar_value);

Note that the PDO parameter type will automatically be set for strings, integers, floats, and nulls. If you want to set a PDO parameter type yourself, pass it as an optional third parameter.

// UPDATE foo SET bar = :bar
$update->column('bar', $bar_value, \PDO::PARAM_LOB);

You can set several placeholders and their corresponding values all at once by using the columns() method:

// UPDATE foo SET bar = :bar, baz = :baz
$update->columns([
    'bar' => $bar_value,
    'baz' => $baz_value
]);

However, you will not be able to specify a particular PDO parameter type when doing do.

Bound values are automatically quoted and escaped; in some cases, this will be inappropriate, so you can use the raw() method to set column to an unquoted and unescaped expression.

// UPDATE foo SET bar = NOW()
$update->raw('bar', 'NOW()');

2.4.5.1.3. WHERE

(All WHERE methods support implicit and sprintf() inline value binding.)

The Update WHERE methods work just like their equivalent Select methods:

  • where() and andWhere() AND a WHERE condition
  • orWhere() ORs a WHERE condition
  • catWhere() concatenates onto the end of the most-recent WHERE condition
  • whereSprintf() and andWhereSprintf() AND a WHERE condition with sprintf()
  • orWhereSprintf() ORs a WHERE condition with sprintf()
  • catWhereSprintf() concatenates onto the end of the most-recent WHERE condition with sprintf()

2.4.5.1.4. ORDER BY

Some databases (notably MySQL) recognize an ORDER BY clause. You can add one to the Update with the orderBy() method; pass each expression as a variadic argument.

// UPDATE ... ORDER BY foo, bar, baz
$update
    ->orderBy('foo')
    ->orderBy('bar', 'baz');

2.4.5.1.5. LIMIT and OFFSET

Some databases (notably MySQL and SQLite) recognize a LIMIT clause; others (notably SQLite) recognize an additional OFFSET. You can add these to the Update with the limit() and offset() methods:

// LIMIT 10 OFFSET 40
$update
    ->limit(10)
    ->offset(40);

2.4.5.1.6. RETURNING

Some databases (notably PostgreSQL) recognize a RETURNING clause. You can add one to the Update using the returning() method, specifying columns as variadic arguments.

// UPDATE ... RETURNING foo, bar, baz
$update
    ->returning('foo')
    ->returning('bar', 'baz');

2.4.5.1.7. Flags

You can set flags recognized by your database server using the setFlag() method. For example, you can set a MySQL LOW_PRIORITY flag like so:

// UPDATE LOW_PRIORITY foo SET bar = :bar WHERE baz = :__1__
$update
    ->table('foo')
    ->column('bar', $bar_value)
    ->where('baz = ', $baz_value)
    ->setFlag('LOW_PRIORITY');

2.4.5.2. Performing The Query

Once you have built the query, call the perform() method to execute it and get back a PDOStatement.

$pdoStatement = $update->perform();

If you added a RETURNING clause with the returning() method, you can retrieve those column values with the returned PDOStatement:

$pdoStatement = $update->perform();
$values = $pdoStatement->fetch(); // : array