Skip to content

8. Oefening IMPOTS met MySQL

We hebben al drie versies van deze oefening geschreven. In de laatste versie werd gebruikgemaakt van een klasse voor belastingberekening. Deze klasse haalde de gegevens voor deze berekening uit een tekstbestand. Voortaan haalt ze deze uit een database. Hiervoor schrijven we een eerste stuk code dat de gegevens uit het tekstbestand naar een database overbrengt.

Het tekstbestand impots.txt ziet er als volgt uit:

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

De te bouwen database ziet er als volgt uit:

De database heet [dbimpots] en bevat één enkele tabel: [impots]. Deze wordt beheerd door de gebruiker [root] zonder wachtwoord.

8.1. Overzetten van een tekstbestand naar een tabel MySQL (txt2mysql)


<?php

// stuurt het tekstbestand met de gegevens die nodig zijn voor de belastingberekening door
// naar een MySQL-tabel
// de gegevens
$IMPOTS = "impots.txt";
$BASE = "dbimpots";
$TABLE = "impots";
$USER = "root";
$PWD = "";
$HOTE = "localhost";

// de gegevens die nodig zijn voor de belastingberekening zijn in het bestand $IMPOTS geplaatst
// met één regel per tabel in de vorm
// val1:val2:val3,...
list($erreur, $limites, $coeffR, $coeffN) = getTables($IMPOTS);
// is er een fout opgetreden?
if ($erreur) {
  print "$erreur\n";
  exit;
}//if
// dan worden deze tabellen overgebracht naar een MySQL-tabel
$erreur = copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE);
if ($erreur)
  print "$erreur\n";
else
  print "Transfert opéré\n";
// einde
exit;

// --------------------------------------------------------------------------
function copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE) {
  // kopieert de 3 numerieke tabellen $limites, $coeffR, $coeffN
  // naar de tabel $TABLE in de MySQL-database $BASE
  // de MySQL-database staat op de machine $HOTE
  // de gebruiker wordt geïdentificeerd door $USER en $PWD
  // verbinding
  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";
  // verwijdering van de tabel
  $requête = "drop table $TABLE";
  exécuteRequête($connexion, $requête);
  // tabel aanmaken
  $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)";
  // vullen
  for ($i = 0; $i < count($limites); $i++) {
    // invoerverzoek
    $requête = "insert into $TABLE (limites,coeffR,coeffN) values ($limites[$i],$coeffR[$i],$coeffN[$i])";
    // uitvoering van de query
    list($erreur, $res) = exécuteRequête($connexion, $requête);
    // terugkeren bij fout
    if ($erreur)
      return "$requête : erreur ($erreur)";
  }//for
  // klaar – verbinding verbreken
  déconnecte($connexion);
  // terugkeer zonder fout
  return "";
}
// --------------------------------------------------------------------------
function getTables($IMPOTS) {
  // $IMPOTS: de naam van het bestand met de gegevens uit de tabellen $limites, $coeffR, $coeffN
  ...
  // einde
  return array("", $limites, $coeffR, $coeffN);
}
// --------------------------------------------------------------------------
function cutNewLinechar($ligne) {
  // de regeleindemarkering van $ligne wordt verwijderd, indien deze aanwezig is
  ...
  // einde
  return($ligne);
}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // verbindt ($login,$pwd) met de database $dsn
  // geeft de id van de verbinding en een foutmelding weer
  ...
}

//maakt verbinding
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // sluit de verbinding af die wordt geïdentificeerd door $connexion
  ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // voert de query $sql uit op de verbinding $connexion
  // retourneert een array met 2 elementen ($erreur,$résultat)
 ...
}

De schermresultaten:

Transfert opéré

8.2. Het programma voor de belastingberekening ( impots_04)

Deze versie maakt gebruik van een klasse Impôts die uit een database de waarden ophaalt die nodig zijn voor de belastingberekening. Hier introduceren we een nieuw begrip, namelijk dat van de voorbereide query. De uitvoering van een opdracht SQL door een SGBD verloopt in twee fasen:

  1. de query wordt voorbereid: de SGBD stelt een geoptimaliseerd uitvoeringsplan voor de query op. Het doel is om deze zo efficiënt mogelijk uit te voeren.
  1. de query wordt uitgevoerd.

Als eenzelfde query N keer wordt uitgevoerd, worden de twee voorgaande stappen N keer uitgevoerd. Het is echter mogelijk om de query één keer voor te bereiden en deze vervolgens N keer uit te voeren. Hiervoor moeten voorbereide query’s worden gebruikt. Als $requête de uit te voeren opdracht SQL is en $connexion het object PDO dat de verbinding vertegenwoordigt:

  • $statement=$connexion->prepare($requête) bereidt een query voor en retourneert de "voorbereide" query
  • $statement->execute() voert de voorbereide query uit.

Als de voorbereide query een opdracht is, select, dan

  • $statement->fetchAll() retourneert alle rijen van de resultatentabel van select in de vorm van een array T, waarbij T[i,j] de waarde is van de kolom j van de rij i van de tabel
  • $statement->fetch() retourneert de huidige rij van de tabel in de vorm van een array T, waarbij T[j] de waarde is van de kolom j van de rij

De voorbereide query biedt nog andere voordelen dan alleen een betere efficiëntie. Ze zorgt met name voor meer veiligheid. Daarom zou men deze systematisch moeten gebruiken.


<?php

