Massless' PHP Database Abstraction Class

Examples:

	// Microsoft Access Database
		$conn=new DBConn();
		$conn->setDatabasePath("c:\\massless\\cgi-bin\\panda.mdb");
		$conn->setPassword("some_cool_password");
		$conn->getAccessConn();
	
	// or...	
	
	// MySQL Database 
		$conn=new DBConn();
		$conn->setDatabasePath("local_server_name");
		$conn->setDatabaseName("database_name");
		$conn->setUsername("some_cool_username");
		$conn->setPassword("some_cool_password");
		$conn->getMySqlConn(); 

	// and then... Make a simple SELECT query
		$query = "SELECT * FROM ActorsInCormanFilms";
		$conn->executeQuery($query);
		$results = $conn->getQueryResults();
		while($row = $results->getRow()) {
			echo $row->getField("Name")."<br />";
			$results->nextRow();
		}

Source code:

1<?
2
3$DB_TYPE_ID_MYSQL = 1;
4$DB_TYPE_ID_ACCESS = 2;
5$DB_TYPE_ID_ORACLE = 3;  
6
7/**
8 * DBConn
9 *
10 * A basic database abstraction class that supports
11 * MySQL, Oracle, and MS Access.
12 *
13 *@author Chris Wetherell
14 *@version 1.0
15 *@copyright none
16 */
17class DBConn {
18
19  var $dbuser,
20      $dbpath,
21      $dbname,
22      $dbpassword,
23      $dbtype, 
24      $DBConn,
25      $dbresults;
26    
27  function DBConn() {}
28  function setDatabasePath($path) {$this->dbpath=$path;}
29  function setUsername($dbuser) {$this->dbuser=$dbuser;}
30  function setPassword($dbpassword) {$this->dbpassword=$dbpassword;}
31  function setDatabaseName($dbname) {$this->dbname=$dbname;}
32   
33  function getAccessConn() {
34    
35    $dbstring="".
36      "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:".
37      "Database Password=".$this->dbpassword.";".
38      "Data Source=".$this->dbpath; 
39  
40    $conn = new COM("ADODB.Connection") or die("Cannot start ADO"); 
41    $conn->Open($dbstring);  
42    
43    if (!$conn) {
44      return false;
45    }
46    $this->dbtype = $GLOBALS["DB_TYPE_ID_ACCESS"];
47    $this->dbresults = new Results($this->dbtype);
48    $this->DBConn = $conn;
49  } 
50  
51  function getMySqlConn() {
52    $conn=mysql_connect ($this->dbpath, $this->dbuser, $this->dbpassword); 
53    if (!$conn) { 
54      echo mysql_error(); exit; 
55    } else { 
56      $db_select = mysql_select_db($this->dbname); 
57      if (!$db_select) { 
58        echo mysql_error(); exit; 
59      } else { } 
60    }
61    $this->dbtype = $GLOBALS["DB_TYPE_ID_MYSQL"];
62    $this->dbresults = new Results($this->dbtype);
63    $this->DBConn = $conn;
64  }
65  
66  function getOracleConn() {
67    $conn = ocilogon($this->dbuser,$this->dbpassword,$this->dbname);
68    $this->dbtype = $GLOBALS["DB_TYPE_ID_ORACLE"];
69    $this->dbresults = new Results($this->dbtype);
70    $this->DBConn = $conn;
71  }
72  
73  
74
75  function getLastInsertedId() {
76    if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
77    {
78      return mysql_insert_id();
79    }
80    if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
81    {
82      $conn = $this->DBConn;
83      $query = "SELECT @@IDENTITY as lastId ;";
84      $conn->executeQuery($query);
85      $results = $conn->getQueryResults();
86      $row = $results->getRow();
87      return $row->getField("lastId");
88    }
89    if($this->dbtype == $GLOBALS["DB_TYPE_ID_ORACLE"]) 
90    {
91      // TODO: Add support for Oracle DBs
92    }
93  }
94  
95  function executeQuery($query) {
96     if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
97     {
98      $res = $this->executeMySqlQuery($query);
99      $this->dbresults = new Results($this->dbtype); 
100      $this->dbresults->setResults($res);
101     }
102     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
103     {
104       $this->dbresults->setResults($this->executeAccessQuery($query));
105     }
106     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ORACLE"]) 
107     {
108       $this->dbresults->setResults($this->executeOracleQuery($query));
109     }
110  }
111  function executeAccessQuery($query) {
112    return $this->DBConn->Execute($query);  
113  }
114  function executeMySqlQuery($query) {
115    $res = mysql_query($query) or die( "Unable to complete task. Reason: " .mysql_error() );
116    return $res;
117  }
118  function executeOracleQuery($query) {
119    $stmt = ociparse($this->DBConn,$query);
120    ociexecute($stmt,OCI_DEFAULT);
121    return $stmt;
122  }
123  function getQueryResults() {
124    return $this->dbresults;
125  }
126}
127
128/**
129 * Results
130 *
131 * Supports DBConn(), a basic database abstraction class. 
132 * It manages result elements such as fields, values, and iteration.
133 *
134 *@author Chris Wetherell
135 *@version 1.0
136 *@copyright none
137 */  
138class Results {
139  var $results;
140  var $dbtype;
141  var $row;
142  
143  function Results($dbtype) {
144    $this->dbtype = $dbtype; 
145  }
146  
147  function setResults($resultsFromQuery) {
148    $this->results = $resultsFromQuery; 
149  }
150  
151  function getResults() {
152    return $this->results;
153  }
154  
155  function getRowCount() {
156     if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
157     {
158       return mysql_num_rows($this->results);
159     }
160     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
161     {
162     // TODO: Support row counting for MS ACCESS
163     }
164     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ORACLE"]) 
165     {
166     // TODO: Support row counting for Oracle DBs
167     }
168  }
169  
170  function nextRow() {
171     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
172     {
173       $this->results->MoveNext();
174     }
175  }
176  function getRow() {
177     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
178     {
179       if ($this->results->EOF) { return false; } 
180       $this->row = $this->results;
181      return $this;
182     }
183     if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
184     {
185       $this->row = mysql_fetch_array($this->results);
186      if ($this->row==false) { return null;}
187      return $this;
188     }
189     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ORACLE"]) 
190     {
191       $this->row = ocifetch($this->results);
192      if ($this->row==false) { return null;}
193      return $this;
194     }
195  }
196  
197  function getFieldCount() {
198     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
199     {
200      $field = $this->row->Fields(); 
201       return $field->count();
202     }
203     if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
204     {
205       return sizeof($this->row)/2;
206     }
207     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ORACLE"]) 
208     {
209       return ocinumcols($this->getResults());
210     }
211  }
212  
213  function getFieldNameFromIndex($strIndex) {
214     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
215     {
216      $field = $this->row->Fields($strIndex); 
217       return $field->name;
218     }
219     if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
220     {
221      return mysql_field_name($this->results,$strIndex);
222     }
223     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ORACLE"]) 
224     {
225      return ocicolumnname($this->results,$strIndex+1);
226     }
227  }
228  
229  function getDateField($strFormat,$strName) {
230     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
231     {
232      $field = $this->row->Fields($strName); 
233      $date = $field->value; 
234      if ($date>0) {      
235        $date = date($strFormat,$date);
236      }
237       return $date;
238     }
239     if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
240     {
241       $date = $this->row[$strName];
242      if (gettype($date)=="string") {
243        $date_time_string =  $date;
244        
245        // Split the string into 3 parts  - date, time and AM/PM
246        $dt_elements =  explode(" " ,$date_time_string);
247        
248        // Split the date up
249        $date_elements =  explode("-" ,$dt_elements[0]);
250        
251        // Split the time up
252        $time_elements =  explode(":" ,$dt_elements[1]);
253        
254        // If we have a PM then we can  add 12 hours to the hour to get into 24 hour time.
255        if (isset($dt_elements[2]) && strtoupper($dt_elements[2])=="PM") { 
256            $time_elements[0]+=12;
257        }
258        
259        // output the result
260        $timestamp = mktime(
261              $time_elements[0], 
262              $time_elements[1], 
263              $time_elements[2], 
264              $date_elements[1], 
265              $date_elements[2], 
266              $date_elements[0]);
267        if ($timestamp>0) {      
268          $date = date($strFormat,$timestamp);
269        } else { 
270          return ""; 
271        }
272      }
273       return $date;
274     }
275  }
276  
277  function getField($strName) {
278     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ACCESS"]) 
279     {
280      $field = $this->row->Fields($strName); 
281       return $field->value;
282     }
283     if($this->dbtype == $GLOBALS["DB_TYPE_ID_MYSQL"]) 
284     {
285       return $this->row[$strName];
286     }
287     if($this->dbtype == $GLOBALS["DB_TYPE_ID_ORACLE"]) 
288     {
289      if (gettype($strName)=="integer") $strName++;
290       return ociresult($this->results,$strName);
291     }
292     
293  }
294
295}
296
297?>