Source Code: dataaccess
DATA ACCESS CLASS
<?php
class dataaccess {
//variable for the MySQL connection obj
var $conn = null;
//this is called each time this class is instantiated
//it creates and returns a MySQL connection object
function __construct(){
$this->conn= new PDO('mysql:host=localhost;dbname=DATABASE_NAME','USERNAME','PASSWORD');
return $conn;
}
//this is call when all of the functions in the class are finished processing
//it disposes of the MySQL connection object
function __destruct(){
$this->conn= null;
}
//test function
function testdb() {
$query = $this->conn->query('SELECT * FROM tblUsers');
foreach($query->fetchAll() as $row) {
print "{$row['userName']} <br />\n";
}
//error info
$arr = $query->errorInfo();
print_r($arr);
//print the SQL Statement
print_r($query);
}
//test function with PDOException Handeling
function testdb2() {
try{
$query = $this->conn->query('SELECT * FROM tblUsers');
foreach($query->fetchAll() as $row) {
print "{$row['userName']} <br />\n";
}
//error info
$arr = $query->errorInfo();
print_r($arr);
//print the SQL Statement
print_r($query);
}catch( PDOException $e ){
//get the PDOException Message
die( $e->getMessage() );
}
}
//Get all of the rows from a table and bind them to an Array
function getAllUsers() {
$users = array();
foreach( $this->conn->query("SELECT * FROM tblUsers ORDER BY userID") as $row ){
$users[$row['userID']] = $row;
}
return $users
}
//get a single random row/column form the database
//and bind it to an array
function getRandomUserID() {
$query = $this->conn->prepare("SELECT userID from tblUser order by rand() limit 1");
$query->execute();
$result = $query->fetch(PDO::FETCH_ASSOC);
return $result;
}
//get a single row using a SQL Parameter and returns the row as an array
function getUserByID($userID) {
$users = array();
$query =$this->conn->prepare("SELECT * FROM tblUsers WHERE tblUsers.userID = ? ");
$query->execute(array($userID));
$users = $query->fetch(PDO::FETCH_ASSOC);
return $users ;
}
//get a user row using 2 SQL Paramaters of roleID and department id
//returns the row as an array
function getUsersByRoleOrDepartment($userRoleID,$userDeptID) {
$users= array();
$query =$this->conn->prepare("SELECT * FROM tblUsers WHERE tblUsers.userRoleID= ? and tblUsers.userDeptID= ?");
$query->execute(array($userRoleID,$userDeptID));
foreach($query->fetchAll() as $row ){
$users[$row['userID']] = $row;
}
return $users;
}
//add a role to the roles table
function addRole($roleName,$roleDescription){
$query = $this->conn->prepare("INSERT INTO tblRoles ( roleName,roleDesc) VALUES ( ?,?)");
$query->execute(array($roleName,$roleDescription));
$returnID = $this->conn->lastInsertId();
return $returnID ;
}
//update a role
function updateRole($roleID,$roleName,$roleDescription){
$query = $this->conn->prepare("UPDATE tblRoles SET roleName = ?, roleDesc = ? WHERE roleID = ?");
$query->execute(array($roleName,$roleDescription,$roleID));
}
//update a role alternet values population
function updateRoleAlt($roleID,$roleName,$roleDescription){
$query = $this->conn->prepare("UPDATE tblRoles SET roleName = ?, roleDesc = ? WHERE roleID = ?");
$values = array();
$values[0] = $roleName;
$values[1] = $roleDescription;
$values[2] = $roleID;
$query->execute($values);
}
//delete a role
function updateRole($roleID){
$query = $this->conn->prepare("DELETE tblRoles WHERE roleID = ?");
$query->execute(array($roleID));
}
}
?>
INDEX.PHP
<?php
//reference the class file
require_once('class.dataaccess.php');
//instanciate the DataAccess Class
$objDB = new dataaccess();
//access a function in the DataAccess Class
$result = $objDB->testdb();
//display the results
print_r($result);
?>
<div class='box'>
<h2>Get All Users</h2>
<?php
$users = $objDB->getAllUsers();
foreach($users as $user){
$user_all_html .= "<p class='box'><strong>".$user['userFullname']."</strong>";
$user_all_html .= "<strong>Email:</strong> ".$user['userEmail']."<br>\n";
$user_all_html .= "<strong>RoleID:</strong> ".$user['userRoleID']."<br>\n";
$user_all_html .= "<strong>DepartmentID:</strong> ".$user['userDeptID']."<br>\n";
$user_all_html .= "<strong>Last Mod:</strong> ".$user['userLastMod']."<br>\n";
$user_all_html .= "</p>\n";
}
echo $user_all_html;
?>
</div>
<div class='box'>
<h2>Get a Single User based on a UserID</h2>
<?php
//get a single user row and display it
$result= $objDB->getUserByID(1);
$user = $result['userID'];
$user_html = "<p class='box'><strong>".$user['userFullname']."</strong>";
$user_html .= "<strong>Email:</strong> ".$user['userEmail']."<br>\n";
$user_html .= "<strong>RoleID:</strong> ".$user['userRoleID']."<br>\n";
$user_html .= "<strong>DepartmentID:</strong> ".$user['userDeptID']."<br>\n";
$user_html .= "<strong>Last Mod:</strong> ".$user['userLastMod']."</p>\n";
echo $user_html;
?>
</div>
<div class='box'>
<h2>Get a All Users based on a Role and Department</h2>
<?php
$users = $objDB->getUsersByRoleOrDepartment(1,2);
foreach($users as $user){
$user_roledept_html .= "<p class='box'><strong>".$user['userFullname']."</strong>";
$user_roledept_html .= "<strong>Email:</strong> ".$user['userEmail']."<br>\n";
$user_roledept_html .= "<strong>RoleID:</strong> ".$user['userRoleID']."<br>\n";
$user_roledept_html .= "<strong>DepartmentID:</strong> ".$user['userDeptID']."<br>\n";
$user_roledept_html .= "<strong>Last Mod:</strong> ".$user['userLastMod']."<br>\n";
$user_roledept_html .= "</p>\n";
}
echo $user_roledept_html;
?>
</div>
SQL
--users table
CREATE TABLE `tblUsers` (
`userID` INT NOT NULL AUTO_INCREMENT,
`userName` VARCHAR( 50 ) NOT NULL ,
`userFullname` VARCHAR( 100 ) NOT NULL ,
`userRoleID` INT NOT NULL ,
`userDeptID` INT NOT NULL ,
`userLastMod` TIMESTAMP NOT NULL ,
PRIMARY KEY ( `userID` ) ,
INDEX ( `userName` )
) TYPE = innodb;
--roles table
CREATE TABLE `tblRoles` (
`roleID` INT NOT NULL AUTO_INCREMENT ,
`roleName` VARCHAR( 20 ) NOT NULL ,
`roleDesc` VARCHAR( 250 ) ,
PRIMARY KEY ( `roleID` ) ,
INDEX ( `roleName` )
) TYPE = innodb;
--departments table
CREATE TABLE `tblDepts` (
`deptID` INT NOT NULL AUTO_INCREMENT ,
`deptName` VARCHAR( 50 ) NOT NULL ,
`deptDesc` VARCHAR( 100 ) ,
PRIMARY KEY ( `deptID` ) ,
INDEX ( `deptName` )
) TYPE = innodb;
