Bước 11: Giải thích thêm phương thức setLimit, fetchRow, fetchAll, execute ... trong class Mysql
<?php
require_once 'database.php';
class Mysql {
var $config = array();
var $tableParameters = array(
'charset' => array(
'value' => 'DEFAULT CHARSET',
'quote' => false,
'join' => '=',
'column' => 'charset'
),
'collate' => array(
'value' => 'COLLATE',
'quote' => false,
'join' => '=',
'column' => 'Collation'
),
'engine' => array(
'value' => 'ENGINE',
'quote' => false,
'join' => '=',
'column' => 'Engine'
)
);
var $keywords = array(
'>=',
'<=',
'>',
'<',
'IN',
'NOT',
'IS',
'LIKE',
'!=',
'<>'
);
var $columns = array(
'primary_key' => array(
'name' => 'int(11) DEFAULT NULL auto_increment'
),
'string' => array(
'name' => 'varchar',
'limit' => '255'
),
'text' => array(
'name' => 'text'
),
'integer' => array(
'name' => 'int',
'limit' => '11',
'formatter' => 'intval'
),
'float' => array(
'name' => 'float',
'formatter' => 'floatval'
),
'datetime' => array(
'name' => 'datetime',
'format' => 'Y-m-d H:i:s',
'formatter' => 'date'
),
'timestamp' => array(
'name' => 'timestamp',
'format' => 'Y-m-d H:i:s',
'formatter' => 'date'
),
'time' => array(
'name' => 'time',
'format' => 'H:i:s',
'formatter' => 'date'
),
'date' => array(
'name' => 'date',
'format' => 'Y-m-d',
'formatter' => 'date'
),
'binary' => array(
'name' => 'blob'
),
'boolean' => array(
'name' => 'tinyint',
'limit' => '1'
)
);
static $instance = null;
public function __construct($config = array()) {
$this->config = $config;
return $this->connect();
}
public static function getInstance() {
$config = array(
'host' => DATABASE_HOST,
'login' => DATABASE_USERNAME,
'password' => DATABASE_PASSWORD,
'port' => DATABASE_PORT,
'database' => DATABASE_NAME,
'encoding' => 'utf8'
);
if(null === Mysql::$instance) {
Mysql::$instance = new Mysql($config);
}
return Mysql::$instance;
}
function connect() {
$config = $this->config;
$this->connected = false;
$this->connection = mysqli_connect($config['host'], $config['login'], $config['password'], $config['database']);
if ($this->connection !== false) {
$this->connected = true;
}
if (!empty($config['encoding'])) {
$this->setEncoding($config['encoding']);
}
return $this->connected;
}
function setEncoding($enc) {
return $this->_execute('SET NAMES ' . $enc) != false;
}
function _execute($sql) {
if (preg_match('/^\s*call/i', $sql)) {
return $this->_executeProcedure($sql);
} else {
return mysqli_query($this->connection, $sql);
}
}
function _executeProcedure($sql) {
$answer = mysqli_multi_query($this->connection, $sql);
$firstResult = mysqli_store_result($this->connection);
if (mysqli_more_results($this->connection)) {
while ($lastResult = mysqli_next_result($this->connection));
}
return $firstResult;
}
function select($myTable, $options = array(), $isCount = false) {
// $options is
// array (
// 'conditions' =>
// array (
// 'email' => 'admin@gmail.com',
// 'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
// ),
// )
// Get options by parameters
$myFields = isset($options['fields']) ? $options['fields'] : '*';
$conditions = isset($options['conditions']) ? $options['conditions'] : null;
$orders = isset($options['orders']) ? $options['orders'] : null;
$groups = isset($options['groups']) ? $options['groups'] : null;
$mJoins = isset($options['joins']) ? $options['joins'] : null;
$mlimit = isset($options['limit']) ? $options['limit'] : false;
$moffset = isset($options['offset']) ? $options['offset'] : false;
try {
$returnArr = array();
$table = $myTable;
// $table is user
$alias = $joins = $order = $group = $limit = "";
$fields = "";
if (is_array($myFields)) {
$fields = implode(', ', $myFields);
} else {
$fields = $myFields;
}
// $fields is *
if (isset($mJoins) && is_array($mJoins)) {
foreach ($mJoins as $jTable => $join) {
if (empty($jTable) || empty($join['join_key']) || empty($join['main_key']) || !isset($join['join_fields']) || !isset($join['join_fields'][1]))
continue;
$fields .= ", " . $jTable . "." . $join['join_fields'][0] . ", " . $jTable . "." . $join['join_fields'][1];
}
}
$conditions = $this->buildConditions($conditions);
// $conditions is " WHERE email = 'admin@gmail.com' AND password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
$order = $this->buildOrders($orders);
// $order is NULL
$group = $this->buildGroups($groups);
// $group is NULL
$joins = $this->buildJoins($myTable, $mJoins);
// $myTable is 'user'
// $mJoins is NULL
// $joins is NUll
$tmpTable = explode('_', $table);
// array (
// 0 => 'user',
// )
$alias = array();
foreach ($tmpTable as $tmp) {
$alias[] = ucfirst($tmp);
}
$alias = implode($alias);
// $alias is User
$query = compact('table', 'alias', 'joins', 'fields', 'conditions', 'joins', 'group', 'order', 'limit');
$sql = $this->renderStatement('select', $query);
// $sql is " SELECT * FROM user User WHERE email = 'admin@gmail.com' AND password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
$sql = $this->setLimit($sql, $mlimit, $moffset);
// $sql is " SELECT * FROM user User WHERE email = 'admin@gmail.com' AND password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
if ($isCount)
$returnArr = $this->fetchRow($sql);
else
$returnArr = $this->fetchAll($sql);
// $returnArr is array (
// 0 =>
// array (
// 'User' =>
// array (
// 'id' => '1',
// 'email' => 'admin@gmail.com',
// 'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
// 'fullname' => 'Admin',
// 'address' => 'Ha Nam Viet Nam',
// 'is_admin' => '1',
// 'created' => '2020-04-02 18:59:20',
// 'modified' => '2020-04-02 18:59:20',
// ),
// ),
// )
}
catch (Exception $ex) {
var_dump($ex);
}
return $returnArr;
}
public function renderStatement($type, $data) {
extract($data);
$aliases = null;
switch (strtolower($type)) {
case 'select':
// SELECT u.`email`, m.title FROM `user` as u JOIN movie_category as m WHERE u.id =1 LIMIT 1
return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";
break;
}
}
protected function buildConditions($condition) {
// $condition is
// array (
// 'email' => 'admin@gmail.com',
// 'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
// )
if (empty($condition)) return;
$sql = '';
$index = 0;
$conditionArray = array();
foreach ($condition as $field => $sub) {
$math = '';
$subVal = explode(' ', $sub);
// $subVal is
// array (
// 0 => 'admin@gmail.com',
// )
if (!in_array($subVal[0], $this->keywords)) {
$math = '=';
}
$conditionArray[] = " " . $field . " " . $math . " '" . $sub . "'";
// array (
// 0 => "email = 'admin@gmail.com'",
// 1 => "password = '7c4a8d09ca3762af61e59520943dc26494f8941b'",
// )
}
return " WHERE " . implode(' AND ', $conditionArray);
// "WHERE email = 'admin@gmail.com' AND password = '7c4a8d09ca3762af61e59520943dc26494f8941b'"
}
protected function buildOrders($orders) {
$sql = '';
if (isset($orders)) {
if (!is_array(!$orders) && $orders != "") {
$ordersCache = preg_replace('/,\s+/', ',', $orders);
if (!is_array($ordersCache))
$ordersCache = explode(",", $ordersCache);
$orders = array();
foreach ($ordersCache as $item) {
$itemArr = explode(" ", $item);
if (isset($itemArr[1])) {
$orders[$itemArr[0]] = $itemArr[1];
} else {
$orders[$itemArr[0]] = 'ASC';
}
}
}
if (count($orders)) {
$str = "";
foreach ($orders as $k => $v) {
$str .= ($k . " " . $v . ", ");
}
if ($str != "")
$str = substr($str, 0, (strlen($str) - strlen($str) - 2));
$sql = "ORDER BY " . $str;
}
}
return $sql;
}
protected function buildGroups($groups) {
$sql = '';
if (isset($groups)) {
if (!is_array(!$groups) && $groups != "") {
$groups = preg_replace('/,\s+/', ',', $groups);
$groups = explode(",", $groups);
}
if (count($groups)) {
$str = "";
foreach ($groups as $v) {
$str .= ($v . ", ");
}
if ($str != "")
$str = substr($str, 0, (strlen($str) - strlen($str) - 2));
$sql = "GROUP BY " . $str;
}
}
return $sql;
}
protected function buildJoins($mainTable, $joins) {
// $mainTable is 'user'
// $joins is NULL
$sql = '';
if ($mainTable != "" && !empty($joins)) {
foreach ($joins as $k => $v) {
$sql .= " " . (isset($v['type']) ? $v['type'] : "") . " JOIN " . $k . " ON " . $mainTable . "." . $v['main_key'] . " = " . $k . "." . $v['join_key'] . " ";
}
}
return $sql;
}
public function setLimit($sql, $limit = false, $offset = false) {
if ($limit) {
$rt = '';
if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
$rt = ' LIMIT';
}
if ($offset) {
$rt .= ' ' . $offset . ',';
}
$rt .= ' ' . $limit;
$sql = $sql . $rt;
}
return $sql;
}
function fetchRow($sql = null) {
if (!empty($sql) && is_string($sql) && strlen($sql) > 5) {
if (!$this->execute($sql)) {
return null;
}
}
if (is_resource($this->_result) || is_object($this->_result)) {
$this->resultSet($this->_result);
$resultRow = $this->fetchResult();
return $resultRow;
} else {
return null;
}
}
function fetchAll($sql) {
if ($this->execute($sql)) {
$out = array();
while ($item = $this->fetchRow()) {
$out[] = $item;
}
return $out;
} else {
return false;
}
}
function execute($sql) {
if (!function_exists('getMicrotime')) {
function getMicrotime()
{
list($usec, $sec) = explode(" ", microtime());
return (( float ) $usec + ( float ) $sec);
}
}
$t = getMicrotime();
$this->_result = $this->_execute($sql);
$this->affected = $this->lastAffected();
$this->took = round((getMicrotime() - $t) * 1000, 0);
$this->error = $this->lastError();
$this->numRows = $this->lastNumRows($this->_result);
return $this->_result;
}
function lastAffected($source = null) {
if ($this->_result) {
return mysqli_affected_rows($this->connection);
}
return null;
}
function lastError() {
if (mysqli_errno($this->connection)) {
return mysqli_errno($this->connection) . ': ' . mysqli_error($this->connection);
}
return null;
}
function lastNumRows($source = null) {
if ($this->_result and is_object($this->_result)) {
return @mysqli_num_rows($this->_result);
}
return null;
}
function resultSet(&$results) {
$this->results =& $results;
$this->map = array();
$num_fields = mysqli_num_fields($results);
$index = 0;
$j = 0;
while ($j < $num_fields) {
$column = mysqli_fetch_field_direct($results, $j);
if (!empty($column->table)) {
$this->map[$index++] = array(
$column->table,
$column->name
);
} else {
$this->map[$index++] = array(
0,
$column->name
);
}
$j++;
}
}
function fetchResult() {
if ($row = mysqli_fetch_row($this->results)) {
$resultRow = array();
$i = 0;
foreach ($row as $index => $field) {
@list($table, $column) = $this->map[$index];
$resultRow[$table][$column] = $row[$index];
$i++;
}
return $resultRow;
} else {
return false;
}
}
}
?><?php
require_once "AppModel.php";
require_once "Helper.php";
require_once "Session.php";
class User extends AppModel {
protected $table = 'user';
protected $alias = 'User';
private $session = null;
protected $rules = array(
"email" => array(
"form" => array(
"type" => "text"
),
"notEmpty" => array(
"rule" => "notEmpty",
"message" => MSG_ERR_NOTEMPTY
),
"isEmail" => array(
"rule" => "email",
"message" => MSG_ERR_EMAIL
)
),
"password" => array(
"form" => array(
"type" => "password"
),
"notEmpty" => array(
"rule" => "notEmpty",
"message" => MSG_ERR_NOTEMPTY
)
),
"fullname" => array(
"form" => array(
"type" => "text"
),
"notEmpty" => array(
"rule" => "notEmpty",
"message" => MSG_ERR_NOTEMPTY
)
),
"address" => array(
"form" => array(
"type" => "textarea"
),
"notEmpty" => array(
"rule" => "notEmpty",
"message" => MSG_ERR_NOTEMPTY
)
)
);
public function __construct() {
parent::__construct();
$this->session = new Session();
}
public function login($data) {
// array (
// 'User' =>
// array (
// 'email' => 'admin@gmail.com',
// 'password' => '123456',
// ),
// )
$exists = $this->find(array(
'conditions' => array(
'email' => $data[$this->alias]['email'],
'password' => Helper::hash($data[$this->alias]['password'])
)
), 'first');
if (!empty($exists)) {
$this->session->write(USER_INFO, $exists);
$this->session->write(LOGGED_IN, true);
return true;
}
return false;
}
public function isAdmin() {
$data = $this->session->read(USER_INFO);
return $data[$this->alias]['is_admin'];
// column is_admin value is 1
}
}<?php
require_once 'const.php';
require_once 'User.php';
$user = new User();
if ($_POST) {
$data = $_POST['data'];
if ($user->login($data)) {
if ($user->isAdmin()) {
echo 'isAdmin';
} else {
echo 'not isAdmin';
}
} else {
$login = false;
}
}
?>
<!DOCTYPE html>
<title>User Login</title>
<link href="css/reset.css" rel="stylesheet" type="text/css" media="all">
<link href="css/main.css" rel="stylesheet" type="text/css" media="all">
<link href="css/form.css" rel="stylesheet" type="text/css" media="all">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.0/jquery-ui.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.0/themes/base/jquery-ui.css">
</head>
<body>
<header>
<div class="logo">
<img src="images/film.png" width="45" />
<div class="title">Platinum Cineplex</div>
</div>
<nav>
<ul>
<li><a href="#"><img src="images/logout.png" width="25">Logout</a></li>
</ul>
</nav>
</header>
<nav>
<ul id="dropmenu">
<li>
<a href="#">Movie</a>
</li>
</ul>
</nav>
<div class="heading">User Login</div>
<?php if (isset($login) && !$login): ?>
<p class="err">Login failed! Please check your email and password!</p>
<?php endif; ?>
<form action="" class="form" method="post">
<section>
<dl>
<dt>Email</dt>
<dd>
<?php
echo $user->form->input('email');
echo $user->form->error('email');
?>
</dd>
</dl>
</section>
<section>
<dl>
<dt>Password</dt>
<dd>
<?php
echo $user->form->input('password');
echo $user->form->error('password');
?>
</dd>
</dl>
</section>
<section>
<dl>
<dd>
<input type="submit" name="submit" value="Login"><br><br>
<a href="http://localhost/testoop/login.php">Register</a>
</dd>
</dl>
</section>
</form>
</div>
</body>
</html>PreviousBước 10: Giải thích thêm phương thức renderStatement trong class Mysql (ok)NextList of HTTP status codes (ok)
Last updated