PHP Classes
elePHPant
Icontem

File: class.dbQuery.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Laurens Ramandt  >  dbQuery - ADO for PHP  >  class.dbQuery.php  >  Download  
File: class.dbQuery.php
Role: Class source
Content type: text/plain
Description: class file
Class: dbQuery - ADO for PHP
Access databases using PDO
Author: By
Last change: Some comments/examples contained the old names of certain methods. This has been adjusted.
Date: 2 years ago
Size: 9,618 bytes
 

Contents

Class file image Download
<?php
/* Class `Semi-ADOŽ
 * This class provides methods for MySQL very similar to those in the ADO class found
 * in Microsoft .NET
 *
 * Written by Laurens Ramandt
 *
 *
 * USAGE:
 * - provide the SQL statement as a constructor parameter
 *
 * - use executeNonQuery for queries which do not return any values such as
 * insertion/deletion queries
 * - use executeScalar if you need a query to return just one value (e.g.
 * password validation)
 * - use executeReader if you need to read multiple rows in a query
 * The result is returned as a two-dimensional array. The fist dimension
 * is the row number, the second one is the field/column number
 *
 * - You can use parameterized queries (cfr SetParamOfType methods).
 *
 * CONFIGURATION: - scroll down to lines 78-88
 *
 * EXAMPLES:
  
     all 3 executing methods can handle parameterized queries
     executeScalar example - used for queries which return maximum one result:
        <CODE>
        $objQry = new dbQuery("SELECT password FROM tblUsers WHERE username=:myusername");
        $objQry->setParamOfTypeString(":myusername", "Administrator");
        $result = $objQry->executeScalar();
        if($result)
        {
            echo "The password of Administrator is " . $result . "<br>";
        }else
        {
            echo "User was not found.<br>";
        }
        </CODE>
        You can re-use the dbQuery object with different parameter values
        

     executeReader example - used for queries returning multiple rows with multiple fields
                            Returns a multidimensional array. The first dimension refers to
                            the row number, the second dimension to the field number.
                             
        <CODE>
        $objQry = new dbQuery("SELECT name FROM tblPupils");
        $result=$objQry->executeReader();
    
        foreach($result as $row){
            echo "Name: " . $row[0] . "<br>";
        }
        </CODE>

    executeNonQuery example - needed to perform a query which doesn't return any value

        <CODE>
        $objQry = new dbQuery("DELETE FROM users WHERE userID=:banneduserid");
        $objQry->setParamOfTypeInteger(":banneduserid", 20);
        $objQry->executeNonQuery();
        </CODE>
*/
        
class dbQuery
{
   
// please provide your SQL connection details below
    
    // optionally provide the computer name of the testing machine, in uppercase letters
   
private $testingMachineName = "TESTINGMACHINENAME";
    
   
// set this to true if you want the class always to use the $online connection details
    // in other words: when you don't have a testing server
   
private $alwaysUseOnlineDatabase = false;
    
   
// testing/development database server
   
private $testingdbUser = 'root';
    private
$testingdbPass = '';
    private
$testingdbHost = 'localhost';
    private
$testingdbName = 'testing_database';
    
    
   
// online/public database server
   
private $onlinedbUser = '';
    private
$onlinedbPass = '';
    private
$onlinedbHost = 'localhost';
    private
$onlinedbName = 'production_database';
        
   
// you don't have to change anything below this line
   
private $dbuser = '';
    private
$dbpass = '';
    private
$dbhost = '';
    private
$dbname = '';
    
   
// constructor and destructor
   
public function __construct($pquery)
    {
        if(
$this->alwaysUseOnlineDatabase)
        {
           
$this->dbuser = $this->onlinedbUser;
           
$this->dbpass = $this->onlinedbPass;
           
$this->dbhost = $this->onlinedbHost;
           
$this->dbname = $this->onlinedbName;
        }else
        {
            if(
strtoupper(getenv('COMPUTERNAME')) == $this->testingMachineName)
            {
               
$this->dbuser = $this->testingdbUser;
               
$this->dbpass = $this->testingdbPass;
               
$this->dbhost = $this->testingdbHost;
               
$this->dbname = $this->testingdbName;
            }else
            {
               
$this->dbuser = $this->onlinedbUser;
               
$this->dbpass = $this->onlinedbPass;
               
$this->dbhost = $this->onlinedbHost;
               
$this->dbname = $this->onlinedbName;
            }
                    
        }
        
       
// set parameter to true for a persistent connection
       
$this->dbConnection = $this->pdoConnect(FALSE);
        
        
        
       
// still save the query in plain text
       
$this->sql = $pquery;
        
       
// parse query
       
if(empty($this->dbConnection)){
            die(
"Class dbQuery could not connect to database. Check config.inc.php and database credentials.");
        }else{
           
$this->stmt = $this->dbConnection->prepare($pquery);
        }
                
        
    }
        
