SQL-Weg

Nette Database bietet zwei Wege: Sie können SQL-Queries selbst schreiben (SQL-Weg) oder sie automatisch generieren lassen (siehe Explorer). Der SQL-Weg gibt Ihnen volle Kontrolle über die Queries und sorgt zugleich dafür, dass sie sicher zusammengesetzt werden.

Details zur Datenbankverbindung und -konfiguration finden Sie im Kapitel Verbindung und Konfiguration.

Grundlegende Abfragen

Für Datenbankabfragen wird die Methode query() verwendet. Sie gibt ein Objekt ResultSet zurück, das das Ergebnis der Query repräsentiert. Schlägt die Query fehl, wirft die Methode eine Exception. Das Ergebnis der Query können Sie mit einer foreach-Schleife durchlaufen oder eine der Hilfsmethoden verwenden.

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

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

Um Werte sicher in SQL-Queries einzusetzen, verwenden Sie parametrisierte Queries. Nette Database macht das denkbar einfach: Fügen Sie hinter der SQL-Query einfach ein Komma und den Wert an:

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

Bei mehreren Parametern haben Sie zwei Möglichkeiten: Entweder verschränken Sie die SQL-Query mit den Parametern:

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

Oder Sie schreiben zuerst die gesamte SQL-Query und hängen dann alle Parameter an:

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

Schutz vor SQL-Injection

Warum ist es wichtig, parametrisierte Queries zu verwenden? Weil sie Sie vor einem Angriff namens SQL-Injection schützen, bei dem ein Angreifer eigene SQL-Befehle einschleusen und dadurch Zugriff auf Daten in der Datenbank erlangen oder diese beschädigen könnte.

Setzen Sie Variablen niemals direkt in eine SQL-Query ein! Verwenden Sie immer parametrisierte Queries, die Sie vor SQL-Injection schützen.

// ❌ GEFÄHRLICHER CODE - anfällig für SQL-Injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Sichere parametrisierte Query
$database->query('SELECT * FROM users WHERE name = ?', $name);

Machen Sie sich mit den möglichen Sicherheitsrisiken vertraut.

Abfragetechniken

WHERE-Bedingungen

WHERE-Bedingungen können Sie als assoziatives Array schreiben, dessen Schlüssel Spaltennamen und dessen Werte die Vergleichsdaten sind. Nette Database wählt anhand des Wertetyps automatisch den passendsten SQL-Operator.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

Sie können den Vergleichsoperator auch explizit im Schlüssel angeben:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // verwendet den Operator >
	'name LIKE' => '%John%', // verwendet den Operator LIKE
	'email NOT LIKE' => '%example.com%', // verwendet den Operator NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Nette behandelt Sonderfälle wie null-Werte oder Arrays automatisch.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // verwendet den Operator =
	'category_id' => [1, 2, 3], // verwendet IN
	'description' => null,      // verwendet IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Für negative Bedingungen verwenden Sie den Operator NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // verwendet den Operator !=
	'category_id NOT' => [1, 2, 3], // verwendet NOT IN
	'description NOT' => null,      // verwendet IS NOT NULL
	'id NOT' => [],                 // übersprungen
]);
// WHERE `name` != 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Standardmäßig werden die Bedingungen mit dem Operator AND verknüpft. Das lässt sich mit dem Platzhalter ?or ändern.

ORDER BY-Regeln

Die ORDER BY-Klausel lässt sich mit einem Array schreiben. Geben Sie die Spalten in den Schlüsseln an und verwenden Sie einen booleschen Wert für aufsteigende (true) oder absteigende (false) Sortierung:

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

Einfügen von Daten (INSERT)

Zum Einfügen von Datensätzen wird der SQL-Befehl INSERT verwendet.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

Die Methode getInsertId() gibt die ID der zuletzt eingefügten Zeile zurück. Bei manchen Datenbanken (z. B. PostgreSQL) muss als Parameter der Name der Sequenz angegeben werden, aus der die ID erzeugt werden soll: $database->getInsertId($sequenceId).

Als Parameter können Sie auch Spezielle Werte wie Dateien, DateTime-Objekte oder Enum-Typen übergeben.

Einfügen mehrerer Datensätze auf einmal:

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

Ein mehrfaches INSERT ist deutlich schneller, weil nur eine einzige Datenbankabfrage ausgeführt wird statt vieler einzelner.

Sicherheitshinweis: Verwenden Sie als $values niemals unvalidierte Daten. Machen Sie sich mit den möglichen Risiken vertraut.

Aktualisieren von Daten (UPDATE)

