So, if you are new to PHP or perhaps just new to creating web applications that connect to a relational database (such as MySQL), the following design pattern could help you get started on the right foot.
I am gonna guess that if you have started to create a web application/CMS that is composed of multiple PHP files (ie, news.php, calendar.php, pictures.php, etc), you are feeling the pain from all the database code that seems to be repeated. This is precisely the problem that I would like to help you solve. With an abstract database class, you can create generalized functions that will be flexible for later growth and unexpected twists.
class db {
/* Members */
private $db_user;
private $db_pass;
private $db_name;
private $db_server;
private $link;
private $result_id;
/* Methods */
public function __construct($user=DB_USER, $pass=DB_PASS, $name=DB_NAME, $server=DB_SERVER){
$this->db_user = $user;
$this->db_pass = $pass;
$this->db_name = $name;
$this->db_server = $server;
$this->connect();
}
private function connect(){
$this->link = @mysql_connect($this->db_server,$this->db_user,$this->db_pass) or die("can't connect to database");
@mysql_select_db($this->db_name,$this->link) or die("can't select db (".$this->db_name.")";
} private function disconnect(){
@mysql_close($this->link);
}
public function __destruct(){
$this->disconnect();
}
This will give us a good base to work from. We have established the constructor, destructor and the methods responsible for making and breaking the connection to the database. The reason we created a new function called connect and simply just wrapping the mysql_connect() function is because we want to the class to flexible enough such that if we wanted to change the database from MySQL to Postgres we could simply replace the mysql_connect() function with the pg_Connect() function without modifying potentially hundreds or thousands of database calls. (same would be true for disconnect())
Now that we can connect, we should create some functions that will allow us to SELECT something from the database.
public function select($params){
extract($params);
$sql = 'SELECT '.$fields.' FROM '.$table;
if(!empty($where)){
$sql .= ' WHERE '.$where;
}
if(!empty($group_by)){
$sql .= ' GROUP BY '.$group_by;
}
if(!empty($order_by)){
$sql .= ' ORDER BY '.$order_by;
}
if(!empty($limit)){
$sql .= ' LIMIT '.$limit;
}
$rows = array();
$this->query($sql);
return $rows;
}
private function query($sql){
$rows = array();
$this->result_id = @mysql_query($sql);
if($this->result_id){
$rows = $this->fetch_rows();
}
return $rows;
}
private function fetch_rows(){
$rows = array();
if($this->result_id){
while($row = @mysql_fetch_object($this->result_id)){
$rows[] = $row;
}
}
return $rows;
}
As you can see we have now created functions that will create an abstraction layer on top of the MySQL functions so that we can easily replace the “guts” of those functions (if necessary) and all the lines of code that reference those functions will not have to change.
Hopefully you get the gist of how the rest would go. I have included a more fleshed out version of the class at this link (db.class.php).