    public function
__destruct()
    {
        unset(
$this->dbConnection);
    }
    
    
    
   
//contains the handler to the database connection
   
private $dbConnection;

    
    private
$sql; // contains the SQL statement in plain text
   
private $stmt; // contains the prepared SQL statement
    

   
private function pdoConnect($persistent = true)
    {
       
// initializes the database connection and returns a handle
        
       
$dbtype = 'mysql';
    
        try
        {
            if (
$persistent)
            {
               
$dbh = new PDO($dbtype.':host='.$this->dbhost.';dbname='.$this->dbname, $this->dbuser,
                   
$this->dbpass, array(PDO::ATTR_PERSISTENT => true));
                return
$dbh;
            }
            else
            {
               
$dbh = new PDO($dbtype.':host='.$this->dbhost.';dbname='.$this->dbname, $this->dbuser,
                   
$this->dbpass);
                return
$dbh;
            }
        }
        catch (
PDOException $e)
        {
            return
false;
        }
    }
    
    
        
   
//parameters
    
    // usage: e.g. $objDbQry->setParamOfTypeString(":name", "Einstein");
   
public function setParamOfTypeString($pname, $pvalue)
    {
           
$this->stmt->bindParam($pname, $pvalue, PDO::PARAM_STR);
    }
    
    public function
setParamOfTypeInteger($pname, $pvalue)
    {
        if(!
is_int($pvalue)){
            try{
               
$pvalue = intval($pvalue, 10);
               
$this->stmt->bindParam($pname, $pvalue, PDO::PARAM_INT);
            } catch(
Exception $e){
                return
false;
            }
        }else{
           
$this->stmt->bindParam($pname, $pvalue, PDO::PARAM_INT);
        }
    }
    
    public function
setParamOfCustomType($pname, $pvalue, $ptype)
    {
           
$this->stmt->bindParam($pname, $pvalue, $ptype);
    }
    
    public function
setParamOfTypeBool($pname, $pvalue)
    {
           
$this->stmt->bindParam($pname, $pvalue, PDO::PARAM_BOOL);
    }
    
    
    public
$rowCount;
   
//executemethods ~scalar / reader / nonquery
    
    // executeScalar returns the first value in the first row of the query result
    // returns false when there is no query result
   
public function executeScalar()
    {
           
$this->stmt->execute();
            
           
$this->rowCount = $this->stmt->rowCount();
            if(
$this->rowCount > 0){
                return
$this->stmt->fetchColumn();
            }else{
                return
false;
            }
    }
    
   
//executeReader returns a multidimensional array containing the query result
    //the first dimension refers to the row number, the second dimension refers to the
    //column number
   
public function executeReader()
    {
            
       
$arrData;
       
$this->rowCount = 0;
              try {
               
$this->stmt->execute();
                    while (
$row = $this->stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
                                  
                        for(
$i=0; $i<=count($row)-1; $i++){
                           
$arrData[$this->rowCount][$i] = $row[$i];
                        }
                      
                         
$this->rowCount++;
                    }
               
$stmt = null;
                
                if(isset(
$arrData))
                    return
$arrData;
            
              }
              catch (
PDOException $e) {
                print
$e->getMessage();
                return
FALSE;
              }
    }
    
   
//executeNonQuery is used for queries which do not need to return any value
    //for example deletion queries. Despite this goal, it still returns the number
    //of rows affected by the query.
   
public function executeNonQuery()
    {
       
$this->stmt->execute();
       
$this->rowCount = $this->stmt->rowCount();
    }
    

   
// returns the auto_increment id of the last inserted row
   
public function getLastInsertID(){
        return
$this->dbConnection->lastInsertId();
    }
}
?>