A PHP Data Access Layer that uses Custom DataTable

In my last post about PHP Code that looks like .Net Code, I demonstrated a DataTable object that I built in PHP.

I'm really digging the potential that has bought me, so I created a DataAccessLayer object that has a method that can return query results as a DataTable.

To download the updated "library" I'm building, Click Here.

The method I built is in the class.DataAccessLayer.php file. The contents of the method are below:

///Gets the query results as a datatable
public function QueryResultsAsDataTable($query)
{
 $this->connectToDatabase();  
 
 if( substr($query, 0, 6) == "SELECT" )
 {
  $currentRow = 0;
  if(!strpos($query,"'"))
  {
   $query = mysql_real_escape_string($query);
  }

  $this->_dataSet   = mysql_query($query, $this->_connection);

  $returnDataTable = new DataTable();

  if($this->_dataSet)
  {  
 
   if (mysql_num_rows($this->_dataSet) > 0 && $currentRow == 0) 
   {

    for ($i=0; $i < mysql_num_fields($this->_dataSet); $i++) 
     $dt->AddColumn(mysql_field_name($this->_dataSet, $i));

   }    


   while($this->row = mysql_fetch_array($this->_dataSet))   
   {
    $dr = $returnDataTable->NewRow();


    for( $this->columnCount = 0; $this->columnCount < mysql_num_fields($this->_dataSet); $this->columnCount++ )
    {
     $columnName = mysql_field_name($this->_dataSet, $this->columnCount);
     $dr->AddValue($columnName, $this->row[$this->columnCount]);
    }  
    

    $returnDataTable->AddRow($dr);  
   
    $currentRow++;
   }

   
   return $returnDataTable;
  }

  throw new Exception('Error getting data:  ' .mysql_errno($this->_connection) . ': ' . mysql_error($this->_connection));   
 }
}

So, I could issue the following code to my data access layer, and I would be able to iterate through the results:

$dt = $_dataAccessObject->QueryResultsAsDataTable("SELECT firstname, lastname FROM people WHERE id=3");

$firstName = "";
$lastName = "";

for($i = 0; $i < $dt->Rows->GetCount(); $i++)
{
   $firstName = $dt->Rows->GetValue("firstname");
   $lastName  = $dt->Rows->GetValue("lastname");
   print("FirstName is " . $firstName . " - LastName is " . $lastName);
}

So there you have it, a simple data access layer that returns a (somewhat) sophisticated object in a quick, non-obscure way.