Skip to content

8. Übung IMPOTS mit MySQL

Wir haben bereits drei Versionen dieser Übung geschrieben. Die letzte Version verwendete eine Klasse zur Steuerberechnung. Diese Klasse bezog die für diese Berechnung erforderlichen Daten aus einer Textdatei. Künftig wird sie diese Daten aus einer Datenbank beziehen. Zu diesem Zweck schreiben wir einen ersten Code, der die Daten aus der Textdatei in eine Datenbank überträgt.

Die Textdatei impots.txt sieht wie folgt aus:

12620:13190:15640:24740:31810:39970:48360:55790:92970:127860:151250:172040:195000:0
0:0.05:0.1:0.15:0.2:0.25:0.3:0.35:0.4:0.45:0.5:0.55:0.6:0.65
0:631:1290.5:272.5:3309.5:4900:6898.5:9316.5:12106:16754.5:23147.5:30710:39312:49062

Die zu erstellende Datenbank sieht wie folgt aus:

Die Datenbank heißt [dbimpots] und enthält eine einzige Tabelle: [impots]. Sie wird vom Benutzer [root] ohne Passwort genutzt.

8.1. Übertragung einer Textdatei in eine Tabelle MySQL (txt2mysql)


<?php

// überträgt die Textdatei mit den für die Steuerberechnung erforderlichen Daten
// in eine MySQL-Tabelle
// Die Daten
$IMPOTS = "impots.txt";
$BASE = "dbimpots";
$TABLE = "impots";
$USER = "root";
$PWD = "";
$HOTE = "localhost";

// Die für die Steuerberechnung erforderlichen Daten wurden in die Datei $IMPOTS gespeichert
// mit jeweils einer Zeile pro Tabelle in der Form
// val1:val2:val3,...
list($erreur, $limites, $coeffR, $coeffN) = getTables($IMPOTS);
// Ist ein Fehler aufgetreten?
if ($erreur) {
  print "$erreur\n";
  exit;
}//if
// werden diese Arrays in eine MySQL-Tabelle übertragen
$erreur = copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE);
if ($erreur)
  print "$erreur\n";
else
  print "Transfert opéré\n";
// Ende
exit;

// --------------------------------------------------------------------------
function copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE) {
  // kopiert die drei numerischen Arrays $limites, $coeffR, $coeffN
  // in die Tabelle $TABLE der MySQL-Datenbank $BASE
  // Die MySQL-Datenbank befindet sich auf dem Rechner $HOTE
  // Der Benutzer wird durch $USER und $PWD identifiziert
  // Verbindung
  list($erreur, $connexion) = connecte("mysql:host=$HOTE;dbname=$BASE", $USER, $PWD);
  if ($erreur)
    return "Erreur lors de la connexion à MySql sous l'identité ($HOTE,$USER,$PWD) : $erreur\n";
  // Löschen der Tabelle
  $requête = "drop table $TABLE";
  exécuteRequête($connexion, $requête);
  // Erstellung der Tabelle
  $requête = "create table $TABLE (limites decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2))";
  list($erreur, $res) = exécuteRequête($connexion, $requête);
  if ($erreur)
    return "$requête : erreur ($erreur)";
  // Befüllung
  for ($i = 0; $i < count($limites); $i++) {
    // Einfügeabfrage
    $requête = "insert into $TABLE (limites,coeffR,coeffN) values ($limites[$i],$coeffR[$i],$coeffN[$i])";
    // Abfrage ausführen
    list($erreur, $res) = exécuteRequête($connexion, $requête);
    // Rückgabe bei Fehler
    if ($erreur)
      return "$requête : erreur ($erreur)";
  }//for
  // Fertig – Verbindung wird getrennt
  déconnecte($connexion);
  // Rückkehr ohne Fehler
  return "";
}
// --------------------------------------------------------------------------
function getTables($IMPOTS) {
  // $IMPOTS: Name der Datei, die die Daten der Tabellen $limites, $coeffR, $coeffN enthält
  ...
  // Ende
  return array("", $limites, $coeffR, $coeffN);
}
// --------------------------------------------------------------------------
function cutNewLinechar($ligne) {
  // die Zeilenende-Markierung von $ligne wird entfernt, falls vorhanden
  ...
  // Ende
  return($ligne);
}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // verbindet ($login,$pwd) mit der Datenbank $dsn
  // gibt die ID der Verbindung sowie eine Fehlermeldung zurück
  ...
}

