prepared statement inside class is not working

Question

I am using below code to get data from my database which matches the user input year and month.

connection :

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;

        }
}

Class to Pull data:

<?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;

            }
    }

}

?>

But it not showing up any result even though there is data in DB.

I am able to get a result when I use the code without a prepared statement like below:

Code without prepared statement(working):

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;

            }
    }

}

?>

I would like to use a prepared statement to avoid SQL injections. I am unable to understand what's wrong with my code? Can someone guide me here please?

Update: As suggested by ADyson in comments, enabled error reporting. Its giving below error :

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

Line 34:

$numRows = $result->num_rows;

Update:

Changed the code to include get_result() like below

    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;

            }
    }

But now its tarted giving error:

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

Upon checking some of the solution, error is due to mysqlnd driver not enabled. This is true in my case. Will there any alternate solution to get it ?

@UmarAbdullah: yes. I checked the $year and $month values.Its showing as expected. var_dump data :string(4) "2019" string(1) "1" — acr, Jan 09 '19 at 08:26
Enable error reporting: add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors. This will tell you if something is going wrong with your query. — ADyson, Jan 09 '19 at 08:36
Ok thanks. As HamzaNig's updated answer states, you have to use get_result() to fetch the actual result set from the statement. As per the [manual](http://php.net/manual/en/mysqli-stmt.execute.php), the execute() command only returns true or false, not an actual result. This is different behaviour than the query() method. Always a good idea to read the instructions and look at examples! — ADyson, Jan 09 '19 at 09:47

HamzaNig · Answer 1 · 2019-01-09T11:02:04.773

1

Bellow this is two solution first is by your database call type mysqli and the second by 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);

edited Jan 09 '19 at 11:02

answered Jan 09 '19 at 08:32

HamzaNig

958
9
33

From the original code it would appear the OP is using mysqli, not PDO – ADyson Jan 09 '19 at 08:35
thanks yeah your right ,but i think even that it still an answer could help him right ? – HamzaNig Jan 09 '19 at 08:39
Not if he hasn't connected to the DB using PDO, and doesn't want to change all his DB code to use a new library. And mysqli doesn't accept named parameters. – ADyson Jan 09 '19 at 08:39
that's much better – ADyson Jan 09 '19 at 09:38
@HamzaNig: It giving error Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::get_result() . mysqlnd driver is not enabled. Looking at some of the solution, I need to use bind_result & Fetch option – acr Jan 09 '19 at 10:27
https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result . As per this answer, while using bind_result, need to define variables. Is there anyway I can use select all option ? – acr Jan 09 '19 at 10:29
@acr all option directly i think no this option its avaible on get_result() but you can use bind_result and get values but you need to specifie you column as on query as in bind_result then use them on while as answer above – HamzaNig Jan 09 '19 at 11:04
or you can instarll mysqlnd driver and use get_result function – HamzaNig Jan 09 '19 at 11:05

prepared statement inside class is not working

1 Answers1