Zum Aktualisieren von Datensätzen wird der SQL-Befehl UPDATE verwendet.

// Aktualisierung eines einzelnen Datensatzes
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Die Anzahl der betroffenen Zeilen gibt $result->getRowCount() zurück.

Bei UPDATE können wir die Operatoren += und -= verwenden:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // erhöht login_count
], 1);

Beispiel für das Einfügen oder Aktualisieren eines Datensatzes, wenn er bereits existiert. Wir verwenden die Technik ON DUPLICATE KEY UPDATE:

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

Beachten Sie, dass Nette Database den Kontext erkennt, in dem ein Array-Parameter im SQL-Befehl verwendet wird, und den SQL-Code entsprechend zusammensetzt. Aus dem ersten Array hat es also (id, name, year) VALUES (123, 'Jim', 1978) gebildet, während es das zweite in die Form name = 'Jim', year = 1978 gebracht hat. Ausführlicher behandeln wir das im Abschnitt Hinweise zur SQL-Konstruktion.

Löschen von Daten (DELETE)

Zum Löschen von Datensätzen wird der SQL-Befehl DELETE verwendet. Beispiel für das Ermitteln der Anzahl gelöschter Zeilen:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

Hinweise zur SQL-Konstruktion

Ein Hinweis ist ein spezieller Platzhalter in einer SQL-Query, der angibt, wie der Wert des Parameters in einen SQL-Ausdruck umgewandelt werden soll:

Hinweis	Beschreibung	Automatisch verwendet bei
`?name`	Dient zum Einsetzen von Tabellen- oder Spaltennamen	–
`?values`	Erzeugt `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Erzeugt Zuweisungen `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Verknüpft Bedingungen in einem Array mit `AND`	`WHERE ?`, `HAVING ?`
`?or`	Verknüpft Bedingungen in einem Array mit `OR`	–
`?order`	Erzeugt die `ORDER BY`-Klausel	`ORDER BY ?`, `GROUP BY ?`

Der Platzhalter ?name dient dazu, Tabellen- und Spaltennamen dynamisch in die Query einzusetzen. Nette Database kümmert sich um das korrekte Quoting der Bezeichner gemäß den Konventionen der Datenbank (in MySQL etwa das Einschließen in Backticks).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (in MySQL)

Achtung: Verwenden Sie den Platzhalter ?name nur für validierte Tabellen- und Spaltennamen. Sonst riskieren Sie Sicherheitslücken.

Die übrigen Hinweise müssen normalerweise nicht angegeben werden, denn Nette verwendet beim Zusammensetzen der SQL-Query eine intelligente Autodetektion (siehe die dritte Spalte der Tabelle). Sie können sie aber zum Beispiel dann einsetzen, wenn Sie Bedingungen mit OR statt mit AND verknüpfen wollen:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Spezielle Werte

Außer den üblichen skalaren Typen (string, int, bool) können Sie als Parameter auch spezielle Werte übergeben:

Dateien: fopen('image.gif', 'r') fügt den binären Inhalt der Datei ein
Datum und Zeit: Objekte vom Typ DateTimeInterface werden in das Datenbankformat umgewandelt
Enum-Typen: Instanzen von enum werden in ihren Wert umgewandelt
SQL-Literale: mit Connection::literal('NOW()') erzeugt, werden direkt in die Query eingefügt

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTimeImmutable, // oder new DateTime
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

Bei Datenbanken, die den Datentyp datetime nicht nativ unterstützen (wie SQLite und Oracle), werden DateTime- und DateTimeImmutable-Objekte in einen Wert umgewandelt, der in der Datenbankkonfiguration durch den Eintrag formatDateTime festgelegt ist (Standardwert ist U – Unix-Timestamp).

SQL-Literale

In manchen Fällen müssen Sie rohen SQL-Code als Wert übergeben, der nicht als String behandelt und escapt werden soll. Dazu dienen Objekte der Klasse Nette\Database\SqlLiteral. Sie werden mit der Methode Connection::literal() erzeugt.

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

Alternativ:

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

SQL-Literale können Parameter enthalten:

$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)

Das erlaubt interessante Kombinationen:

$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')

Daten abrufen

Abkürzungen für SELECT-Abfragen

Um das Laden von Daten zu vereinfachen, bietet Connection mehrere Abkürzungen an, die einen Aufruf von query() mit einem anschließenden fetch*() kombinieren. Diese Methoden nehmen dieselben Parameter entgegen wie query(), also eine SQL-Query und optionale Parameter. Eine vollständige Beschreibung der fetch*()-Methoden finden Sie weiter unten.