// test -----------------------------------------------------
// definitie van de constanten
$DATA = "data.txt";
$RESULTATS = "resultats.txt";
$TABLE = "impots";
$BASE = "dbimpots";
$USER = "root";
$PWD = "";
$HOTE="localhost";

// de gegevens die nodig zijn voor de berekening van de belasting zijn in de tabel mysqL $TABLE geplaatst
// die deel uitmaakt van de database $BASE. De tabel heeft de volgende structuur
// grenzen decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
// de parameters van de belastingplichtigen (burgerlijke staat, aantal kinderen, jaarsalaris)
// zijn in het tekstbestand $DATA geplaatst, met één regel per belastingplichtige
// de resultaten (burgerlijke staat, aantal kinderen, jaarsalaris, te betalen belasting) zijn opgeslagen in
// het tekstbestand $RESULTATS, met één resultaat per regel

// er wordt een object ‘Belastingen’ aangemaakt
$I = new Impôts($HOTE, $USER, $PWD, $BASE, $TABLE);
// is er een fout opgetreden?
$erreur=$I->getErreur();
if ($erreur) {
  print "$I->erreur\n";
  exit;
}//if
// wordt een hulpprogramma-object aangemaakt
$u = new Utilitaires();

// het bestand met de gegevens van de belastingplichtigen openen
$data = fopen($DATA, "r");
if (!$data) {
  print "Impossible d'ouvrir en lecture le fichier des données [$DATA]\n";
  exit;
}

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

// de huidige regel van het bestand met belastingplichtigengegevens wordt verwerkt
while ($ligne = fgets($data, 100)) {
  // het eventuele einde-van-regel-teken wordt verwijderd
  $ligne = $u->cutNewLineChar($ligne);
  // de 3 velden ‘gehuwd’, ‘kinderen’ en ‘salaris’ worden opgehaald, die samen $ligne vormen
  list($marié, $enfants, $salaire) = explode(",", $ligne);
  // de belasting wordt berekend
  $impôt = $I->calculer($marié, $enfants, $salaire);
  // het resultaat wordt ingevuld
  fputs($résultats, "$marié:$enfants:$salaire:$impôt\n");
  // volgende gegevens
}
// de bestanden worden gesloten
fclose($data);
fclose($résultats);

// einde
exit;

// ---------------------------------------------------------------------------------
// definitie van een klasse Belastingen
class Impôts {

  // attributen: de 3 gegevenstabellen
  private $limites;
  private $coeffR;
  private $coeffN;
  private $erreur;
  private $nbLimites;

  // getter
  public function getErreur(){
    return $this->erreur;
  }
  // constructor

  function __construct($HOTE, $USER, $PWD, $BASE, $TABLE) {
    // initialiseert de attributen $limites, $coeffR, $coeffN
    // de gegevens die nodig zijn voor de belastingberekening zijn in de MySQL-tabel $TABLE geplaatst
    // die deel uitmaakt van de database $BASE. De tabel heeft de volgende structuur
    // limites decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
    // de verbinding met de MySQL-database van de machine $HOTE wordt tot stand gebracht onder de identiteit ($USER,$PWD)
    // initialiseert het veld $erreur met een eventuele fout
    // wordt leeggemaakt als er geen fout is
    // 
    // verbinding met de MySQL-database
    $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;
    }
    // leest de tabel $TABLE
    $requête = "select limites,coeffR,coeffN from $TABLE";
    // voert de query $requête uit op de verbinding $connexion
    try {
      $statement = $connexion->prepare($requête);
      $statement->execute();
      // verwerking van het resultaat van de query
      while ($colonnes = $statement->fetch()) {
        $this->limites[] = $colonnes[0];
        $this->coeffR[] = $colonnes[1];
        $this->coeffN[] = $colonnes[2];
      }
      // geen fout
      $this->erreur = "";
      // aantal elementen in de limietentabel
      $this->nbLimites = count($this->limites);
    } catch (PDOException $e) {
      $this->erreur = $e->getMessage();
    }
     // verbinding verbroken
    déconnecte($connexion);
  }
  // --------------------------------------------------------------------------
  function calculer($marié, $enfants, $salaire) {
    // $marié: ja, nee
    // $enfants: aantal kinderen
    // $salaire: jaarsalaris
    
  // is het object in goede staat?
    if ($this->erreur)
      return -1;

    // aantal aandelen
    ...
  }

}

// ---------------------------------------------------------------------------------
// een klasse van hulpprogramma's
class Utilitaires {

  function cutNewLinechar($ligne) {
    // het regeleinde van $ligne wordt verwijderd, indien aanwezig
    ...
  }

}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // verbindt ($login,$pwd) met de database $dsn
  // geeft de id van de verbinding en een foutmelding weer
  ...
}

//maakt verbinding
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // verbreekt de verbinding met ID $connexion
  ...
}

Resultaten: dezelfde als bij de vorige versies.

Opmerkingen

De nieuwigheden staan in de regels 98-109:

  • regel 99: de opdracht SQL select waarmee de gegevens kunnen worden opgehaald die nodig zijn voor de berekening van de belasting.
  • regel 102: voorbereiding van de opdracht SQL select
  • regel 103: uitvoering van de voorbereide opdracht
  • regels 105-109: regel voor regel verwerken van de resultatentabel van select
https://stahe.github.io
Deze cursus-tutorial, geschreven door Serge Tahé, wordt ter beschikking gesteld aan het publiek volgens de voorwaarden van de Creative Commons Naamsvermelding – Geen commercieel gebruik – Gedeeld onder dezelfde voorwaarden 3.0 ongewijzigd.