Direct SQL

You can work with Nette Database in two ways: by writing SQL queries directly (Direct Access) or by letting SQL be generated automatically (Explorer Access). Direct Access allows you to safely build queries while keeping full control over their structure.

For information on creating a connection and configuring it, see the separate page.

Basic Querying

To query database use query() method that returns ResultSet.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

echo $result->getRowCount(); // returns the number of rows if is known

Over the ResultSet is possible to iterate only once, if we need to iterate multiple times, it is necessary to convert the result to the array via fetchAll() method.

You can easily add parameters to the query, note the question mark:

$database->query('SELECT * FROM users WHERE name = ?', $name);

$database->query('SELECT * FROM users WHERE name = ? AND active = ?', $name, $active);

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids is array

In the case of failure query() throws either Nette\Database\DriverException or one of its descendants:

• ConstraintViolationException – violation of any constraint
• ForeignKeyConstraintViolationException – invalid foreign key
• NotNullConstraintViolationException – violation of the NOT NULL condition
• UniqueConstraintViolationException – conflict of unique index

In addition to query(), there are other useful methods:

// returns the associative array id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// returns all rows as array
$rows = $database->fetchAll('SELECT * FROM users');

// returns single row
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// return single field
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

In case of failure, all of these methods throw Nette\Database\DriverException.

Insert, Update & Delete

The parameter that we insert into the SQL query can also be the array (in which case it is possible to skip the wildcard ?), which may be useful for the INSERT statement:

$database->query('INSERT INTO users ?', [ // here can be omitted question mark
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // returns the auto-increment of inserted row

$id = $database->getInsertId($sequence); // or sequence value

Multiple insert:

$database->query('INSERT INTO users', [
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	]
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

We can also pass files or DateTime objects:

$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // or $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserts file contents
]);

Updating rows:

$result = $database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

echo $result->getRowCount(); // returns the number of affected rows

For UPDATE, we can use operators += and -=:

$database->query('UPDATE users SET', [
	'age+=' => 1, // note +=
], 'WHERE id = ?', $id);
// UPDATE users SET `age` = `age` + 1

Deleting:

$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // returns the number of affected rows

Advanced Queries

Insert or update, if it already exists:

$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE', [
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Note that Nette Database recognizes the SQL context in which the array parameter is inserted and builds the SQL code accordingly. So, from the first array he generates (id, name, year) VALUES (123, 'Jim', 1978), while the second converts to name = 'Jim', year = 1978.

We can also describe sorting using array, in keys are column names and values are boolean that determines whether to sort in ascending order:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // ascending
	'name' => false, // descending
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

If the detection did not work, you can specify the form of the assembly with a wildcard ? followed by a hint. These hints are supported:

The WHERE clause uses the ?and operator so conditions are linked by AND:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND `year` = 1978

Which can easily be changed to OR by using the ?or wildcard:

$result = $database->query('SELECT * FROM users WHERE ?or', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978

We can use operators in conditions:

$result = $database->query('SELECT * FROM users WHERE', [
	'name <>' => $name,
	'year >' => $year,
]);
// SELECT * FROM users WHERE `name` <> 'Jim' AND `year` > 1978

And also enumerations:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // enumeration + operator NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

We can also include a piece of custom SQL code using the so-called SQL literal:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Alternatively:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

SQL literal also can have its parameters:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Thanks to which we can create interesting combinations:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Variable Name

There is a ?name wildcard that you use if the table name or column name is a variable. (Beware, do not allow the user to manipulate the content of such a variable):

$table = 'blog.users';
$column = 'name';
$database->query('SELECT * FROM ?name WHERE ?name = ?', $table, $column, $name);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'