« cfcontent reset="yes" | ^ Main | echo vs niceEcho »


PHP + ODBC (specifically Access)

Let’s just face it PHP bites when it comes to ODBC. I’m just being real. With MySQL it is manageable and very usable but with ODBC it is horrible.

We have a project I’m working on which involves PHP connecting to a Microsoft Access database. I thought it was simply telling it which db file and doing normal query stuff. Well, it kinda is but in a way definitely is not. Let me list my problems.

  1. As expected, the query returns a result object which you have to use a method to get the contents. In this case you can use odbc_fetch_array(). Simple enough huh? Nope! You have to loop over the recordset and grab each column then place that into an array. Without specifying the row number you will get the first record only. This isn’t that bad but let’s go to the next one.
  2. Getting the number of records isn’t as easy as howManyRowsAreInThisRecordSet(). :-) There actually isn’t a function that tells you how many records there are. You have to loop over the entire thing, increment a var, then use the final # as the total. How lame is that? Would you believe people on php.net actually suggested querying the database twice? The first time to grab your records and the second to do a count(*) statement. That is super-lame that developers would have to go to such bounds.
  3. Linux is no piece of cake to get ODBC connected. I don’t know how it will work if you are on a shared hosting site but Bryan Mills (btw, nice lime) posted back in 2003 a way to setup Linux for Microsoft Access connectivity. You can read his post here.

With all of my complaints I still have to get the work done. So, I researched and looked for ways other people were doing it (namely looked at docs on php.net) and decided to make something easily implemented. Here is the class I built that works perfectly fine. It returns a nice array of arrays of objects ($result3[‘columnname’]).

<?
class odbcData{
 	var $connectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=";
 	var $database = "mysubfolder\\mydb.mdb";
 	var $connection = "";
 	var $username = "";
 	var $password = "";
 	
 	function odbcData(){
  		$this->connect();
  	}
 
 	function connect(){
  		$this->connectionString .=  realpath($this->database);
  		$this->connection = odbc_connect($this->connectionString, $this->username, $this->password);
  	}
 	
 	function query($sql){
  		return $this->odbcConvertToArray(odbc_exec($this->connection,$sql));
  	}
 	
 	function odbcConvertToArray($rs){
  		$result = array();
  		$len = $this->odbcRecordCount($rs);
  		echo $len;
  		for($i=0; $i<$len; $i++){
   			$result[count($result)] = odbc_fetch_array($rs, $i+1);
   		}
  		return $result;
  	}
 	
 	function odbcRecordCount($rs){
  		$count=0;
  		while($temp = odbc_fetch_into($rs, &$counter)){
   			$count++;
   		}
  		return $count;
  	}
}
?>

The result will look something like this:

Array
(
    [0] => Array
        (
            [MemberID] => 941
        )
    [1] => Array
        (
            [MemberID] => 942
        )

    [2] => Array
        (
            [MemberID] => 943
        )
}

Hopefully this will help someone get over the downfalls I ran into. There may be better solutions out there but this one is quick and useful. I don’t like adding tons of extra files to do something when I can build my solution in 1 file.

Note: DON’T FORGET TO USE \ WHEN REFERENCING LOCAL FOLDERS FOR THE DATABASE PATH.

Posted by John C. Bland II on March 9, 2006 11:14 PM |

TrackBack

TrackBack URL for this entry:
http://mt.katapultmedia.com/mt-tb.cgi/64

Post a comment