Updating

New in version 9.3.

Just as SQL SELECT queries, you should avoid plain SQL and use methods provided by the framework from the DB object.

General

Escaping of data is currently provided automatically by the framework for all data passed from GET or POST; you do not have to take care of them (this will change in a future version). You have to take care of escaping data when you use values that came from elsewhere.

The WHERE part of UPDATE and DELETE methods uses the same criteria capabilities than SELECT queries.

Inserting a row

You can insert a row in the database using the insert():

<?php

$DB->insert(
   'glpi_my_table', [
      'a_field'      => 'My value',
      'other_field'  => 'Other value'
   ]
);
// => INSERT INTO `glpi_my_table` (`a_field`, `other_field`) VALUES ('My value', Other value)

An insertOrDie() method is also provided.

Updating a row

You can update rows in the database using the update() method:

<?php

$DB->update(
   'glpi_my_table', [
      'a_field'      => 'My value',
      'other_field'  => 'Other value'
   ], [
      'id' => 42
   ]
);
// => UPDATE `glpi_my_table` SET `a_field` = 'My value', `other_field` = 'Other value' WHERE `id` = 42

An updateOrDie() method is also provided.

New in version 9.3.1.

When issuing an UPDATE query, you can use an ORDER and/or a LIMIT clause along with the where (which remains mandatory). In order to achieve that, use an indexed array with appropriate keys:

<?php
$DB->update(
   'my_table', [
      'my_field'  => 'my value'
   ], [
      'WHERE'  => ['field' => 'value'],
      'ORDER'  => ['date DESC', 'id ASC'],
      'LIMIT'  => 1
   ]
);

Removing a row

You can remove rows from the database using the delete() method:

<?php

$DB->delete(
   'glpi_my_table', [
      'id' => 42
   ]
);
// => DELETE FROM `glpi_my_table` WHERE `id` = 42

Use prepared statements

On some cases, you may want to use prepared statements to improve performances. In order to achieve that, you will have to create a query with some parameters (not named, since mysqli does not supports named parameters), then to prepare it, and finally to bind parameters and execute the statement.

Let’s see an example with an insert statement:

<?php
$insert_query = $DB->buildInsert(
   'my_table', [
      'field'  => new QueryParam(),
      'other'  => new QueryParam()
   ]
);
// => INSERT INTO `glpi_my_table` (`field`, `other`) VALUES (?, ?)
$stmt = $DB->prepare($insert_query);

foreach ($data as $row) {
   $stmt->bind_params(
      'ss',
      $row['field'],
      $row['other']
   );
   $stmt->execute();
}

Just like the buildInsert() method used here, buildUpdate and buildDelete methods are available. They take exactly the same arguments as “non build” methods.

Note

Note the use of the QueryParam object. This is used for the builder to be aware you are not passing a value, but a parameter (that must not be escaped nor quoted).

Preparing a SELECT query is a bit different:

<?php
$it = new DBmysqlIterator();
$it->buildQuery([
   'FROM'   => 'my_table',
   'WHERE'  => [
      'something' => new QueryParam(),
      'foo'       => 'bar'
]);
$query = $it->getSql();
// => SELECT FROM `my_table` WHERE `something` = ? AND `foo` = 'bar'
$stmt = $DB->prepare($query);
// [...]

Creative Commons License