Inserting/Altering Data

DB::insert()

Will INSERT one or more rows into the table. Returns the number of rows that were inserted.

DB::insert('accounts', [
  'username' => 'Joe',
  'password' => 'hello'
]);

You can insert multiple rows at once by passing an array of assoc arrays.

$rows = [];
$rows[] = [
  'username' => 'Frankie',
  'password' => 'abc'
];
$rows[] = [
  'username' => 'Bob',
  'password' => 'def'
];
DB::insert('accounts', $rows);

If you need to run SQL functions within your insert, you can wrap them in DB::sqleval() so they won't be escaped by MeekroDB.

DB::insert('accounts', [
  'username' => 'Joe',
  'password' => 'hello',
  'created_at' => DB::sqleval("NOW()") // NOW() is evaluated by MySQL
]);

You can even use placeholder variables with DB::sqleval().

DB::insert('accounts', [
  'username' => 'Joe',
  'password' => 'hello',
  'data' => DB::sqleval("REPEAT('blah', %i)", 4), // REPEAT('blah', 4) is evaluated by MySQL
]);

DB::insertId()

Returns the auto incrementing ID for the last insert statement.

// accounts.id is an auto-incrementing column
DB::insert('accounts', array(
  'username' => 'Joe',
  'password' => 'hello'
));

$joe_id = DB::insertId(); // which id did it choose?!? tell me!!

DB::insertIgnore()

Works like INSERT, except it does an INSERT IGNORE. Won't give a MySQL error if the primary key is already taken. Returns the number of rows that were inserted.

DB::insertIgnore('accounts', [
  'id' => 5,
  'username' => 'Joe',
  'password' => 'hello'
]);

DB::insertUpdate()

Does an INSERT ... ON DUPLICATE KEY UPDATE. Returns the number of rows that were inserted or affected. In this first example, if the primary key is already taken then we just change the account's password to "hello."

DB::insertUpdate('accounts', [
  'id' => 5,
  'username' => 'Joe',
  'password' => 'hello'
], 'password=%s', 'hello');

We can also pass a second assoc array. If the primary key is already taken, the second assoc array will be used to update those fields. This example does the same thing as the last one.

DB::insertUpdate('accounts', [
  'id' => 5, //primary key
  'username' => 'Joe',
  'password' => 'hello'
], [
  'password' => 'goodbye'
]);

If you just pass a single assoc array, it will update all of its values if the primary key is taken. This is similar to a REPLACE INTO. In this example, if the primary key is taken then both the username and password will be updated.

DB::insertUpdate('accounts', [
  'id' => 5,
  'username' => 'Joe',
  'password' => 'hello'
]);

DB::replace()

Works just like INSERT, but does a REPLACE. Returns the number of rows that were inserted.

// change Joe's password (assuming username is a primary key)
DB::replace('accounts', [
  'username' => 'Joe',
  'password' => 'asd254890s'
]);

DB::update()

This command doesn't add much value over doing an UPDATE with DB::query(), but it's here if you want it. In this example, the three commands are equivalent. Returns the number of rows that were affected.

DB::update('tbl', ['age' => 25, 'height' => 10.99], "name=%s", $name);
DB::update('tbl', ['age' => 25, 'height' => 10.99], ['name' => $name]);
DB::query("UPDATE tbl SET age=%i, height=%d WHERE name=%s", 25, 10.99, $name);

DB::delete()

This command doesn't add much value over doing a DELETE with DB::query(), but it's here if you want it. In this example, the three commands are equivalent. Returns the number of rows that were affected.

DB::delete('tbl', 'username=%s', $name);
DB::delete('tbl', ['username' => $name]);
DB::query("DELETE FROM tbl WHERE username=%s", $name);

DB::affectedRows()

Returns the number of rows changed by the last command.

DB::query("UPDATE accounts SET password=%s WHERE password=%s", 'sdfwsert4rt', 'hello');
$counter = DB::affectedRows();
echo $counter . " people just got their password changed!!\n";