Navigator

Devb Code Snippets

Please read the notice carefully ¹⁾ before using any of the contents.

ValueDataManager and Sample Class
Accessing MySQL from PHP and Java
Tricks and Tips in PHP

Data Access in PHP

I have used this code for Value Data Access. Initially this code was donated to the Zend developers column. There are some variations from the original data access code that was presented in Zend. The reason for the changes are accomodating other classes that are through foreign key references.

 
abstract class ValueDataManager {
    public $connection;
    public $resultSet;
    public $autoCommit = 0;
    public $rowData;
    public $nextRowNumber = 0;
    public $rowCount = 0;
    public function __construct() {
        die("DataLayer: Do not create instances of DataManager! Use a subclass instead.");
    }
	public function SetDataManager() { } 
    public function CreateDataStructure() { }
    public function InsertData() { }
    public function Query() { }
    public function SeekRow() { }
    public function ReadRow() { }
    public function LoadData() { }
    public function LoadAll() { }
    public function Begin() { }
    public function Commit() { }
    public function Rollback() { }
    public function _identify() {
        return "Value::DataManager/4.2";
    }
}
 
class ValueDataManagerMySQL extends ValueDataManager {
    public function __construct($host, $user, $pass, $db = null) {
    	// Initialize
    }
    public function SetDataManager($host, $user, $pass, $db = null) {
        $this->connection = mysql_connect($host, $user, $pass);
        if ($db != null) {
            mysql_select_db($db, $this->connection);
        }
    }
    public function CreateDataStructure($datastr, $connection=null) {
    	if (!$connection)
    		$result = mysql_query($datastr, $this->connection);
    	else
    		$result = mysql_query($datastr, $connection);
    	return $result;
    }
    public function InsertData($datastr, $connection=null) {
    	if (!$connection)
    		$result = mysql_query($datastr, $this->connection);
		else
    		$result = mysql_query($datastr, $connection);
    	return $result;
    }
    public function Query($querystr, $connection=null) {
    	if (!$connection)
        	$result = mysql_query($querystr, $this->connection);
		else
        	$result = mysql_query($querystr, $connection);
        if (!$result) {
            return 0;
        } else {
            if (isset($this->dbQResult))
                mysql_free_result($this->dbQResult);
            $this->rowData = array();
            $this->resultSet = $result;
            $this->rowCount = mysql_num_rows($this->resultSet);
            if (!$this->rowCount) {
                $this->rowCount = 0;
            }
            return 1;
        }
    }
    public function SeekRow($row = 0) {
        if ((!mysql_data_seek($this->resultSet, $row)) or ($row > $this->rowCount - 1)) {
            printf("ChartOfAccounts SeekRow: Cannot seek the row %dn", $row);
            return 0;
        } else {
            return 1;
        }
    }
    public function ReadRow() {
        if ($this->rowData = mysql_fetch_array($this->resultSet)) {
            $this->nextRowNumber++;
            return 1;
        } else {
            return 0;
        }
    }
    public function Begin() {
        mysql_query("SET AUTOCOMMIT=0");
        // mysql_query("BEGIN"); // For MySQL 3.x 
        mysql_query("START TRANSACTION"); // For MySQL 4 and above
    }
    public function Commit() {
        mysql_query("COMMIT");
    }
    function Rollback() {
        mysql_query("ROLLBACK");
    }
    public function _identity() {
        return "Value::DataManagerMySQL/4.2";
    }
}
?>

