I decided to help out other developers either getting into PHP or experienced ones that are just looking for a different way to make fairly easy and clean database connection and queries.
I try to write a majority of my SQL in stored procedures, but that is just me. This way I can cut down as much as possible with SQL Injection. It is sometimes a bit more work, especially if you are using the current MySQL Clustered environment, where you have to edit the stored procedure on each SQL node you have in the cluster, hopefully in a future release they will make is single edit and it updates everywhere. For this example though, I will not be using stored procedures. This way you can see the sql code being used.
For this example, I have my "framework" setup in the following format:
- components (FOLDER)
-- Database.php (FILE)
-- iTemplate.php (FILE)
-- User.php (FILE)
- incs (FOLDER)
-- constants.php (FILE)
-- functions.php (FILE)
index.php (FILE)
For this article, I am not going to go into all the security of adding a blank index.php page to each folder to stop folder browsing from the web, as well as putting the constants.php file outside of the root directory but still in the include path, we will leave that for another time.
If you copy and paste any of this code, please ignore the multiple ######## BEGIN X.PHP ########, it is merely an identifier so that you can see where the code begins and ends for each page. You will also notice, that I do not close my pages with a ?> The reason I do this, is so that I eleminate any whitespace from the end of each code segment that might cause an error on display or code.
First off, we have the constants.php file. This file is currently setup to work on two different environments, my local development environement (127.0.0.1) and the production environment, anywhere else. It checks to see the address of where the code is being called from and then it determines which constants to use for the site.
The functions.php file is just where I keep any functions that I use more than once throughout the site. The only one in here for this example is the autoload one so that I do not have to constantly load each object. The __autoload function is a new feature of php5 that will load whatever object you need as long as you call it correctly.
Next, we have the iTemplate.php. This is simple a template for all my objects to follow. Adding the implement tage to each object forces each object to implement specific functions that are declared. I try to make sure each object has an INSERT, UPDATE and DELETE function.
The Database.php is the core of the code. This is where all the heavy-lifting is done. We currently have this one setup for MySQL.
Our User.php which implements iTemplate.php and extends the Database.php will have any and all code that pertains to the User table in the database. I generally have one object for each table in the database. Some of the code takes a bit to setup in the beginning, but believe me, once everything is setup, it is VERY easy to come back and make changes to it.
The index.php page display the data to the end-user. For this example, we are creating a User object that will simply pull a user from the database and display their information to the end-user.
Now that you have a brief idea of what each page does, I am sure you want to see just how easy it is to retrieve information from the database. Well, scroll down to the index.php section and that is all there is to it.
Pretty simple, huh? Now you can download all the code and play around with it. Hopefully, it works as easy for you as it does for me. If you have any questions, you can email me at mike@pateconsulting.com and we can chat about it.
##################
# BEGIN constants.php
##################
<?php
/**
* constants to be used throughout the entire site
*/
switch($_SERVER["SERVER_ADDR"]) {
case "127.0.0.1":
define("DB_HOST" , "localhost");
define("DB_USERNAME", "");
define("DB_PASSWORD", "");
define("DB_NAME", "");
define("DB_PORT", "");
break;
default:
define("DB_HOST", "localhost");
define("DB_USERNAME", "");
define("DB_PASSWORD", "");
define("DB_NAME", "");
define("DB_PORT", "");
}
##################
# END constants.php
##################
##################
# BEGIN functions.php
##################
<?php
/**
* __autoload
*
* autoloads each object without having to add the require_once on each page
*
* @param string $classname
*/
function __autoload($classname)
{
require_once("components/$classname.php");
}
##################
# END functions.php
##################
##################
# BEGIN iTemplate.php
##################
<?php
/**
* interface iTemplate
*
* @author Mike McRorey mike@pateconsulting.com
*
*/
interface iTemplate
{
public function insert();
public function update();
public function delete();
}
##################
# END iTemplate.php
##################
##################
# BEGIN Database.php
##################
<?php
/**
* class Database
*
* Connection class to the database. Currently setup for MySQL
*
* @author Mike McRorey mike@pateconsulting.com
*
*/
class Database
{
protected $link;
/**
* __construct
*
* default method called upon instantiation
*
* @return boolean
*/
public function __construct()
{
try
{
/**
* make the connection to the database
*/
$this->link = @mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME, DB_PORT);
/**
* check to see if the connection has been made
*/
if (!$this->link)
{
errorPage("Could not connect: " . @mysqli_connect_error());
}
return true;
}
catch(Exception $e)
{
errorPage("Caught exception: " . $e->getMessage());
}
}
/**
* __destruct()
*
* default method called upon close
*/
public function __destruct()
{
$this->link = NULL;
}
/**
* query
*
* Performs a query on the database
*
* @param string $sql
*
* @return result $result
*/
protected function query($sql)
{
try {
if (@mysqli_multi_query($this->link, $sql))
{
$result = @mysqli_store_result($this->link);
@mysqli_next_result($this->link );
return ($result) ? $result : 1;
}
else
{
errorPage("Sql Error: " . @mysqli_error($this->link));
}
}
catch(Exception $e)
{
errorPage("Caught exception: " . $e->getMessage());
}
}
/**
* free
*/
protected function free($resource)
{
while(@mysqli_more_results($this->link))
{
if(@mysqli_next_result($this->link))
{
$result = mysqli_use_result($this->link);
mysql_free_result($result);
}
}
}
/**
* convertToArray
*
* loops through a resource and converts it to a multi-dimensional array
*
* @param resource $resource
*
* @return array $data
*
protected function convertToArray($resource)
{
$data = array();
try
{
@mysqli_data_seek($resource, 0);
$rows = @mysqli_num_rows($resource);
$cols = @mysqli_num_fields($resource);
if ($rows != 0)
{
for ( $i=0; $i<$rows; $i++ )
{
$row = $this->GetResultsAsArray($resource);
for ($j=0; $j<$cols; $j++)
{
list($col_name, $col_value) = each($row);
$item[$col_name] = $col_value;
}
$data[] = $item;
}
}
return $data;
}
catch(Exception $e)
{
errorPage("Caught exception: " . $e->getMessage());
}
}
/**
* getResultsAsArray
*
* Returns an array that corresponds to the fetched row and moves the
* internal data pointer ahead
*
* @param resource $resource
*
* @return array
*/
protected function getResultsAsArray($resource)
{
try
{
return @mysqli_fetch_array($resource, MYSQLI_ASSOC);
}
catch(Exception $e)
{
errorPage("Caught exception: " . $e->getMessage());
}
}
/**
* insertId
*
* Returns the auto generated id used in the last query
*
* @return int
*/
protected function insertId()
{
try
{
return @mysqli_insert_id($this->link);
}
catch(Exception $e)
{
errorPage("Caught exception: " . $e->getMessage());
}
}
/**
* safe
*
* @param string $string
* @param bool $bln_html
*/
protected function safe(&$string, $bln_html=false)
{
if (get_magic_quotes_gpc())
{
$string = stripslashes($string);
}
if ($bln_html) {
$string = @mysqli_real_escape_string($this->link, $string);
} else {
$string = @mysqli_real_escape_string($this->link, htmlspecialchars($string));
}
return $string;
}
}
##################
# END Database.php
##################
##################
# BEGIN User.php
##################
<?php
/**
* class User
*
* @author Mike McRorey mike@pateconsulting.com
*/
class User extends Database implements iTemplate
{
private $_isLoaded;
private $_id;
private $_userName;
private $_userPass;
private $_firstName;
private $_lastName;
/**
* __construct
*/
public function __construct($id=NULL)
{
// have to call the parent
parent::__construct();
// check to see if we are tryig to load a user
if (!is_null($id)) {
$this->_id = (int)safe($id);
// load the user details
$this->_load();
}
}
/**
* id
*/
public function id($id=NULL)
{
if (!is_null($id)) {
$this->_id = (int)safe($id);
return true;
} else {
return $this->_id;
}
}
/**
* userName
*/
public function userName($userName=NULL)
{
if (!is_null($userName)) {
$this->_userName = (string)safe($userName);
return true;
} else {
return $this->_userName;
}
}
/**
* userPass
*/
public function userPass($userPass)
{
$this->_userPass = md5((string)safe($userPass));
return true;
}
/**
* firstName
*/
public function firstName($firstName=NULL)
{
if (!is_null($firstName)) {
$this->_firstName = (string)safe($firstName);
return true;
} else {
return $this->_firstName;
}
}
/**
* lastName
*/
public function lastName($lastName=NULL)
{
if (!is_null($lastName)) {
$this->_lastName = (string)safe($lastName);
return true;
} else {
return $this->_lastName;
}
}
/**
* insert
*/
public function insert() {}
/**
* update
*/
public function update() {}
/**
* delete
*/
public function delete() {}
/**
* select
*/
public function select()
{
return $this->_load();
}
/**
* _load
*/
private function _load()
{
$sql = "
SELECT *
FROM users
WHERE id = '%d';";
$sql = sprintf(
$sql,
$this->_id
);
$result = $this->query($sql);
if (!$result) {
return false;
} else {
$data = $this->getResultsAsArray($result);
$this->free($result);
$result = NULL;
if (count($data)) {
$this->_isLoaded = TRUE;
$this->_id = $data["id"];
$this->_userName = $data["user_name"];
$this->_userPass = $data["user_pass"];
$this->_firstName = $data["first_name"];
$this->_lastName = $data["last_name"];
return true;
} else {
return false;
}
}
}
/**
* isLoaded
*
* checks to see if the population of a user was valid
*/
public function isLoaded()
{
return $this->_isLoaded;
}
/**
* listAll
*/
public function listAll() {}
}
##################
# END User.php
##################
##################
# BEGIN index.php
##################
<?php
# 1 is the user_id we are using for this example
require_once("incs/constants.php");
require_once("incs/functions.php");
$u = new User(1);
$first_name = $u->firstName();
$u = NULL;
echo $first_name;
##################
# END index.php
##################
####################################
# BEGIN SQL TO CREATE user table for example
####################################
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(25) NOT NULL,
`user_pass` varchar(32) NOT NULL,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL
PRIMARY KEY (`id`),
UNIQUE KEY `user_name_user_pass` (`user_name`,`user_pass`),
KEY `user_name` (`user_name`),
KEY `user_pass` (`user_pass`)
);
####################################
# END SQL TO CREATE user table for example
####################################