`fetch($sql, ...$params): ?Row`	Führt die Query aus und gibt die erste Zeile als `Row`-Objekt oder `null` zurück.
`fetchAll($sql, ...$params): array`	Führt die Query aus und gibt alle Zeilen als Array von `Row`-Objekten zurück.
`fetchPairs($sql, ...$params): array`	Führt die Query aus und gibt ein assoziatives Array zurück (Schlüssel-Wert-Paare).
`fetchField($sql, ...$params): mixed`	Führt die Query aus und gibt den Wert der ersten Spalte der ersten Zeile zurück.
`fetchList($sql, ...$params): ?array`	Führt die Query aus und gibt die erste Zeile als indiziertes Array oder `null` zurück.

Beispiel:

// fetchField() - gibt den Wert der ersten Zelle zurück
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – Iteration über Zeilen

Nach dem Ausführen einer Query wird ein Objekt ResultSet zurückgegeben, das mehrere Wege bietet, die Ergebnisse zu durchlaufen. Der einfachste Weg, eine Query auszuführen und die Zeilen zu erhalten, ist die Iteration in einer foreach-Schleife. Diese Methode ist am speicherschonendsten, denn sie holt die Daten Zeile für Zeile und lädt nicht das gesamte Ergebnis auf einmal in den Speicher.

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

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

Das ResultSet lässt sich nur einmal durchlaufen. Wenn Sie es mehrfach durchlaufen müssen, müssen Sie die Daten zuerst in ein Array laden, zum Beispiel mit der Methode fetchAll().

fetch(): ?Row

Gibt eine Zeile als Row-Objekt zurück. Wenn es keine weiteren Zeilen gibt, wird null zurückgegeben. Der interne Zeiger rückt auf die nächste Zeile vor.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // lädt die erste Zeile
if ($row) {
	echo $row->name;
}

fetchAll(): array

Gibt alle verbleibenden Zeilen des ResultSet als Array von Row-Objekten zurück.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // lädt alle Zeilen
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Gibt das Ergebnis als assoziatives Array zurück. Das erste Argument bestimmt die Spalte, die als Schlüssel verwendet wird, das zweite Argument die Spalte, die als Wert verwendet wird:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Wenn nur der erste Parameter ($key) angegeben wird, ist der Wert die gesamte Zeile (das Row-Objekt):

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

Bei doppelten Schlüsseln wird der Wert aus der letzten Zeile verwendet. Wird null als Schlüssel verwendet, entsteht ein numerisch (ab null) indiziertes Array, sodass keine Schlüsselkollisionen auftreten:

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Alternativ können Sie einen Callback angeben, der jede Zeile verarbeitet. Der Callback kann einen einzelnen Wert oder ein Schlüssel-Wert-Paar zurückgeben.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// Der Callback kann auch ein Array mit einem Schlüssel-Wert-Paar zurückgeben:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Gibt den Wert der ersten Spalte der aktuellen Zeile zurück. Wenn es keine weiteren Zeilen gibt, wird null zurückgegeben. Der interne Zeiger rückt auf die nächste Zeile vor.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // lädt den Namen aus der ersten Zeile

fetchList(): ?array

Gibt die Zeile als indiziertes Array zurück. Wenn es keine weiteren Zeilen gibt, wird null zurückgegeben. Der interne Zeiger rückt auf die nächste Zeile vor.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Gibt die Anzahl der betroffenen Zeilen der letzten UPDATE- oder DELETE-Query zurück. Bei SELECT-Queries gibt sie die Anzahl der Zeilen im Ergebnis zurück. Diese muss allerdings nicht immer bekannt sein; in diesem Fall gibt die Methode null zurück.

getColumnCount(): ?int

Gibt die Anzahl der Spalten im ResultSet zurück.

Informationen zu Abfragen

Zu Debugging-Zwecken können wir Informationen über die zuletzt ausgeführte Query erhalten:

echo $database->getLastQueryString();   // gibt die SQL-Query aus

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // gibt die SQL-Query aus
echo $result->getTime();           // gibt die Ausführungszeit in Sekunden aus

Um das Ergebnis als HTML-Tabelle anzuzeigen, können Sie verwenden:

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

ResultSet bietet Informationen über die Spaltentypen:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column ist vom Typ $type"; // z. B. 'id ist vom Typ int'
}

Abfrage-Protokollierung

Wir können eine eigene Protokollierung der Queries implementieren. Das Event onQuery ist ein Array von Callbacks, die nach jeder ausgeführten Query aufgerufen werden:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Time: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');
	}
};