Now to implement this class, for example for Chart of Accounts, the complete abstract and concrete class would be.

 
abstract class ChartOfAccountsAbstract extends MasterAccount {
	// References to other Classes
	protected $subLedger = array(); // type: SubLedger
	protected $accountingYear; // type: AccountingYear
	protected $company; // type: Company
	// Attributes through Extending Other Classes
	public $beginCr = 0.00; 
	public $beginDr = 0.00; 
	public $budgetCr = 0.00; 
	public $budgetDr = 0.00; 
	public $description = ""; 
	public $transactDr = 0.00; 
	public $transactCr = 0.00; 
	public $accountCode = ""; 
	// Relational Mapping Types
	public $connection;
	public $resultSet;
	public $autoCommit = 0;
	public $rowData;
	public $nextRowNumber = 0;
	public $rowCount = 0;
	public function __construct() {
		die("ChartOfAccountsAbstract : Do not create instances of ChartOfAccountsAbstract! Use a subclass instead.");
	}
	//
	public function SetDataManager() { } 
	public function CreateDataStructure() { }
	public function InsertData() { }
	public function Query() { }
	public function SeekRow() { }
	public function ReadRow() { }
	public function LoadData() { }
	public function LoadAll() { }
	public function Begin() { }
	public function Commit() { }
	public function Rollback() { }
	public function _identify() {
		return 'Value::ChartOfAccountsAbstract/4.2';
	}
}
// --- Class with Relational Mapping for MySQL Data Only
class ChartOfAccounts extends ChartOfAccountsAbstract {
	public function __construct() {
		$subLedger[] = new SubLedger();
		$accountingYear = new AccountingYear();
		$company = new Company();
	}
	public function SetDataManager($host, $user, $pass, $db = null) {
		$this->connection = mysql_connect($host, $user, $pass);
		if ($db != null) {
			mysql_select_db($db, $this->connection);
		}
	}
	public function CreateDataStructure($datastr, $connection=null) {
		if (!$connection)
			$result = mysql_query($datastr, $this->connection);
		else
			$result = mysql_query($datastr, $connection);
		return $result;
	}
	public function InsertData($datastr, $connection=null) {
		if (!$connection)
			$result = mysql_query($datastr, $this->connection);
		else
			$result = mysql_query($datastr, $connection);
		return $result;
	}
	public function Query($querystr, $connection=null) {
		if (!$connection)
			$result = mysql_query($querystr, $this->connection);
		else
			$result = mysql_query($querystr, $connection);
		if (!$result) {
			return 0;
		} else {
			if (isset($this->dbQResult))
				mysql_free_result($this->dbQResult);
			$this->rowData = array();
			$this->resultSet = $result;
			$this->rowCount = mysql_num_rows($this->resultSet);
			if (!$this->rowCount) {
				$this->rowCount = 0;
			}
			return 1;
		}
	}
	public function SeekRow($row = 0) {
		if ((!mysql_data_seek($this->resultSet, $row)) or ($row > $this->rowCount - 1)) {
			printf("ChartOfAccounts SeekRow: Cannot seek the row %dn", $row);
			return 0;
		} else {
			return 1;
		}
	}
	public function ReadRow() {
		if ($this->rowData = mysql_fetch_array($this->resultSet)) {
			$this->nextRowNumber++;
			return 1;
		} else {
			return 0;
		}
	}
	public function Begin() {
		mysql_query("SET AUTOCOMMIT=0");
		mysql_query("START TRANSACTION"); 
	}
	public function Commit() {
		mysql_query("COMMIT");
	}
	function Rollback() {
		mysql_query("ROLLBACK");
	}
	public function _identify() {
		return 'Value::ChartOfAccounts/4.2';
	}
}

To test the class, here's a sample test.

 
$rollback = false;
$chartofaccounts = new ChartOfAccounts();
$chartofaccounts->SetDataManager("localhost", "root", "", "test");
$chartofaccounts->CreateDataStructure('CREATE TABLE `chartofaccounts` (
		beginCr decimal(20,4), 
		beginDr decimal(20,4), 
		budgetCr decimal(20,4), 
		budgetDr decimal(20,4), 
		description varchar(128), 
		transactDr decimal(20,4), 
		transactCr decimal(20,4), 
		accountCode varchar(128), 
		chartofaccountsId integer NOT NULL PRIMARY KEY
) TYPE=INNODB;');	
$chartofaccounts->Begin();
$chartofaccounts->InsertData("INSERT INTO `chartofaccounts` VALUES (
 200.65,  200.65,  200.65,  200.65,  'gfedcba',  200.65,  200.65,  'gfedcba', 1 )");	
 
$chartofaccounts->Query("delete from chartofaccounts where `chartofaccountsId` = 2");
echo "Row Deleted <br>";
$chartofaccounts->Query("SELECT `chartofaccountsId` FROM chartofaccounts where `chartofaccountsId` between 1 and 10");
$rollback = $chartofaccounts->connection;
echo "Total Rows = ". $chartofaccounts->rowCount . "<br>";
$chartofaccounts->Rollback();
$chartofaccounts->Query("SELECT `chartofaccountsId` FROM chartofaccounts where `chartofaccountsId` between 1 and 10");
$rollback = $chartofaccounts->connection;
echo "Total Rows = ". $chartofaccounts->rowCount . "<br>";
if ($rollback) $chartofaccounts->Rollback();

¹⁾ if you do not agree to the notice and disclaimers, please do not use the links to navigate to other pages