Skip to content

8. 使用 MySQL 进行税费计算练习

我们已经编写了该练习的三个版本。最新版本使用了一个税费计算类。该类从文本文件中获取计算所需的数据。现在，它将从数据库中获取数据。为此，我们将编写一段初始代码，将数据从文本文件导入数据库。

文本文件 imports.txt 内容如下：

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

将创建的数据库如下：

该数据库名为 [dbimpots]，包含一个名为 [impots] 的表。用户 [root] 可无需密码直接访问该数据库。

8.1. 将文本文件导入 MySQL 表（txt2mysql）


<?php
 
// transfers the text file containing the data needed to calculate taxes
// in a mysql table
// the data
$IMPOTS = "impots.txt";
$BASE = "dbimpots";
$TABLE = "impots";
$USER = "root";
$PWD = "";
$HOTE = "localhost";
 
// the data required to calculate the tax has been placed in the $IMPOTS file
// one line per table in the form
// val1:val2:val3,...
list($erreur, $limites, $coeffR, $coeffN) = getTables($IMPOTS);
// was there a mistake?
if ($erreur) {
  print "$erreur\n";
  exit;
}//if
// transfer these tables to a mysql table
$erreur = copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE);
if ($erreur)
  print "$erreur\n";
else
  print "Transfert opéré\n";
// end
exit;
 
// --------------------------------------------------------------------------
function copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE) {
  // copy the 3 numerical tables $limites, $coeffR, $coeffN
  // in table $TABLE of mysql database $BASE
  // the mysql database is on machine $HOTE
  // the user is identified by $USER and $PWD
  // connection
  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";
  // table deletion
  $requête = "drop table $TABLE";
  exécuteRequête($connexion, $requête);
  // table creation
  $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)";
  // filling
  for ($i = 0; $i < count($limites); $i++) {
    // insertion request
    $requête = "insert into $TABLE (limites,coeffR,coeffN) values ($limites[$i],$coeffR[$i],$coeffN[$i])";
    // query execution
    list($erreur, $res) = exécuteRequête($connexion, $requête);
    // return if error
    if ($erreur)
      return "$requête : erreur ($erreur)";
  }//for
  // it's over - log off
  déconnecte($connexion);
  // error-free return
  return "";
}
// --------------------------------------------------------------------------
function getTables($IMPOTS) {
  // $IMPOTS: name of the file containing data from tables $limites, $coeffR, $coeffN
  ...
  // end
  return array("", $limites, $coeffR, $coeffN);
}
// --------------------------------------------------------------------------
function cutNewLinechar($ligne) {
  // delete the end-of-line mark from $ligne if it exists
  ...
  // end
  return($ligne);
}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // connects ($login,$pwd) to base $dsn
  // returns the connection id and an error msg
  ...
}
 
//connect
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // closes the connection identified by $connexion
  ...
}
 
// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executes the $sql request on the $connexion connection
  // returns an array of 2 elements ($erreur,$résultat)
 ...
}

屏幕显示结果：

Transfert opéré

8.2. 税费计算程序（ impots_04）

此版本使用了一个 Impôts 类，该类从数据库中检索计算税款所需的值。在此我们引入一个新概念：预编译语句。DBMS 执行 SQL 语句分为两个阶段：

  1. 查询预处理：DBMS 为该查询准备一个优化的执行计划。目的是尽可能高效地执行该查询。
  1. 执行查询。

如果同一条查询被执行 N 次，前两个步骤将重复执行 N 次。然而，我们可以实现“预备一次，执行 N 次”的效果。为此，必须使用预编译查询。假设 $query 是要执行的 SQL 语句，$connection 是表示连接的 PDO 对象：

  • $statement = $connection->prepare($query) 准备查询并返回“预编译”后的查询
  • $statement->execute() 执行该预编译查询。

如果预编译查询是一个 SELECT 语句，那么

  • $statement->fetchAll() 将 SELECT 查询结果表中的所有行作为数组 T 返回，其中 T[i,j] 表示该表第 i 行第 j 列的值
  • $statement->fetch() 返回表中的当前行，将其作为数组 T 返回，其中 T[j] 是第 j 列在第 j 行中的值

预编译语句的优势不仅在于提高了效率。特别是，它们提供了更高的安全性。因此，应系统地使用它们。


<?php
 
// test -----------------------------------------------------
// definition of constants
$DATA = "data.txt";
$RESULTATS = "resultats.txt";
$TABLE = "impots";
$BASE = "dbimpots";
$USER = "root";
$PWD = "";
$HOTE="localhost";
 
