PHP 减少数据库上的口是心非连接

Question

我正在使用 3 个不同的函数来连接我的 MySQL第 1 个 - 检查数据库中是否有字符串的结果第二个 - 根据搜索到的字符串显示一些值第三个 - 在日志中插入一些有关搜索的信息

因此，一般来说，第一个 2 搜索相同的表并进行口是心非，而第三个则连接到相同的数据库以及不同的表。我真的开始认为这不是我的脚本应该工作的方式。因为表非常大，脚本需要花一些时间来搜索它。因此，如果我口是心非，只需要两倍的时间，这是毫无意义的，有人可以给我一些建议，我如何编辑它以使其更快或避免这种情况而不出现安全问题？

一般来说，我在数据库上创建 2x 连接:

数据.php

 public function fetchByVinEvidenceDetail($con) {
     $success = false;
     try{
        $sql = "SELECT * FROM evidence_calculations WHERE vin = :vin LIMIT 5";
        $stmt = $con->prepare( $sql );
        $stmt->bindValue( "vin", $this->vin, PDO::PARAM_STR );
        $stmt->execute();
        echo $this->vin;
            while ($row = $stmt->fetch()){
                echo "<tr>";
                echo "<br>#4";
                echo "<br>".$row['street'];
                echo "<br>".$row['city'];
                echo "<br>".$row['claim_number'];
                echo "<br>".$row['company'];
                echo "<br>".$row['country'];
                echo "<br>".$row['other'];
                echo "<br>".$row['parts'];
                echo "<br>".$row['labor'];
                echo "<br>".$row['calculation_start'];
                echo "<br> -------------------------------- <br>";
            }
            }catch(PDOExeption $e){
            echo $e->getMessage();
            echo $con->errorInfo();
            }

         return $success; 
     }                   

     public function ExistVinEvidence() {
     $success = false;
     try{
        $con = new PDO( DB_HOST, DB_USER, DB_PASS ); 
        $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $sql = "SELECT * FROM evidence_calculations WHERE vin = :vin LIMIT 30";
        $stmt = $con->prepare( $sql );
        $stmt->bindValue( "vin", $this->vin, PDO::PARAM_STR );
        $stmt->execute();
        $count = $stmt->rowCount();
        if ($count>0){
            echo 'For VIN:<b>'.$this->vin.'</b> we found '.$count.' rows in our far history database.</a> <br>';
        }
        else
        {
             echo 'We are sorry but we didnt find any data for VIN <b> '.$this->vin.' in far history database</b> <br>';
        }
        }catch(PDOExeption $e){
         echo $e->getMessage();
         echo $con->errorInfo();
        }
 }

 public function searchLog() {
    $correct = false;
        try {
            $date = new DateTime();
            $con = new PDO( DB_HOST, DB_USER, DB_PASS );
            $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $sql = "INSERT INTO search_log(vin, date, user, ip, search_directory) VALUES(:vin, :date, :user, :ip, :search_directory)";

            $stmt = $con->prepare( $sql );
            $stmt->bindValue( "vin", $this->vin, PDO::PARAM_STR );
            $stmt->bindValue( "date", $date->getTimestamp(), PDO::PARAM_STR );
            $stmt->bindValue( "user", $this->getUsername(), PDO::PARAM_STR );
            $stmt->bindValue( "ip", $this->getUserIP(), PDO::PARAM_STR );
            $stmt->bindValue( "search_directory", "1", PDO::PARAM_STR );
            $stmt->execute();
            return "Logged";
        }catch( PDOException $e ) {
            return $e->getMessage();
        }
 }

sql.php(我将其包含到 data.php 中)

  $con = new PDO( DB_HOST, DB_USER, DB_PASS ); 
        $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

index.php

                    $data = new Data;
                    $data->storeFormValues( $_POST );
                    $data->fetchByVinEvidence();
                    $data->ExistVinEvidence();

它抛出错误UNDEFINED $con

Answer 1

打开一次与数据库的连接，并将 $con 参数传递给您的函数:

$con = new PDO( DB_HOST, DB_USER, DB_PASS );
$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

searchLog($con);
fetchByVinEvidenceDetail($con);
ExistVinEvidence($con);

和搜索日志:

public function searchLog($con) {
    $correct = false;
    try {
        $date = new DateTime();
        $sql = "INSERT INTO search_log(vin, date, user, ip, search_directory) VALUES(:vin, :date, :user, :ip, :search_directory)";

        $stmt = $con->prepare( $sql );
        ...