<?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();
}
}
?>
|
Stumble It!
Bookmark in del.icio.us
Download