// the data required to calculate the tax has been placed in table mysqL $TABLE
// belonging to the $BASE database. The table has the following structure
// limits decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
// taxable person parameters (marital status, number of children, annual salary)
// were placed in the $DATA text file, one line for each taxpayer
// the results (marital status, number of children, annual salary, tax payable) are placed in the
// the $RESULTATS text file, with one result per line
 
// create a Tax object
$I = new Impôts($HOTE, $USER, $PWD, $BASE, $TABLE);
// was there a mistake?
$erreur=$I->getErreur();
if ($erreur) {
  print "$I->erreur\n";
  exit;
}//if
// create a utilities object
$u = new Utilitaires();
 
// opening taxpayer data files
$data = fopen($DATA, "r");
if (!$data) {
  print "Impossible d'ouvrir en lecture le fichier des données [$DATA]\n";
  exit;
}
 
// open results file
$résultats = fopen($RESULTATS, "w");
if (!$résultats) {
  print "Impossible de créer le fichier des résultats [$RESULTATS]\n";
  exit;
}
 
// the current line of the taxpayer data file is used
while ($ligne = fgets($data, 100)) {
  // remove any end-of-line marker
  $ligne = $u->cutNewLineChar($ligne);
  // we retrieve the 3 fields married,children,salary which form $ligne
  list($marié, $enfants, $salaire) = explode(",", $ligne);
  // tax calculation
  $impôt = $I->calculer($marié, $enfants, $salaire);
  // enter the result
  fputs($résultats, "$marié:$enfants:$salaire:$impôt\n");
  // following data
}
// close files
fclose($data);
fclose($résultats);
 
// end
exit;
 
// ---------------------------------------------------------------------------------
// definition of a Tax class
class Impôts {
 
  // attributes: the 3 data tables
  private $limites;
  private $coeffR;
  private $coeffN;
  private $erreur;
  private $nbLimites;
 
  // getter
  public function getErreur(){
    return $this->erreur;
  }
  // manufacturer
 
  function __construct($HOTE, $USER, $PWD, $BASE, $TABLE) {
    // initializes $limites, $coeffR, $coeffN attributes
    // the data required to calculate the tax has been placed in the mysql table $TABLE
    // belonging to the $BASE database. The table has the following structure
    // limits decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
    // connection to the mysql database on machine $HOTE is made as ($USER,$PWD)
    // initializes the $erreur field with a possible error
    // empty if no error
    // 
    // connection to 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;
    }
    // read table $TABLE
    $requête = "select limites,coeffR,coeffN from $TABLE";
    // executes the $requête request on the $connexion connection
    try {
      $statement = $connexion->prepare($requête);
      $statement->execute();
      // query result evaluation
      while ($colonnes = $statement->fetch()) {
        $this->limites[] = $colonnes[0];
        $this->coeffR[] = $colonnes[1];
        $this->coeffN[] = $colonnes[2];
      }
      // no error
      $this->erreur = "";
      // number of elements in the limit table
      $this->nbLimites = count($this->limites);
    } catch (PDOException $e) {
      $this->erreur = $e->getMessage();
    }
     // disconnect
    déconnecte($connexion);
  }
  // --------------------------------------------------------------------------
  function calculer($marié, $enfants, $salaire) {
    // $marié : yes, no
    // $enfants : number of children
    // $salaire: annual salary
 
  // is the item in good condition?
    if ($this->erreur)
      return -1;
 
    // number of shares
    ...
  }
 
}
 
// ---------------------------------------------------------------------------------
// a class of utility functions
class Utilitaires {
 
  function cutNewLinechar($ligne) {
    // delete the end-of-line mark from $ligne if it exists
    ...
  }
 
}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // connects ($login,$pwd) to base $dsn
  // returns the connection id and an error msg
  ...
}
 
//connect
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // closes the connection identified by $connexion
  ...
}

结果：与之前版本相同。

评论

新功能位于第98至109行：

  • 第 99 行：用于检索计算税款所需数据的 SQL SELECT 语句。
  • 第 102 行：准备 SQL SELECT 语句
  • 第 103 行：执行预编译语句
  • 第 105–109 行：逐行处理 SELECT 语句的结果表
https://stahe.github.io
本教程由 Serge Tahé 编写，根据 知识共享 署名-非商业性使用- ShareAlike 3.0 Unported.