//stellt eine Verbindung her
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // schließt die durch $connexion identifizierte Verbindung
  ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // führt die Abfrage $sql über die Verbindung $connexion aus
  // gibt ein Array mit 2 Elementen zurück ($erreur, $résultat)
 ...
}

Die Bildschirmausgabe:

Transfert opéré

8.2. Das Programm zur Steuerberechnung ( impots_04)

Diese Version verwendet eine Klasse Impôts, die aus einer Datenbank die für die Steuerberechnung erforderlichen Werte abruft. Hier führen wir einen neuen Begriff ein: den der vorbereiteten Abfrage. Die Ausführung eines Auftrags SQL durch einen SGBD erfolgt in zwei Schritten:

  1. Die Abfrage wird vorbereitet: Der SGBD erstellt einen optimierten Ausführungsplan für die Abfrage. Dabei geht es darum, diese so effizient wie möglich auszuführen.
  1. Die Abfrage wird ausgeführt.

Wird dieselbe Abfrage N-mal ausgeführt, werden die beiden vorgenannten Schritte N-mal durchgeführt. Es ist jedoch möglich, die Abfrage einmal vorzubereiten und N-mal auszuführen. Dazu müssen vorbereitete Abfragen verwendet werden. Wenn $requête der auszuführende Befehl SQL ist und $connexion das Objekt PDO, das die Verbindung darstellt:

  • $statement=$connexion->prepare($requête) bereitet eine Abfrage vor und gibt die „vorbereitete“ Abfrage zurück
  • $statement->execute() führt die vorbereitete Abfrage aus.

Wenn die vorbereitete Abfrage ein Befehl select ist, dann

  • $statement->fetchAll() gibt alle Zeilen der Ergebnistabelle von select in Form eines Arrays T zurück, wobei T[i,j] der Wert der Spalte j der Zeile i der Tabelle ist
  • $statement->fetch() gibt die aktuelle Zeile der Tabelle in Form eines Arrays T zurück, wobei T[j] der Wert der Spalte j der Zeile

Die vorbereitete Abfrage bietet neben einer höheren Effizienz noch weitere Vorteile. Sie sorgt insbesondere für mehr Sicherheit. Daher sollte sie systematisch verwendet werden.


<?php

// Test -----------------------------------------------------
// Definition der Konstanten
$DATA = "data.txt";
$RESULTATS = "resultats.txt";
$TABLE = "impots";
$BASE = "dbimpots";
$USER = "root";
$PWD = "";
$HOTE="localhost";

// Die für die Steuerberechnung erforderlichen Daten wurden in die Tabelle mysqL $TABLE
//, die zur Datenbank $BASE gehört. Die Tabelle hat folgende Struktur
// Grenzwerte decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
// Angaben zu den Steuerpflichtigen (Familienstand, Anzahl der Kinder, Jahreslohn)
// wurden in die Textdatei $DATA mit jeweils einer Zeile pro Steuerpflichtigen geschrieben
// Die Ergebnisse (Familienstand, Anzahl der Kinder, Jahreslohn, zu zahlende Steuer) werden in
// in die Textdatei $RESULTATS, wobei pro Zeile ein Ergebnis steht

// Es wird ein Steuerobjekt angelegt
$I = new Impôts($HOTE, $USER, $PWD, $BASE, $TABLE);
// Ist ein Fehler aufgetreten?
$erreur=$I->getErreur();
if ($erreur) {
  print "$I->erreur\n";
  exit;
}//if
// wird ein Dienstprogramm-Objekt angelegt
$u = new Utilitaires();

// Öffnen der Steuerzahlerdaten-Datei
$data = fopen($DATA, "r");
if (!$data) {
  print "Impossible d'ouvrir en lecture le fichier des données [$DATA]\n";
  exit;
}

// Ergebnisdatei öffnen
$résultats = fopen($RESULTATS, "w");
if (!$résultats) {
  print "Impossible de créer le fichier des résultats [$RESULTATS]\n";
  exit;
}

