Wahllinie - Hinter den Kulissen 1: Datenbank
In vier Notizen der Reihe “Wahllinie - Hinter den Kulissen” werde ich auf die technische Umsetzung des ganzen Projektes eingehen sowie auch viele Sachen für Interessierte erklären. Den Anfang macht das Herz von Wahllinie - die Datenbank.
Da Wahllinie natürlich nur mit den Rohdaten von den verschiedensten OpenData-Schnittstellen alleine nichts anfangen kann, ist natürlich eine Datenbank vonnöten, die die relevanten Daten speichert und so auf die verschiedenen Tabellen aufteilt, dass sie für das Projekt leicht abzurufen sind.
Aufbau der Datenbank
Die Datenbank besteht aus neun Relationen - je eine für Fahrzeugarten, Linien, Haltestellen, Haltestellenstege, Sprengel, Wahlen, Kandidaten, Wahlergebnisse sowie eine Verteilerrelation zwischen Linien, Haltestellen und Sprengel. Diese Aufteilung erlaubt mir auch spätere Wahlen einfach in das System einfügen zu können.
Ein Manko besteht allerdings an der Datenbank: Die Linien und Haltestellen entsprechen nur dem Stand Jetzt. Wie und ob ich die Datenbank dahingehend umbauen werde, dass auch diese Sachen eine Wahl zugeordnet kriegen, ist heute noch offen.
Da die meisten OpenData CSV (wann schafft man das endlich ab?) oder JSON bereits für jeden Eintrag eine ID haben, konnte ich diesen komfortabel auch als Primary Key in diesen Tabellen nehmen. Für alle Wahllinie-spezifischen Relationen (die Kandidaten-Tablle z.B.) wird natürlich ein automatisch generierter Primary Key benutzt.
Befüllen der Datenbank - mySQLi Prepared Statements
Aber natürlich muss so eine Datenbank auch irgendwie mit den Daten aus den OpenData-Schnittstellen gefüllt werden, und womit geht das einfacher als PHP? Ja, möglicherweise als Javaliebhaber JSP - aber wer benutzt das heutzutage noch außer ein paar Großkonzerne (leider) und welcher Webhoster bietet einen günstigen Server mit JSP an (niemand)?
Ich bin mir zwar nicht sicher, was ich alles erklären soll, aber vielleicht schaut hier ja auch der ein oder andere PHP-Anfänger vorbei und findet folgende Sachen ganz hilfreich. Wie holt sich PHP denn nun das JSON vom OpenData Server und wandelt es in ein normales PHP-Array um? Ganz einfach:
$ogd = json_decode(file_get_contents("pfad/zu/ogd.json"), true);
Um das JSON von der OpenData-Schnittstelle zu beziehen, wird file_get_contents() verwendet. Das kann man auch für jede andere Datei machen, die Funktion ist logischerweise nicht nur auf JSONs beschränkt. Um dieses JSON nun zu einem PHP-Array umzuwandeln wird die Funktion json_decode() verwendet, wichtig hierbei ist, dass der zweite Parameter auf true gesetzt wird, da sonst kein assoziatives Array zurückkommt.
Um nun die Daten aus unserem Array in die Datenbank einzufügen, benutze ich weitgehend die mySQLi Prepared Statements - gibt zwar auch PDO, aber damit habe ich mich nicht beschäftigt, da die Datenbank eh eine mySQL-Datenbank ist. Um per mySQLi Prepared Statements auf die Datenbank zugreifen zu können, benötigt PHP natürlich erst einmal eine Verbindung zu dieser - welche wie folgt hergestellt werden kann; ich schreibe PHP Code nur im prozeduralen Stil, ich lehne den objektorientierten nämlich zutiefst ab.
$database = "wahllinie";
$host = "localhost";
$user = $password = "root";
$mysqli = mysqli_connect($host, $user, $password, $database);
Steht erst einmal diese Verbindung zur Verfügung, können nun auch die Prepared Statements erstellt und ausgeführt werden. Der Vorteil an ihnen ist, dass man sie öfters ausführen kann und SQL-Injections ebenfalls vorgebeugt werden, da beim Übergeben der einzufügenden Daten auch immer der jeweilige Datentyp mitgegeben werden muss.
Als Beispiel für ein Prepared Statement nehme ich die Kandidaten zur Hand. Das ist zwar insofern falsch, da ich die Kandidaten einfach über phpMyAdmin eingetragen habe, aber es ist ein zweckmäßiges Beispiel, passt also wieder.
$candidates = array(array(name => "Alexander Van der Bellen", color => "yellow", election => 2), array(name => "Norbert Hofer", color => "blue", election => 2));
$query = "INSERT INTO candidate(name, color, election) VALUES(?,?,?)";
Bei Prepared Statements werden die einzusetzenden Variablen nicht direkt im Query-String mitgegeben, sondern über separate mySQLi Funktionen, welche - wie schon oben erwähnt - SQL-Injections vorbeugen, da so nie ein Query String mit den eingesetzten Variablen entsteht.
$stmt = mysqli_prepare($mysqli, $query); //Zuerst die Verbindung und dann die Query
mysqli_stmt_bind_param($stmt, "ssi", $name, $color, $election);
mysqli_stmt_bind_param() ist eine entscheidende Funktion hierbei. Es wird das Statement angegeben, dann die Datentypen (i = integer, d = double, s = string, b = blob) der zu übergebenden Variablen - wenn diese nicht stimmen, wird das Prepared Statement nicht ausgeführt, und zum Schluss werden die Variablen aufgelistet, die die ? beim Ausführen ersetzen sollen - die Reihenfolge ist unbedingt einzuhalten! Die Variablen die man in dieser Funktion anführt müssen zu diesem Zeitpunkt noch garnicht existieren.
foreach($candidates as $key => $candidate) {
$name = $candidate["name"];
$color = $candidate["color"];
$election = $candidate["election"];
mysqli_stmt_execute($stmt);
}
In einer forEach-Schleife werden alle Kandidaten durchgegangen, die Variablen, die oben mysqli_stmt_bind_param() übergeben wurden ein Wert gegeben, und mit mysqli_stmt_execute() wird das Statement ausgeführt. Im Idealfall hat alles geklappt. Sollte das nicht so sein, kann ganz einfach über
Das war nun also die erste Notiz zur Entwicklung von Wahllinie. Sollten Fragen zu den erklärten Sachen bestehen, nur zu - ich helfe jedem gerne weiter. In der nächsten Notiz werde ich näher beleuchten, wie man mit Prepared Statements etwas aus der Datenbank holt und eine kleine Einführung in die JavaScript-Bibliothek “D3JS” geben.