PHP PDO Database Connection Class

Create a php file somewhere on your server and place the code below to have a reusable database class.

//creating a db connection in a static class method
class DBCxn {

/*COMMENT OUT BELOW TO USE HARDCODED VALUES OTHER MAKE SURE TO 
* DEFINE APP_DATABASEHOST, APP_DATABASENAME, APP_DATABASEUSER & APP_DATABASEPASS 
* somewhere before creating the instance of this static object
* public static $host = DHOST;
* public static $dbname = DDBNAME;
* public static $user = DUSER;
* public static $pass = DPASS;
* public static $dsn = "mysql:host=fasssqlpr;dbname=fass_modules_db_v2";
* */

//internal variable to hold the connection
private static $db;
//no cloning or instantiating allowed
final private function __construct() { }
final private function __clone() { }

public static function get() {
//connect if not already connected
if (is_null(self::$db)) {
/* COMMENT IN TO USE VALUES DEFINED WITHIN THE OBJECT
* self::$db = new PDO(self::$dsn, self::$user, self::$pass);
*/
self::$db = new PDO("mysql:host=".APP_DATABASEHOST.";dbname=".APP_DATABASENAME, APP_DATABASEUSER, APP_DATABASEPASS);
//set the response type below
self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
//return the connection
return self::$db;
}

How to use it?

Method 1) Directly accessing it

try {
	$dbc = DBCxn::get();
	$st = $dbc->prepare("SELECT CONCAT('\'', GROUP_CONCAT(DISTINCT(f1) ORDER BY f1 DESC SEPARATOR '\',\''), '\'') AS fieldNameAlias FROM modules ");
	$st->execute();
	$count = $st->rowCount();
	if ($count != 0) {
		$result = $st->fetch(PDO::FETCH_ASSOC);
	}
}catch(PDOException $e){
	echo $e->getMessage();
}

Method 2) Use a data layer, this is another class called db_command(), so paste the code in another file and make sure to include in the script

class db_commands {
 
 /**
 * will display feedback as a list
 * @param unknown $data
 */
 function displayfeedback($data) {
 $feedback = "";
 //feedback
 if (is_array($data["message"])) {
 $feedback .= "<ol>";
 foreach ($data["message"] as $log) {
 $feedback .= "<li>".$log."</li>";
 }
 $feedback .= "</ol>";
 }else {
 $feedback .= "<p>".$data["message"]."</p>";
 }
 }
 
 /**
 * update and inserts to be performed using 
 * @param unknown $query
 * @param unknown $parameters
 * @return multitype:boolean string |multitype:boolean string number NULL
 */
 function execute($query, $parameters) {
 $q = $query;
 $errorCode = 0;
 $affected = 0;
 $last_insert_id = 0;
 
 try {
 $dbc = DBCxn::get();
 //$dbc->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $st = $dbc->prepare($q);
 if (empty($parameters)) {
 $st->execute();
 
 } else {
 $st->execute($parameters);
 }
 
 $affected = $st->rowCount();
 $last_insert_id = $dbc->lastInsertId();
 
 $dbc = NULL;
 
 } catch(PDOException $e) {
 //echo $e;
 $errorCode = 2;
 }
 
 switch ($errorCode) {
 
 case 0:
 return array(
 "response" => true,
 "message" => "Success ".$affected. " row(s) affected.", 
 "affected" => $affected,
 "data" => null
 );
 break;
 default:
 return array(
 "response" => false,
 "message" => "Error: ".$e,
 "affected" => 0,
 "data" => null
 );
 break;
 
 }
 }
 
 /**
 * assumes fetchAll so bear that in mind
 * 
 * @param unknown $query
 * @param unknown $parameters
 * @return multitype:boolean string NULL multitype:
 * fetchmode can be for example: PDO::FETCH_ASSOC etc
 */
 function select($query, $parameters, $fetchmode = "") {
 
 $q = $query;
 $data = null;
 $errorCode = 0;
 $count = 0;
 
 try {
 $dbc = DBCxn::get();
 $st = $dbc->prepare($q);
 //to execute with parameters use $st->execute(array($parameter));
 //to execute without parameters use $st->execute();
 if (empty($parameters)) {
 $st->execute();
 } else {
 $st->execute($parameters);
 }
 
 //echo "<pre>";
 //print_r($st);
 //echo "</pre>";
 
 $count = $st->rowCount();
 //echo $count;
 if ($count != 0) {
 //populate array with item details
 //if you want just first row use $data = $st->fetch(0);
 //if you want all the rows use $data= $st->fetchAll();
 //if you want just the first column use $data =$st->fetchColumn();
 //if you want just the second column use $data =$st->fetchColumn(1);
 
 switch ($fetchmode) {
 case "PDO::FETCH_ASSOC":
 $data = $st->fetchAll(PDO::FETCH_ASSOC);
 break;
 case "":
 $data = $st->fetchAll();
 break;
 }
 
 
 } else {
 $errorCode = 1;
 }
 $dbc = NULL;
 } catch(PDOException $e) {
 //echo $e->getMessage();
 $errorCode = 2;
 }
 //echo "errorcode".$errorCode;
 switch ($errorCode) {
 
 case 0:
 return array(
 "response" => true,
 "message" => "Success",
 "affected" => null,
 "data" => $data,
 "count" => $count
 );
 break;
 case 1:
 return array(
 "response" => false,
 "message" => "Error: no data found from query",
 "affected" => null,
 "data" => array(),
 "count" => $count
 ); 
 break;
 case 2:
 return array(
 "response" => false,
 "message" => "Error unable to perform: ".$e,
 "affected" => null,
 "data" => array(),
 "count" => $count
 );
 break;
 }

 }
 


}

Now you can run select commands like:

$q = "SELECT ".FIELD." FROM ".TABLE." WHERE ".KUIDFIELD."= ?";
$p = array(isset($this->usrdata["fields"]["cn"][0]) ? $this->usrdata["fields"]["cn"][0] : "");
$fm = "PDO::FETCH_ASSOC";
 
$db_command = new db_commands();
$ds = $db_command->select($q, $p, $fm);
$result = "";
if ($ds["count"] >= 1) {
 $result = $ds["data"][0][APP_ADMINS_AUTHLVLFIELD]; //first row result only
}

And you could do an execute, for example INSERT, like this:

 $parameters = array(
 ':cn' => $user['cn'][0],
 ':title' => isset($user['title'][0]) ? $user['title'][0] : ""
);
 
 
//query to insert users (Amend this as necessary for each project)
$q = "INSERT INTO `databasename`.`users` (
 `ID`,
 `UserID`,
 `Title`
 ) 
 VALUES
 (
 NULL,
 :cn,
 :title
 ) 
 ON DUPLICATE KEY UPDATE 
 `Title` = :title
 ";
 
$dbc = new db_commands();
$response = $dbc->execute($q, $parameters);
return $response["response"];