// Die aktuelle Zeile der Steuerzahlerdatei wird ausgewertet
while ($ligne = fgets($data, 100)) {
  // Das eventuelle Zeilenendezeichen wird entfernt
  $ligne = $u->cutNewLineChar($ligne);
  // Die drei Felder „verheiratet“, „Kinder“ und „Gehalt“, die $ligne bilden, werden abgerufen
  list($marié, $enfants, $salaire) = explode(",", $ligne);
  // Die Steuer wird berechnet
  $impôt = $I->calculer($marié, $enfants, $salaire);
  // Das Ergebnis wird eingetragen
  fputs($résultats, "$marié:$enfants:$salaire:$impôt\n");
  // Nächster Datensatz
}
// Dateien werden geschlossen
fclose($data);
fclose($résultats);

// Ende
exit;

// ---------------------------------------------------------------------------------
// Definition einer Steuerklasse
class Impôts {

  // Attribute: die 3 Datentabellen
  private $limites;
  private $coeffR;
  private $coeffN;
  private $erreur;
  private $nbLimites;

  // Getter
  public function getErreur(){
    return $this->erreur;
  }
  // Konstruktor

  function __construct($HOTE, $USER, $PWD, $BASE, $TABLE) {
    // initialisiert die Attribute $limites, $coeffR, $coeffN
    // Die für die Steuerberechnung erforderlichen Daten wurden in die MySQL-Tabelle $TABLE
    // der Datenbank $BASE. Die Tabelle hat folgende Struktur
    // Grenzwerte decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
    // Die Verbindung zur MySQL-Datenbank des Rechners $HOTE erfolgt unter der Identität ($USER,$PWD)
    // initialisiert das Feld $erreur mit einem eventuellen Fehler
    // wird geleert, wenn kein Fehler vorliegt
    // 
    // Verbindung zur MySQL-Datenbank
    $DSN = "mysql:host=$HOTE;dbname=$BASE";
    list($erreur, $connexion) = connecte($DSN, $USER, $PWD);
    if ($erreur) {
      $this->erreur = "Erreur lors de la connexion à MySql sous l'identité ($HOTE,$USER,$PWD) : $erreur\n";
      return;
    }
    // Auslesen der Tabelle $TABLE
    $requête = "select limites,coeffR,coeffN from $TABLE";
    // führt die Abfrage $requête über die Verbindung $connexion aus
    try {
      $statement = $connexion->prepare($requête);
      $statement->execute();
      // Auswertung des Abfrageergebnisses
      while ($colonnes = $statement->fetch()) {
        $this->limites[] = $colonnes[0];
        $this->coeffR[] = $colonnes[1];
        $this->coeffN[] = $colonnes[2];
      }
      // kein Fehler
      $this->erreur = "";
      // Anzahl der Elemente im Array „limits“
      $this->nbLimites = count($this->limites);
    } catch (PDOException $e) {
      $this->erreur = $e->getMessage();
    }
     // Abmeldung
    déconnecte($connexion);
  }
  // --------------------------------------------------------------------------
  function calculer($marié, $enfants, $salaire) {
    // $marié: ja, nein
    // $enfants: Anzahl der Kinder
    // $salaire: Jahresgehalt
    
  // Befindet sich der Gegenstand in einem ordnungsgemäßen Zustand?
    if ($this->erreur)
      return -1;

    // Anzahl der Anteile
    ...
  }

}

// ---------------------------------------------------------------------------------
// eine Klasse von Hilfsfunktionen
class Utilitaires {

  function cutNewLinechar($ligne) {
    // Das Zeilenendezeichen von $ligne wird entfernt, falls vorhanden
    ...
  }

}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // verbindet ($login, $pwd) mit der Datenbank $dsn
  // gibt die ID der Verbindung sowie eine Fehlermeldung zurück
  ...
}

//stellt eine Verbindung her
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // beendet die durch $connexion identifizierte Verbindung
  ...
}

Ergebnisse: dieselben wie bei den vorherigen Versionen.

Kommentare

Die Neuerungen befinden sich in den Zeilen 98–109:

  • Zeile 99: Der Befehl SQL select, mit dem die für die Steuerberechnung erforderlichen Daten abgerufen werden können.
  • Zeile 102: Vorbereitung des Befehls SQL select
  • Zeile 103: Ausführung des vorbereiteten Auftrags
  • Zeilen 105–109: Zeilenweise Auswertung der Ergebnistabelle von select
https://stahe.github.io
Dieses Tutorial, verfasst von Serge Tahé, wird der Öffentlichkeit unter den Bedingungen der Creative Commons Namensnennung – Nicht kommerziell – ShareAlike 3.0 Unported.