Learning PDO - Database ?

[klemme]

Hi all,

I am looking into PDO, as I want to learn how to use it for future projects.

I am fairly new to OOP, but got the basics by now..

All though I cant get this database class to work :

class Database {

private $host      = 'localhost';
private $user      = 'root';
private $pass      = '';
private $dbname    = 'cms';

private $dbh;
private $error;

public function __construct(){
    // DSN :
    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
    // Options :
    $options = array(
        PDO::ATTR_PERSISTENT    => true,
        PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
    );
    // Opret nyt PDO instance :
    try {
        return $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
    }
    // Eventuelle fejl :
    catch(PDOException $e){
        $this->error = $e->getMessage();
    }
}
}

$db = new Database();

$db->query("SELECT link, url FROM pages WHERE publiceret = 'ja'");

What needs to be fixed in order to use the connection?

Also, I want to use a persistent connection, so I dont create a new connection each time, is this the correct approach to do that? AND is it a good approach to enhance performance? Or is it not really gaining a lot...?

The attempt on the query above results in the following error:

 Fatal error: 
 Call to undefined method Database::query() in C:\wamp\www\classLibrary\PDO\pdo_connection.php     
 on line 37

Best regards, Klemme

[Szabi Zsoldos]

query is not a function/method in this Class, you have to write a query function in your Database class with the PDO query function so that it will work.

[pritaeas]

You can use query if you derive your class from PDO:

class Database implements PDO
{
    // etc.
}

[jkon]

There could be many implementations depending on architectural views. I have worked for many years in one pure OOP language in an architecture where inside the db objects where queries. From that I’ve gained my opinion of never doing so. I will give you a small example of how to keep logic layers separate. Of course there could be a lot more in that example, but lets keep it simple for now ( in real life projects, that shouldn’t be in a single file , but instead using MVC (in my opinion) and never the bad practice that most are calling “scripting” meaning mixing view and code)

<?php
$dbname="testdatabase";
$dbUser="testuser";
$dbPassword = "testpassword";
$host = "localhost";
$db = new Db("mysql:dbname=".$dbname.";host=".$host,$dbUser,$dbPassword);
// Lets say that we have a table that we have only ID (primary) and NAME lets call it "names"
// and we want to get all rows that the name is "John" 
$dataWorker = new Names_Data_Worker($db);
$resultArray = $dataWorker->selectByName("John");
var_dump($resultArray);



class Names_Data_Worker
{
  /**
   * @var Db
   */
  private $db;

  private $selectByNameSql = "SELECT * FROM names WHERE NAME = ?";

  public function __construct(Db $db)
  {
    $this->db = $db;
  }

  public function selectByName($name)
  {
    return $this->db->request($this->selectByNameSql,$name);
  }
}

/**
 This class is just an example , it inherits from PDO and 
 is the minified version of a framework that isn't in air anymore
 and the new one isn't ready yet  
 */
class Db extends PDO
{
  /**
   * The PDO statements that have been used.
   * Keys of the array are the text of the statement.
   * That way PDO statements instantiates only once.
   * @var array of PDOStatement
   */
  private $statements = array();

  /**
   * It takes a sql statement and replacement values for question marks placeholders (if there are any) executes the query by a prepared statement and
   * returns the result (if any) as an multidimentional array with the first key the index num in the row of results, the second the name of result column.
   * @param $statement The query statement
   * @param none,string,array $values The replacement values as array for question marks placeholders if there are any. If there is only one then this can be a string
   * @throws Exception
   * @return null,array Returns the result (if any) as an multidimentional array with the first key the index num in the row of results, the second the name of result column and the value.
   */
  public function request($statement,$values = null)
  {

    $result=array();

    $statement = trim($statement);

    if(is_null($values))
    {
      $values = array();
    }
    if(!is_array($values))
    {
      $values = array($values);
    }

    if(substr_count($statement,"?") != count($values))
    {
      throw new Exception("Prepared Statements Mismatch", 4561);
    }

    if(!isset($this->statements[$statement]))
    {
      $this->statements[$statement] = $this->prepare($statement);
    }

    $this->statements[$statement]->execute($values);
    if($this->statements[$statement]->errorCode()!="00000")
    {
      $error = $this->statements[$statement]->errorInfo();
      throw new Exception($error[2],(int)$error[0]);
    }
    $result = $this->statements[$statement]->fetchAll(PDO::FETCH_ASSOC);

    return $result;
  }
}
?>