PHP: How to use prepare on database object?

Question

I have tried for to make a php database connection using a class which finally works. My Class looks like this:

class Db {
protected static $connection;

public function connect() {

    if(!isset(self::$connection)) {
        self::$connection = new mysqli('localhost','root','','Personenverzeichnis');
    }

    if(self::$connection === false) {
        return false;
    }
    return self::$connection;
}

public function query($query) {
    $connection = $this -> connect();
    $result = $connection -> query($query);
    return $result;
}   

public function select($query) {
    $rows = array();
    $result = $this -> query($query);
    if($result === false) {
        return false;
    }
    while ($row = $result -> fetch_assoc()) {
        $rows[] = $row;
    }
    return $rows;
}

public function error() {
    $connection = $this -> connect();
    return $connection -> error;
}


public function quote($value) {
    $connection = $this -> connect();
    return "'" . $connection -> real_escape_string($value) . "'";
}

}

Then I use this class in my php-Code like this:

$db = new Db();
$abfrage = "SELECT `Date`,`Keyword` FROM `notes` WHERE `PERSON_ID` = 
2 ORDER BY `Date` DESC; ";
$resultat = $db -> query($abfrage);

  while($daten = $resultat->fetch_object() ){
      echo "<p>date ".$daten->Date.": ".$daten->Keyword."</p>";
  }

This way it works perfectly fine. But how can I use prepare() method?

I tried this code which does not work. Can anybody help me?

$db = new Db();
$abfrage = "SELECT `Date`,`Keyword` FROM `notes` WHERE `PERSON_ID` = 2 
ORDER BY `Date` DESC; ";
$resultat = $db->prepare($abfrage);
$resultat->execute();
$resultat->bind_result($date, $keyword);

  while($daten = $resultat->fetch_object() ){
      echo $date .": " .$keywords ."<br>";
  }

The methode prepare is unknown. How to solve this problem?

Answer 1

Since $db is an instance of your Db Class,

$db = new Db();

you can only use the methods defined in the class

class Db {
    public function connect() {}
    public function query($query) {}
    public function select($query) {}
    public function error() {}
    public function quote($value) {}
}

Since you do not have a "prepare" method defined, you won't be able to use prepared statements like you are wanting to do. Wrapping the prepared statements functionality into your Db class would be overly complex and would gain you nothing.

Instead, I would just bypass your Db Class and access the mysqli driver directly:

$db = new mysqli('localhost','root','','Personenverzeichnis');
$abfrage = "SELECT `Date`,`Keyword` FROM `notes` WHERE `PERSON_ID` = 2 
ORDER BY `Date` DESC; ";
$resultat = $db->prepare($abfrage);
$resultat->execute();
$resultat->bind_result($date, $keyword);

  while($daten = $resultat->fetch_object() ){
      echo $date .": " .$keywords ."<br>";
  }

Answer 2

$db, in your case, is an instance of the Db class you created, thus only has the methods that you've defined on it. You haven't defined a prepare method, thus cannot call it. I would add one, similar to the query method, but instead calls mysqli::prepare.

public function prepare($query) {
    $connection = $this -> connect();
    $stmt = $connection -> prepare($query);
    return $stmt;
}

Answer 3

The prepare() function you're speaking of is a method of mysqli, not of your database. The way you implemented your connect method you could just do this:

...
$db = new Db();
$mysqli = $db->connect();
$stmt = $mysqli->prepare( ... );
...