My struggles in understanding and learning about Object Oriented design, and the tools and knowledge I've taken from them.

Tuesday, February 8, 2011

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.
Post a Comment

Followers

Search This Blog

Powered by Blogger.