Um mit PDO (PHP Data Objects) auf SQLite3 zugreifen zu können, musste ich viel im Internet recherchieren. Die wichtigsten Befehle und Code-Schnippsel habe ich für Euch hier zusammengestellt. PDO kann mit den meisten SQL-Datenbanken verwendet werden, ohne dass die verwendeten Befehle angepasst werden müssen!
Mit PDO zur SQLite-Datenbank verbinden
try {
// Use database file "HomeAutomation.sql3" or create a new one if not exists:
$db = new PDO('sqlite:sql/HomeAutomation.sql3');
// Throw exceptions on error
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Create tables, if not exists:
$sql = "CREATE TABLE IF NOT EXISTS SensorValues (VID INTEGER PRIMARY KEY, TStamp TEXT, SName TEXT, Value INTEGER, Sync INTEGER)";
$db->exec($sql);
// Place INSERT, UPDATE or SELECT statements here!
$db= NULL; // Close database
} catch(PDOException $e) {
echo $e->getMessage();
echo $e->getTraceAsString();
}
Es sollte prinzipiell das TRY-CATCH-Konstrukt verwendet werden, damit eventuelle Fehler korrekt abgefangen werden!
Datenbank per SELECT abfragen (PDO)
Ergebnisse eines SELECT-Befehls können mit einer Schleife abgearbeitet werden:
foreach($db->query('SELECT * FROM table') as $row)
{
echo $row['field1'].' '.$row['field2'];
//etc...
}
Alternativ können die einzelnen Records in einem assoziativen Array abgefragt (Index = Feldname) werden:
<?php
$stmt = $db->query('SELECT * FROM table');
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['field1'].' '.$row['field2']; //etc...
}
Alle Records auf einmal abfragen:
<?php
$stmt = $db->query('SELECT * FROM table');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
//use $results
Anzahl der betroffenen Datensätze ermitteln (RowCount)
Wenn man bei einem SELECT oder UPDATE Statement wissen möchte, wie viele Datensätze betroffen waren, verwendet man
<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
ID des zuletzt eingefügten Datensatzes abfragen
Wird nach einem INSERT die Nummer (ID) des neu erzeugten Datensatzes benötigt, verwendet man
<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();
Einfache INSERT, UPDATE und DELETE Anweisungen mit PDO
Man kann die Werte direkt innerhalb einer Query angeben
<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows.' were affected'
Dies empfiehlt sich aus Sicherheitsgründen nicht, weil die Website Opfer einer SQL-Injection werden kann. Ausweg bieten die Prepared Statements in PDO.
Prepared Statements
In der Query werden Platzhalter verwendet, das SQL-Statement also nur vorbereitet (Prepared Statement). Dem Execute() gibt man dann ein Parameter-Array mit. PDO sorgt bei der Ausführung automatisch für die Maskierung gefährlicher Inhalte:
<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
PDO Anweisungen mit numerischen Parametern
Verwendet man den Platzhalter "?" im Query, dann kann man in den Folgezeilen mit 'BindValue' die Wertezuweisung und Datentypzuweisung vornehmen.
<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Beim BindValue() werden die Werte des SQL-Statements von links nach rechts durchnumeriert (links = 1).
PDO Anweisungen mit benannten Parametern
Übersichtlicher kann man es mit Namensplatzhaltern gestalten, welche durch einen führenden ":" gekennzeichnet sind:
<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Prepared Queries INSERT, UPDATE und DELETE
Prepared Statements können bei INSERT, UPDATE und DElETE zum Einsatz kommen. Hier die Befehle für das Anlegen eines neuen Records:
<?php
$stmt = $db->prepare("INSERT INTO table(field1,field2,field3,field4,field5) VALUES(:field1,:field2,:field3,:field4,:field5)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));
$affected_rows = $stmt->rowCount();
Update eines bestehenden Records:
<?php
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();
Löschen eines bestehenden Records:
<?php
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
Prepared Statements in einer Schleife verwenden
Liegen die Werte für ein SQL-Statement in einem Array vor, können sie in einer FOREACH-Schleife mit dem Prepared Statement verwendet werden:
<?php
$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $db->prepare("INSERT INTO table(`name`) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name) {
$stmt->execute();
}
Transaktionen
PDO unterstützt Transaktionen. Werden mehrere Befehle benötigt und diese müssen komplett abgearbeitet sein, bevor die Daten gültig sind, dann verwendet man Transaktionen. Diese werden mit beginTransaction() eingeleitet und mit commit() beendet. Stürzt der Raspberry mitten in der Transaction ab, dann erledigt ein rollBack() das Zurückgehen auf den vorhergehenden Zustand - sprich die Datenbank wird nicht korrupt.
<?php
try {
$db->beginTransaction();
$db->exec("SOME QUERY");
$stmt = $db->prepare("SOME OTHER QUERY?");
$stmt->execute(array($value));
$stmt = $db->prepare("YET ANOTHER QUERY??");
$stmt->execute(array($value2, $value3));
$db->commit();
} catch(PDOException $ex) {
//Something went wrong rollback!
$db->rollBack();
echo $ex->getMessage();
}
Links