I just started learning about MVC framework implementation. Essentially I have a DB class that wraps either PDO or MySQLi connection objects.
class DB
{
private static $_instance=null;
private $_pdo, $_query, $_error=false, $_result, $_count=0, $_lastInsertId=null;
private function __construct()
{
try
{
$this->_pdo=new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD);
}
catch(PDOException $e)
{
die($e->getMessage());
}
}
public static function getInstance()
{
if(!isset(self::$_instance))
{
self::$_instance=new DB();
}
return self::$_instance;
}
public function first()
{
return (!empty($this->_result))?$this->_result[0]:[];
}
public function query($sql, $params=[])
{
$this->_error=false;
if($this->_query=$this->_pdo->prepare($sql))
{
$x=1;
if(count($params))
{
foreach($params as $param)
{
$this->_query->bindValue($x, $param);
$x++;
}
}
if($this->_query->execute())
{
$this->_result=$this->_query->fetchALL(PDO::FETCH_OBJ);
$this->_count=$this->_query->rowCount();
$this->_lastInsertId=$this->_pdo->lastInsertId();
}
else
{
$this->_error=true;
}
}
return $this;
}
protected function _read($table, $params=[])
{
$conditionString='';
$bind=[];
$order='';
$limit='';
if(isset($params['conditions']))
{
if(is_array($params['conditions']))
{
foreach($params['conditions'] as $condition)
{
$conditionString.=' '.$condition.' AND';
}
$conditionString=trim($conditionString);
$conditionString=rtrim($conditionString, ' AND');
}
else
{
$conditionString=$params['conditions'];
}
if($conditionString!='')
{
$conditionString=' WHERE '.$conditionString;
}
}
if(array_key_exists('bind', $params))
{
$bind=$params['bind'];
}
if(array_key_exists("order", $params ))
{
$order=' ORDER BY '.$params['order'];
}
if(array_key_exists("limit", $params ))
{
$limit=' LIMIT '.$params['limit'];
}
$sql="SELECT * FROM {$table} {$conditionString} {$order} {$limit}";
if($this->query($sql, $bind))
{
if(!count($this->_result))
return false;
else
return true;
}
return false;
}
public function findFirst($table, $params)
{
if($this->_read($table, $params))
{
return $this->first();
}
else
return false;
}
}
There are some other methods, but I think they are not important.
So we have the _read() meathod that builds a Select query. The _read() method gets a table name and select query clauses as its arguments. The query() method that runs the query built by the _read() method. Also findFirst() method returns the first record in the result set of a select query.
The user_sessions table is of the following structure
______ ______________________________
| id | user_id| session |user_agent|
The session value and the user_agent values are stored in a cookie. Now when I try to run
$db=new DB("some_table_name");
$db->findFirst("user_sessions", ['conditions'=>'user_agent= ? AND session= ?',
'bind'=>[Session::uagent_no_version(), Cookie::get(REMEMBER_ME_COOKIE_NAME)]]);
The result is nothing. Moreover when I tried to get the exact sql query the query() method gets, Instead of
SELECT * FROM user_sessions WHERE user_name=? AND session=?
query , the
Show Columns
query runs. The mechanism I want implement is to get the first record in a result set for a particular query. However, as a Select query gets replaced by Show Columns query, the plan does not work as expected.
I tried to check what sql query the _read() method builds. The select query is built as expected. But when the query() method is called from within the _read() method, instead of SELECT query, Show Columns from user_sessions query runs.
I want to know whey select query is replaced by show columns query
yerzhan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5