Ricycle 1.1 Mysql
Mysql
<?php
require_once 'database.php';
class Mysql {
public $config = [];
public $keywords = [
'>=',
'<=',
'>',
'<',
'IN',
'NOT',
'IS',
'LIKE',
'!=',
'<>',
];
static $instance = null;
public function __construct($config = []) {
$this->config = $config;
return $this->connect();
}
public static function getInstance() {
$config = [
'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;
}
public 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;
}
$this->setEncoding($config['encoding']);
return $this->connected;
}
public function setEncoding($enc) {
return $this->_execute('SET NAMES ' . $enc) != false;
}
public function _execute($sql) {
return mysqli_query($this->connection, $sql);
}
protected function buildConditions($condition) {
// $condition is
// array (
// 'email' => 'admin@gmail.com',
// 'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
// )
if (empty($condition)) {
return;
}
$conditionArray = [];
foreach ($condition as $field => $sub) {
$math = '';
if (!in_array($sub, $this->keywords)) {
$math = '=';
}
$conditionArray[] = " " . $field . " " . $math . " '" . $sub . "' ";
}
return " WHERE " . implode(' AND ', $conditionArray);
}
public function select($myTable, $options = [], $isCount = false) {
// $options is
// array (
// 'conditions' =>
// array (
// 'email' => 'admin@gmail.com',
// 'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
// ),
// )
$myFields = isset($options['fields']) ? $options['fields'] : '*';
$conditions = isset($options['conditions']) ? $options['conditions'] : null;
try {
$returnArr = [];
$table = $myTable;
$alias = $joins = $order = $group = $limit = "";
$fields = "";
if (is_array($myFields)) {
$fields = implode(', ', $myFields);
} else {
$fields = $myFields;
}
$conditions = $this->buildConditions($conditions);
// $conditions is " WHERE email = 'admin@gmail.com' AND password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
$tmpTable = explode('_', $table);
// array (
// 0 => 'user',
// )
$alias = [];
foreach ($tmpTable as $tmp) {
$alias[] = ucfirst($tmp);
}
$alias = implode($alias);
// $alias is User
$query = compact('table', 'alias', 'joins', 'fields', 'conditions', 'joins', 'group', 'order', 'limit');
// array (
// 'table' => 'user',
// 'alias' => 'User',
// 'joins' => '',
// 'fields' => '*',
// 'conditions' => ' WHERE email = \'wordpress\' AND password = \'b1909932aac1c5510c044de0cb8c0f3ef049a250\'',
// 'group' => '',
// 'order' => '',
// 'limit' => '',
// )
$sql = $this->renderStatement('select', $query);
// $sql is " SELECT * FROM user User WHERE email = 'admin@gmail.com' AND password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
$returnArr = $this->fetchAll($sql);
// $returnArr is array (
// 0 =>
// array (
// 'MovieCategory' =>
// array (
// 'id' => '1',
// 'title' => 'Category 1',
// 'created' => '2020-04-02 19:02:57',
// 'modified' => '2020-04-02 19:02:57',
// ),
// ),
// )
// array (
// 0 =>
// array (
// 'Movie' =>
// array (
// 'id' => '1',
// 'title' => 'Title 1',
// 'duration' => '9',
// 'director' => 'Director 1',
// 'actor' => 'Actor 1',
// 'language' => 'Language 1',
// 'country' => 'VN',
// 'category_id' => '1',
// 'description' => 'Description 1',
// 'open_date' => '2020-04-03',
// 'trial_url' => 'xhNwhL58c9E',
// 'created' => '2020-04-02 19:03:56',
// 'modified' => '2020-04-02 19:03:56',
// ),
// 'movie_category' =>
// array (
// 'id' => '1',
// 'title' => 'Category 1',
// 'created' => '2020-04-02 19:02:57',
// 'modified' => '2020-04-02 19:02:57',
// ),
// ),
// )
return $returnArr;
} catch (Exception $ex) {
var_dump($ex);
}
}
public function renderStatement($type, $data) {
// array (
// 'table' => 'user',
// 'alias' => 'User',
// 'joins' => '',
// 'fields' => '*',
// 'conditions' => ' WHERE email = \'lionel@gmail.com\' AND password = \'7c4a8d09ca3762af61e59520943dc26494f8941b\'',
// 'group' => '',
// 'order' => '',
// 'limit' => '',
// )
extract($data);
switch (strtolower($type)) {
case 'select':
return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {order} {$limit}";
break;
}
}
public function fetchAll($sql) {
if ($this->execute($sql)) {
$out = [];
while ($item = $this->fetchRow()) {
$out[] = $item;
}
return $out;
} else {
return false;
}
}
public 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();
return $this->_result;
}
public function lastAffected() {
if ($this->_result) {
return mysqli_affected_rows($this->connection);
}
return null;
}
public function lastError() {
if (mysqli_errno($this->connection)) {
return mysqli_errno($this->connection) . ': ' . mysqli_error($this->connection);
}
return null;
}
public function lastNumRows() {
if ($this->_result and is_object($this->_result)) {
return @mysqli_num_rows($this->_result);
}
return null;
}
function fetchRow() {
if (is_resource($this->_result) || is_object($this->_result)) {
$this->resultSet($this->_result);
$resultRow = $this->fetchResult();
// $resultRow is
// array (
// 'MovieCategory' =>
// array (
// 'id' => '1',
// 'title' => 'Category 1',
// 'created' => '2020-04-02 19:02:57',
// 'modified' => '2020-04-02 19:02:57',
// ),
// )
// false
// array (
// 'Movie' =>
// array (
// 'id' => '1',
// 'title' => 'Title 1',
// 'duration' => '9',
// 'director' => 'Director 1',
// 'actor' => 'Actor 1',
// 'language' => 'Language 1',
// 'country' => 'VN',
// 'category_id' => '1',
// 'description' => 'Description 1',
// 'open_date' => '2020-04-03',
// 'trial_url' => 'xhNwhL58c9E',
// 'created' => '2020-04-02 19:03:56',
// 'modified' => '2020-04-02 19:03:56',
// ),
// 'movie_category' =>
// array (
// 'id' => '1',
// 'title' => 'Category 1',
// 'created' => '2020-04-02 19:02:57',
// 'modified' => '2020-04-02 19:02:57',
// ),
// )
// false
return $resultRow;
} else {
return null;
}
}
public function resultSet(&$results) {
$this->results = &$results;
$this->map = [];
$num_fields = mysqli_num_fields($results);
// (object) array(
// 'name' => 'id',
// 'orgname' => 'id',
// 'table' => 'User',
// 'orgtable' => 'user',
// 'def' => '',
// 'db' => 'ticket',
// 'catalog' => 'def',
// 'max_length' => 0,
// 'length' => 11,
// 'charsetnr' => 63,
// 'flags' => 49667,
// 'type' => 3,
// 'decimals' => 0,
// )
// ...
$index = 0;
$j = 0;
while ($j < $num_fields) {
$column = mysqli_fetch_field_direct($results, $j);
if (!empty($column->table)) {
$this->map[$index++] = [
$column->table,
$column->name,
];
} else {
$this->map[$index++] = [
0,
$column->name,
];
}
$j++;
}
// [
// 0 => [
// 0 => 'User',
// 1 => 'id',
// ],
// 1 => [
// 0 => 'User',
// 1 => 'email',
// ],
// 2 => [
// 0 => 'User',
// 1 => 'password',
// ],
// 3 => [
// 0 => 'User',
// 1 => 'fullname',
// ],
// 4 => [
// 0 => 'User',
// 1 => 'address',
// ],
// 5 => [
// 0 => 'User',
// 1 => 'is_admin',
// ],
// 6 => [
// 0 => 'User',
// 1 => 'created',
// ],
// 7 => [
// 0 => 'User',
// 1 => 'modified',
// ],
// ];
}
public function fetchResult() {
if ($row = mysqli_fetch_row($this->results)) {
// $this->results
// mysqli_result::__set_state(array(
// 'current_field' => NULL,
// 'field_count' => NULL,
// 'lengths' => NULL,
// 'num_rows' => NULL,
// 'type' => NULL,
// ))
// =================================================
// $row is
// table is movie_category
// array (
// 0 => '1',
// 1 => 'Category 1',
// 2 => '2020-04-02 19:02:57',
// 3 => '2020-04-02 19:02:57',
// )
// table is movie
// array (
// 0 => '1',
// 1 => 'Title 1',
// 2 => '9',
// 3 => 'Director 1',
// 4 => 'Actor 1',
// 5 => 'Language 1',
// 6 => 'VN',
// 7 => '1',
// 8 => 'Description 1',
// 9 => '2020-04-03',
// 10 => 'xhNwhL58c9E',
// 11 => '2020-04-02 19:03:56',
// 12 => '2020-04-02 19:03:56',
// 13 => '1',
// 14 => 'Category 1',
// 15 => '2020-04-02 19:02:57',
// 16 => '2020-04-02 19:02:57',
// )
$resultRow = [];
foreach ($row as $index => $field) {
@list($table, $column) = $this->map[$index];
$resultRow[$table][$column] = $row[$index];
}
return $resultRow;
} else {
return false;
}
}
// fetchResult
====================================================
$resultRow is
====================================================
array (
'User' =>
array (
'id' => '3',
'email' => 'lionel@gmail.com',
'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
'fullname' => 'Lionel',
'address' => 'ABC',
'is_admin' => NULL,
'created' => '2020-04-03 19:24:49',
'modified' => '2020-04-03 19:24:49',
),
)
false
====================================================
End $resultRow is
====================================================
};
?>AppModel
DATABASE
Form
Helper
login
Session
User
Last updated
Was this helpful?