ClanCats Logo

Hydrahon

SQL insert

Note: The displayed SQL query in the examples has no prepared statements. In other words the "?" placeholders have been replaced with the actual parameter.

#  Inserting data

// SQL: insert into `people` (`firstname`, `lastname`) values ('Ethan', 'Klein')
$h->table('people')->insert(['firstname' => 'Ethan', 'lastname' => 'Klein'])->execute();

#  Bulk inserting data

When you want to insert multiple rows at once simply pass in a multidimensional array.

// SQL: insert into `people` (`firstname`, `lastname`) values ('Ethan', 'Klein'), ('Hila,' 'Klein')
$h->table('people')->insert([
    ['firstname' => 'Ethan', 'lastname' => 'Klein'],
    ['firstname' => 'Hila', 'lastname' => 'Klein'],
])->execute();

#  Values

The first argument of the insert method simply forwards the given data to the values method.

The values method will always append the given data so you write stuff like this which will generate the exact same query as above.

// SQL: insert into `people` (`firstname`, `lastname`) values ('Ethan', 'Klein'), ('Hila,' 'Klein')
$insert = $h->table('people')->insert();

$insert->values(['firstname' => 'Ethan', 'lastname' => 'Klein']);
$insert->values(['firstname' => 'Hila', 'lastname' => 'Klein']);

$insert->execute();

Add values to the insert

->values(['name' => 'Mario', 'age' => 42])

// you can also add multiple rows
->values([
     ['name' => 'Patrick', 'age' => 24],
     ['name' => 'Valentin', 'age' => 21]
])

Method definition:

public function values(array $values = array())

Arguments

Data type Variable name Comment
array $values The data you want to insert.

Returns

static The current query builder.

#  Ignore

You can toggle insert ignore.

// SQL: insert ignore into `people` (`firstname`, `lastname`) values (Ethan, Klein)
$h->table('people')
    ->insert()
    ->values(['firstname' => 'Ethan', 'lastname' => 'Klein'])
    ->ignore()
    ->execute();

Insert ignore setter

->ignore(true)

Method definition:

public function ignore($ignore = true)

Arguments

Data type Variable name Comment
bool $ignore

Returns

static The current query builder.

#  Reset values

Because values are always appended you need to be able to reset them at some point. For that, we have the resetValues method.

// SQL: insert into `people` (`firstname`, `lastname`) values (Hila, Klein)
$insert = $h->table('people')->insert();

$insert->values(['firstname' => 'Ethan', 'lastname' => 'Klein']);
$insert->resetValues();
$insert->values(['firstname' => 'Hila', 'lastname' => 'Klein']);

$insert->execute();

Resets the current insert values of the query.

Method definition:

public function resetValues()

Returns

static The current query builder.