php - 类内准备好的语句不起作用

Question

我使用下面的代码从我的数据库中获取与用户输入的年份和月份相匹配的数据。

连接:

class ConnectDB{

    private $servername;
    private $username;
    private $password;
    private $dbname;


    protected function connect(){

        $this->servername ="localhost";
        $this->username ="root";
        $this->password ="";
        $this->dbname ="dbexpense";

        $conn = new mysqli($this->servername,$this->username,$this->password,$this->dbname);

            if($conn -> connect_error) {

                die("connection failed:".$conn-> connect_error);
            }

        return $conn;

        }
}

拉取数据的类:

<?php

class SelectAMonthGet extends ConnectDB {

    var $year;
    var $month;


    function __construct( ){

        $this->year = $_POST['year'];
        $this->month = $_POST['analyze_options_month'];
    }

    protected function SelectAMonthGetData(){

    $year =  $this->year; 
    $month = $this->month;


    $sql = $this->connect()->prepare("SELECT * FROM wp_myexpenses WHERE YEAR(date) = ? AND MONTH(date) = ? order by date,id");

    $sql->bind_param("ss",$year,$month);

    $result = $sql ->execute();  

        $numRows = $result->num_rows;

            if($numRows > 0) {

                while ($row = $result->fetch_assoc()){

                $data[] = $row;
                }

                return $data;

            }
    }

}

?>

但是即使数据库中有数据，它也没有显示任何结果。

当我使用没有像下面这样的准备好的语句的代码时，我能够得到一个结果:

没有准备好的语句的代码(工作):

class SelectAMonthGet extends ConnectDB {

    var $year;
    var $month;


    function __construct( ){

        $this->year = $_POST['year'];
        $this->month = $_POST['analyze_options_month'];
    }

    protected function SelectAMonthGetData(){

    $year =  $this->year;; $month = $this->month;
    $sql = "SELECT * FROM wp_myexpenses WHERE YEAR(date) = '$year' AND MONTH(date) = '$month' order by date,id";

        $result = $this->connect()->query($sql);
        $numRows = $result->num_rows;

            if($numRows > 0) {

                while ($row = $result->fetch_assoc()){

                $data[] = $row;
                }

                return $data;

            }
    }

}

?>

我想使用准备好的语句来避免 SQL 注入(inject)。我无法理解我的代码有什么问题？有人可以在这里指导我吗？

更新:正如 ADyson 在评论中所建议的那样，启用了错误报告。它给出以下错误:

Notice: Trying to get property of non-object in **** on line 34

第 34 行:

$numRows = $result->num_rows;

更新:

更改代码以包含如下所示的 get_result()

    protected function SelectAMonthGetData(){

    $year =  $this->year; 
    $month = $this->month; 

    $sql = $this->connect()->prepare("SELECT * FROM wp_myexpenses WHERE YEAR(date) = ? AND MONTH(date) = ? order by date,id");

    $sql->bind_param("ss",$year,$month);

    $sql ->execute();  

$result = $sql->get_result();


        $numRows = $result->num_rows;

            if($numRows > 0) {

                while ($row = $result->fetch_assoc()){

                $data[] = $row;
                }

                return $data;

            }
    }

但现在开始出现错误:

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::get_result() in ****:28

检查一些解决方案后，错误是由于未启用 mysqlnd 驱动程序所致。我的情况是这样。是否有任何替代解决方案来获得它？

Answer 1

下面是两个解决方案，第一个是通过数据库调用类型 mysqli，第二个是通过 PDO

using mysqli prepare with bind_result without get_result()

  function SelectAMonthGetData(){
$year =  $this->year; 
$month = $this->month;
///*****************
$mysqli=$this->connect();
/* Crée une requête préparée */
$stmt = $mysqli->prepare("SELECT date,col2 FROM ets WHERE (YEAR(date) = ? AND MONTH(date) = ?) ");
    /* Lecture des marqueurs */
    $stmt->bind_param("ss", $year,$month);
      $stmt->execute();
     /* bind result variables */
    $stmt->bind_result($date, $col2); // here you can add your columns 

    /* fetch values */
    while ($stmt->fetch()) {
    $row=array();
    $row['date']=$date;
    $row['col2']=$col2;
    $data[] = $row;

    }
    return $data;


                                    }

using mysqli prepare with get_result()

     function SelectAMonthGetData(){
$year =  $this->year; 
    $month = $this->month;

 ///*****************

$mysqli=$this->connect();


/* Crée une requête préparée */
$stmt = $mysqli->prepare("SELECT * FROM ets WHERE YEAR(date) = ? AND MONTH(date) = ? ");

    /* Lecture des marqueurs */
    $stmt->bind_param("ss", $year,$month);

    /* Exécution de la requête */
    $result=$stmt->execute();
    /* instead of bind_result: */
    $result = $stmt->get_result();

    $numRows = $result->num_rows;

            if($numRows > 0) {

                while ($row = $result->fetch_assoc()){

                $data[] = $row;
                }

                print_r($data);
return $data;
            }




return null;







    }

PDO and bind the parameters using : :

$sql = "SELECT * FROM wp_myexpenses WHERE YEAR(date) = :year AND MONTH(date) = :month order by date,id"
    $stmt = $db_pdo->prepare($sql);
    $stmt->execute(Array(':year' => $year, ':month' => $month));
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);