Binding input arguments to post data doesn’t result in a very reusable interface. I would recommend passing the data in arguments, rather than relying on its existence in the post array.
This, just screams non-reuse.
public static function findUser() {
$name = parent::escape($_POST['searchname']);
$userID = parent::escape($_POST['searchUserID']);
$sql = "SELECT Name FROM users WHERE `Name` = '{$name}' && UserID = '{$userID}'";
echo "THIS WAS COMPLETED: " . parent::query($sql);
}
particularly:
$name = parent::escape($_POST['searchname']);
$userID = parent::escape($_POST['searchUserID']);
That decision makes the entire thing dependent on the post, very bad idea in terms or reuse and maintainability. Change a post field name and you need to update not only the application layer but the model layer also.
echo "THIS WAS COMPLETED: " . parent::query($sql);
The other thing is NEVER, print output in the the model, unless merely debugging. Return true, false, throw an exception to allow the outer layer to handle the situation in terms of display. Just never print anything in the model.
In terms of adapter this is the standard interface I use. This makes it possible to swap out PDO, MySQL, and MySQLi, since they all cohere to the same interface, including bound arguments.
interface MCPDB {
public function connect($strHost,$strUser,$strPwd,$strDb);
public function disconnect();
public function query($strSQL,$arrBind=array());
public function affectedRows();
public function lastInsertId();
public function escapeString($strValue);
public function isConnected();
}
Example of MySQL adpater:
<?php
$this->import('App.Core.Resource');
$this->import('App.Core.DB');
class MCPMySQL extends MCPResource implements MCPDB {
private
/*
* Connection resource identifier
*/
$_objLink;
/*
* Connects to database
*
* @param str host name
* @param str user name
* @param str user password
* @param str database name
*/
public function connect($strHost,$strUser,$strPwd,$strDb) {
$this->_objLink = mysql_connect($strHost,$strUser,$strPwd);
if(!$this->_objLink) {
$this->_objMCP->triggerError('Unable to establish database connection');
}
if(!mysql_select_db($strDb,$this->_objLink)) {
$this->_objMCP->triggerError('Unable to select database');
}
}
/*
* Disconnects from database
*/
public function disconnect() {
if(!mysql_close($this->_objLink)) {
$this->_objMCP->triggerError('Unable to close database connection');
}
}
/*
* Queries database returning result set as associative array
*
* @param str query
* @param array bound arguments (necessary for consistent interface for PDO and MySQLi adapters)
* @return array result set associative array
*/
public function query($strSQL,$arrBind=array()) {
/*
* Fake/replicate binding
*/
if(!empty($arrBind)) {
$strSQL = $this->_rewriteQuery($strSQL,$arrBind);
}
$objResult = mysql_query($strSQL,$this->_objLink);
if(!$objResult) {
$this->_objMCP->triggerError('SQL Query invalid');
}
$arrRows = array();
if(strpos($strSQL,'SELECT') === 0 || strpos($strSQL,'DESCRIBE') === 0 || strpos($strSQL,'SHOW') === 0) {
while($arrRow = mysql_fetch_assoc($objResult)) {
$arrRows[] = $arrRow;
}
} else if(strpos($strSQL,'INSERT') === 0) {
return $this->lastInsertId();
} else if(strpos($strSQL,'UPDATE') === 0) {
return $this->affectedRows();
}
return $arrRows;
}
/*
* Number of rows affected by the last query
*
* @return int number of affected rows
*/
public function affectedRows() {
$affectedRows = mysql_affected_rows($this->_objLink);
if($affectedRows < 0) {
$this->_objMCP->triggerError('Last query failed so number of affected rows could not be determined');
}
return $affectedRows;
}
/*
* Insert id of last query
*
* @return mix last insert id
*/
public function lastInsertId() {
return mysql_insert_id($this->_objLink);
}
/*
* Escapes string for proper input into query statement
*
* @param str string to escape
* @return escaped string value
*/
public function escapeString($strValue) {
$strEscapedValue = mysql_real_escape_string($strValue,$this->_objLink);
if($strEscapedValue === false) {
$this->_objMCP->triggerError('String escape failed');
}
return $strEscapedValue;
}
/*
* Has database connection been established?
* @return bool
*/
public function isConnected() {
return $this->_objLink === null?false:true;
}
/*
* Internal method used to rewrite supplied queries with
* question mark or named placeholders. Since, default MySQL
* adapter doesn't support bound arguments the the behvior
* must be "faked" to keep a consistent interface across
* adapters that do actually supply this capability such as; PDO
* and MySQLi.
*
* @param str SQL w/ placeholders
* @param array bindings
* @return str rewritten SQL
*/
protected function _rewriteQuery($strSQL,$arrBind) {
/*
* Determines whether matching will be name or ? placeholder based
*/
$named = 'false';
/*
* Pttern to match against; defaults to ? placeholder
*/
$pattern = '\\?';
/*
* Name based placeholder named and pattern change
*/
if(strpos($strSQL,'?') === false) {
$pattern = '('.implode('|',array_keys($arrBind)).')';
$named = 'true';
}
/*
* begin building replacement function
*/
$func = 'static $i=0;$values = array();$named = '.$named.';';
foreach($arrBind as $mixIndex=>$mixValue) {
/*
* NULL, integer and string mutations
*/
if($mixValue === null) {
$mixValue = '\\'NULL\\'';
} else if(is_int($mixValue)) {
$mixValue = $this->escapeString($mixValue);
} else {
$mixValue = "\\"'".$this->escapeString($mixValue)."'\\"";
}
/*
* Push onto replacement function values array
*/
$func.= '$values[\\''.$mixIndex.'\\'] = '.$mixValue.';';
};
$func.= 'return $named?$values[$matches[0]]:$values[$i++];';
/*
* End building replacement function; rebuild SQL.
*/
return preg_replace_callback("/$pattern/",create_function('$matches',$func),$strSQL);
}
}
?>
The private rewrite function allows the standard adapter to “support” a bound argument interface.
Though my goal has been to support interchangable adapters, perhaps different from your own.
Here is a simple example of one of my models. No magic, or anything just straight forward, reusable, isolated business logic methods to accomplish common tasks.
/*
* Site data access layer
*/
class MCPDAOSite extends MCPDAO {
/*
* List all sites
*
* @param str select fields
* @param str where clause
* @param order by clause
* @param limit clause
* @return array users
*/
public function listAll($strSelect='s.*',$strFilter=null,$strSort=null,$strLimit=null) {
/*
* Build SQL
*/
$strSQL = sprintf(
'SELECT
%s %s
FROM
MCP_SITES s
%s
%s
%s'
,$strLimit === null?'':'SQL_CALC_FOUND_ROWS'
,$strSelect
,$strFilter === null?'':"WHERE $strFilter"
,$strSort === null?'':"ORDER BY $strSort"
,$strLimit === null?'':"LIMIT $strLimit"
);
$arrSites = $this->_objMCP->query($strSQL);
if($strLimit === null) {
return $arrSites;
}
return array(
$arrSites
,array_pop(array_pop($this->_objMCP->query('SELECT FOUND_ROWS()')))
);
}
/*
* Fetch site data by sites id
*
* @param int site id
* @param str select fields
* @return array site data
*/
public function fetchById($intId,$strSelect='*') {
$strSQL = sprintf(
'SELECT %s FROM MCP_SITES WHERE sites_id = %s'
,$strSelect
,$this->_objMCP->escapeString($intId)
);
return array_pop($this->_objMCP->query($strSQL));
}
}
?>
