Section 2: The database class and the database interface
Right. Alot of people use database to store things. Not only is it faster than if you would store it with.. say a .txt file, but it's also alot safer, but the main
thing is that it's EASY to manipulate the data. This means that you could write neat admin panels to control your site layout, templates and lots and lots more!!
But in order to get a nice way of handling the database, you need a proper database class, that will provide a set of functions for you to use inside your classes.
This database class, will actually consist of 3 files.
iDB.php - This is our interface for the class.
DBmysql.php - This is the actual class for the database, in this case a mysql-database which I called mysql.php(convinient xD?) with the prefix of DB(DBmysql).
DB.php - This is our.. DB factory, which makes it easier to include the database file.
So let's start by taking a look at our interface!
An interface is merely a blueprint on how the class should look, what functions it have to contain. This is neccessary, because when you guys figure out
that you don't want to use a mysql database, but an oracle database, and you write your own db class(DBoracle.php), you will then see if your class follows the blueprint.
The interface is define with
PHP Code:
interface iDB {
}
Pretty simple.
So what functions should the database have?
Well, first of all, we need a function that will set the values of the hostname, username, userpass and dbname. We would also need a function that connects.. etc etc.
I'm sure you can figure out by yourself all the functions that a database class should have.
This is my interface anyhow:
PHP Code:
/**
||||||||||||||||||||||||||||||||||||||||||
|||| @author Tanax
|||| @copyright 2008
||||||||||||||||||||||||||||||||||||||||||
**/
interface iDB {
public function setHandler($host, $user, $pass, $data);
public function connect();
public function select();
public function disconnect();
public function query($query);
public function fetch($sql);
public function setTable($field, $value);
public function setCol($field, $value);
}
2 very important functions are the setTable and setCol. I will explain later in the tutorial what they do, just keep them there!
So the next file, is ofcourse the database class itself.
As I mentioned before, this is just the mysql since it's most used.
PHP Code:
class DBmysql implements iDB {
private $host;
private $user;
private $pass;
private $data;
public $table = array();
public $col = array();
public function setHandler($host, $user, $pass, $data) {
}
public function connect() {
}
public function select() {
}
public function disconnect() {
}
public function query($query) {
}
public function fetch($sql) {
}
public function secure($string) {
}
public function setTable($field, $value) {
}
public function setCol($field, $value) {
}
}
As you see, we IMPLEMENT the interface. So if you guys decide to create another dbtype class, you have to implement the interface.
And ofcourse, we have to have all the functions that we defined in the interface.
PHP Code:
public function setHandler($host, $user, $pass, $data) {
$this->host = $host;
$this->user = $user;
$this->pass = $pass;
$this->data = $data;
return $this;
}
I think this is pretty self-explenatory.
The end:
..makes it possible to CHAIN the functions later when we call them and that just looks so awesome.
PHP Code:
public function connect() {
@mysql_connect($this->host, $this->user, $this->pass) or die("Could not connect to the database.<br /> " .mysql_error());
return $this;
}
I think this is pretty straight forward aswell. We return $this here aswell.
PHP Code:
public function select() {
@mysql_select_db($this->data) or die("Could not select database.");
return $this;
}
I'm not gonna say anything..
PHP Code:
public function disconnect() {
@mysql_close() or die("Could not close database connection. Perhaps because the connection isn't opened.");
}
PHP Code:
public function query($query) {
$result = mysql_query($query);
return $result;
}
Here's some new stuff, but still really basic. If you're even reading this guide, about classes and interfaces, you should definitely know how to make a mysql query!!
PHP Code:
public function fetch($sql) {
$array = mysql_fetch_array($sql, MYSQL_ASSOC);
return $array;
}
... Fetches an array
PHP Code:
public function secure($string) {
$secure = mysql_real_escape_string($string);
return $secure;
}
Secures a string from possible mysql injections.
PHP Code:
public function setTable($field, $value) {
$this->table[$field] = $value;
}
public function setCol($field, $value) {
$this->col[$field] = $value;
}
These are our 2 magic functions
The explenation comes later..
The whole class:
PHP Code:
/**
||||||||||||||||||||||||||||||||||||||||||
|||| @author Tanax
|||| @copyright 2008
||||||||||||||||||||||||||||||||||||||||||
**/
class DBmysql implements iDB {
private $host;
private $user;
private $pass;
private $data;
public $table = array();
public $col = array();
public function setHandler($host, $user, $pass, $data) {
$this->host = $host;
$this->user = $user;
$this->pass = $pass;
$this->data = $data;
return $this;
}
public function connect() {
@mysql_connect($this->host, $this->user, $this->pass) or die("Could not connect to the database.<br /> " .mysql_error());
return $this;
}
public function select() {
@mysql_select_db($this->data) or die("Could not select database.");
return $this;
}
public function disconnect() {
@mysql_close() or die("Could not close database connection. Perhaps because the connect isn't opened.");
}
public function query($query) {
$result = mysql_query($query);
return $result;
}
public function fetch($sql) {
$array = mysql_fetch_array($sql);
return $array;
}
public function secure($string) {
$escapedstring = mysql_real_escape_string($string);
$secure = stripslashes($escapedstring);
return $secure;
}
public function setTable($field, $value) {
$this->table[$field] = $value;
}
public function setCol($field, $value) {
$this->col[$field] = $value;
}
}
Did you notice btw, that the table variable, and the col variable are public eventhough I said in my other guide that you should never have public variables??
This makes it really sweet for you when you write your plugins later So trust me on this one :P
Section 4: The config and classes files
So the configuration file will ofcourse contain alot more when you have your plugins written, but right now we'll just include the basic stuff.
This is my configuration file:
PHP Code:
/**
||||||||||||||||||||||||||||||||||||||||||
|||| @author Tanax
|||| @copyright 2008
||||||||||||||||||||||||||||||||||||||||||
**/
$tanaxia['config']['database']['host'] = 'localhost'; // Database host
$tanaxia['config']['database']['user'] = 'root'; // Database user
$tanaxia['config']['database']['pass'] = ''; // Database pass
$tanaxia['config']['database']['data'] = 'slutuppgift'; // Database name
$tanaxia['config']['database']['type'] = 'mysql'; // Database type
session_start();
include('classes.php');
Yeh, erh.. I got kinda long variables+arrays there, but that's just so I could easially see what it is.
It's the $tanaxia variable(in this case for me it means that it's belonging to the core), and it's the ['config'] array, which means for me that it's
a setting for the core. The next array ['database'], means that it's a database setting, and then the next is defining what setting.
I then start the session, and include classes.php
Onto the classes file; this is what makes or breaks the script. Here we start all the classes, AND we use the setCol and setTable functions of the database class.
This is the classes file:
PHP Code:
/**
||||||||||||||||||||||||||||||||||||||||||
|||| @author Tanax
|||| @copyright 2008
||||||||||||||||||||||||||||||||||||||||||
**/
include('classes/DB.php');
$tanaxia['database'] = DB::getInstance($tanaxia['config']['database']['type']);
$tanaxia['database']->setHandler(
$tanaxia['config']['database']['host'],
$tanaxia['config']['database']['user'],
$tanaxia['config']['database']['pass'],
$tanaxia['config']['database']['data']
)
->connect()
->select();
//$tanaxia['database']->setTable('news', 'news');
//$tanaxia['database']->setCol('news_id', 'news_id');
//$tanaxia['database']->setCol('news_publish', 'news_publish');
So, we include our DB factory.
Then we create the database, based on the type settings in our config file.
After that we set the handler with all the data provided from the config file, and then directly we chain the next function(remember the chain I talked about?) and connect to
the host, and then select the database.
That's really everything we've done now.
The things that are commented describes how to use the setTable.
The good things with that, is that later when you use the db object in the plugins(for example news plugin), you want to insert values into different columns in the database.
An example would be this:
PHP Code:
class news {
private $db;
private $newsid;
public function __construct($db) {
$this->db = $db;
}
public function getNewsbyID($id) {
if(!is_numeric($id)) {
return false;
}
else {
$sql = sprintf(" SELECT
*
FROM
`%s`
WHERE
`%s` = '%s'
AND
`%s` != 0
LIMIT 1",
$this->db->table['news'],
$this->db->col['news_id'],
$id,
$this->db->col['news_publish']);
$query = $this->db->query($sql);
if(!$query) {
return false;
}
else {
unset($this->newsid);
$this->newsid = $this->db->fetch($query);
return $this->newsid;
}
}
}
}
As you see, we call the table news. But what if you decide to rename the table in your database and you've hardprinted `news` into the query?
Then you need to enter your class and change EVERYTHING to match the new name. And that kinda stinks.
So let's say you change the news table name to "articles".
Then you just enter your classes.php file, and change this line:
PHP Code:
$tanaxia['database']->setTable('news', 'news');
to this:
PHP Code:
$tanaxia['database']->setTable('news', 'articles');
Now, even if we in the class call the table "news", it will assosciate with "articles", so it's really easy and manageable.
NOTE: Both config.php and classes.php goes into your includes folder!
So the structure we have, looks like this right now: