I need some advice\suggestions on how to create\handle database connections for a project I'm working on. I'm creating a simple work order system for my company using PHP 5.4.3, right now there isn't any type of authentication as I'm not ready for that complexity.
So far I'm planning on the following structure:
class Db {
private static $dbh = null;
static function open_connection() {
self::$dbh = new PDO(... }
static function query($sql) {
$result = self::$dbh->... }
static function fetch_array($result){
...}
...
}
class Workorder extends Db {
protected static $table_name = ...
...
public $wonum;
...
function instantiate {
//return workorder objects
...}
fucntion findallwos {
//find all work orders...
...}
...}
I think this will work fine for pages that I need to display all of the work orders or put in new work orders. But I have a few pages that require very simple queries, for example one of the reporting pages will just have a drop down list of the 3 technicians we have working for us, if I was using a global $dbh
variable I could just do:
function create_dropdown () {
GLOBAL $dbh;
$sql = "select...";
$sth = $dbh->prepare($sql);
$sth->execute();
$arry = $sth->fetchAll(PDO::FETCH_ASSOC);
.....
But I'd rather stay away from global variables as eventually I would like to add future complexity to this site which might not work with globals. (Including multiple users with authentication etc).
My questions are:
- Should I not use the Db class as I have it designed, and instead use some sort of connection_factory class instead that passes out connections? (and not have my workorder class be an extension of anything)
- Should I just use static calls to the Db class to pull in simple queries?
db::fetch_array...
- Do you have any other suggestions? I've come across a lot of different ways to go about this, but ideally I'd like to design this so that I don't have to completely recode everything when I get to the point where I'm adding multiple users with different permissions etc.