<?php
class
DBConnect
{
private
$dbname
= null;
private
$pdo
= null;
private
$persistent
= false;
private
$statement
= null;
private
$lastInsID
= null;
private
static
$_instance
= [];
private
function
__construct(
$dbname
,
$attr
)
{
$this
->dbname =
$dbname
;
$this
->persistent =
$attr
;
}
public
static
function
db(
$flag
='r',
$persistent
=false)
{
if
(!isset(
$flag
)){
$flag
= 'r';
}
if
(!
class_exists
('PDO'))
{
throw
new
Exception('not found PDO');
return
false;
}
$mysql_server
= Yaf_Registry::get('mysql');
if
(!isset(
$mysql_server
[
$flag
])){
return
false;
}
$options_arr
=
array
(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.
$mysql_server
[
$flag
]['charset'],PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC);
if
(
$persistent
=== true){
$options_arr
[PDO::ATTR_PERSISTENT] = true;
}
try
{
$pdo
=
new
PDO(
$mysql_server
[
$flag
]['connectionString'],
$mysql_server
[
$flag
]['username'],
$mysql_server
[
$flag
]['password'],
$options_arr
);
}
catch
(PDOException
$e
) {
throw
new
Exception(
$e
->getMessage());
return
false;
}
if
(!
$pdo
) {
throw
new
Exception('PDO CONNECT ERROR');
return
false;
}
return
$pdo
;
}
public
static
function
getInstance(
$dbname
= 'r',
$attr
= false)
{
$mysql_server
= Yaf_Registry::get('mysql');
if
(!isset(
$mysql_server
[
$dbname
])){
return
false;
}
$key
= md5(md5(
$dbname
.
$attr
,true));
if
(!isset(self::
$_instance
[
$key
]) || !
is_object
(self::
$_instance
[
$key
]))
self::
$_instance
[
$key
] =
new
self(
$dbname
,
$attr
);
return
self::
$_instance
[
$key
];
}
private
function
getConnect(){
$this
->pdo = self::db(
$this
->dbname,
$this
->persistent);
}
public
function
query(
$sql
,
$data
= [],
$one
= false)
{
if
(!
is_array
(
$data
) ||
empty
(
$sql
) || !
is_string
(
$sql
))
return
false;
$this
->free();
return
$this
->queryCommon(
$data
,
$sql
,
$one
);
}
private
function
queryCommon(
$data
,
$sql
,
$one
)
{
$this
->pdoExec(
$data
,
$sql
);
if
(
$one
){
return
$this
->statement->fetch(PDO::FETCH_ASSOC);
}
else
{
return
$this
->statement->fetchAll(PDO::FETCH_ASSOC);
}
}
public
function
queryes(
$arr_sql
,
$arr_data
= [],
$one
= false)
{
if
(!
is_array
(
$arr_sql
) ||
empty
(
$arr_sql
) || !
is_array
(
$arr_data
))
return
false;
$this
->free();
$res
= [];
$i
= 0;
foreach
(
$arr_sql
as
$val
) {
if
(!isset(
$arr_data
[
$i
]))
$arr_data
[
$i
] = [];
elseif
(!
is_array
(
$arr_data
[
$i
]))
throw
new
Exception('Error where queryes sql:'.
$val
.' where:'.
$arr_data
[
$i
]);
$res
[] =
$this
->queryCommon(
$arr_data
[
$i
],
$val
,
$one
);
$i
++;
}
return
$res
;
}
public
function
limitQuery(
$sql
,
$page
=0,
$pageSize
=20,
$data
= [])
{
$page
=
intval
(
$page
);
if
(
$page
< 0) {
return
[];
}
$pageSize
=
intval
(
$pageSize
);
if
(
$pageSize
> 0) {
$sql
.= ' LIMIT ' .
$pageSize
;
if
(
$page
> 0) {
$start_limit
= (
$page
- 1) *
$pageSize
;
$sql
.= ' OFFSET ' .
$start_limit
;
}
}
return
$this
->query(
$sql
,
$data
);
}
public
function
executeDDL(
$sql
,
$data
= [],
$Transaction
= false){
if
(!
is_array
(
$data
) || !
is_string
(
$sql
))
return
false;
$this
->free();
if
(
$Transaction
)
$this
->pdo->beginTransaction();
try
{
$this
->execRes(
$data
,
$sql
);
if
(
$Transaction
)
$this
->pdo->commit();
return
$this
->lastInsID;
}
catch
(Exception
$e
) {
if
(
$Transaction
)
$this
->pdo->rollBack();
throw
new
Exception('Error DDLExecute <=====>'.
$e
->getMessage());
return
false;
}
}
public
function
executeDDLes(
$arr_sql
,
$arr_data
= [],
$Transaction
= false){
if
(!
is_array
(
$arr_sql
) ||
empty
(
$arr_sql
) || !
is_array
(
$arr_data
))
return
false;
$res
= [];
$this
->free();
if
(
$Transaction
)
$this
->pdo->beginTransaction();
try
{
$i
= 0;
foreach
(
$arr_sql
as
$val
){
if
(!isset(
$arr_data
[
$i
]))
$arr_data
[
$i
] = [];
elseif
(!
is_array
(
$arr_data
[
$i
])){
if
(
$Transaction
)
$this
->pdo->rollBack();
throw
new
Exception('Error where DDLExecutees sql:'.
$val
.' where:'.
$arr_data
[
$i
]);
}
$this
->execRes(
$arr_data
[
$i
],
$val
);
$res
[] =
$this
->lastInsID;
$i
++;
}
if
(
$Transaction
)
$this
->pdo->commit();
return
$res
;
}
catch
(Exception
$e
) {
if
(
$Transaction
)
$this
->pdo->rollBack();
throw
new
Exception('Error DDLExecutees array_sql:'.json_encode(
$arr_sql
).' <=====>'.
$e
->getMessage());
return
false;
}
return
$res
;
}
public
function
countRows(
$sql
,
$data
= []){
if
(!
is_array
(
$data
) ||
empty
(
$sql
) || !
is_string
(
$sql
))
return
false;
$this
->free();
$res
=
$this
->pdoExec(
$data
,
$sql
);
if
(
$res
== false)
return
false;
return
$this
->statement->fetchColumn();
}
public
function
countRowses(
$arr_sql
,
$arr_data
= []){
if
(!
is_array
(
$arr_sql
) ||
empty
(
$arr_sql
) || !
is_array
(
$arr_data
))
return
false;
$res
= [];
$this
->free();
$i
= 0;
foreach
(
$arr_sql
as
$val
) {
if
(!isset(
$arr_data
[
$i
]))
$arr_data
[
$i
] = [];
elseif
(!
is_array
(
$arr_data
[
$i
]))
throw
new
Exception('Error where CountRowses sql:'.
$val
.' where:'.
$arr_data
[
$i
]);
$res1
=
$this
->pdoExec(
$arr_data
[
$i
],
$val
);
if
(
$res1
== false)
$res
[] = false;
else
$res
[] =
$this
->statement->fetchColumn();
}
return
$res
;
}
public
function
getDB(
$Transaction
=false)
{
$this
->Transaction =
$Transaction
;
$this
->getConnect();
if
(
$Transaction
=== true)
$this
->pdo->beginTransaction();
return
$this
;
}
public
function
execSq(
$sql
,
$data
= [])
{
if
(
$this
->checkParams(
$sql
,
$data
) === false)
return
false;
try
{
$this
->execRes(
$data
,
$sql
);
return
$this
->lastInsID;
}
catch
(Exception
$e
) {
if
(isset(
$this
->Transaction) &&
$this
->Transaction === true)
$this
->pdo->rollBack();
throw
new
Exception('Error execSq<=====>'.
$e
->getMessage());
return
false;
} finally {
if
(!
empty
(
$this
->statement))
{
$this
->statement->closeCursor();
unset(
$this
->statement);
}
}
}
public
function
querySq(
$sql
,
$data
= [],
$one
= false)
{
if
(
$this
->checkParams(
$sql
,
$data
) === false)
return
false;
return
$this
->pdoExecSq(
$sql
,
$data
,[1,
$one
]);
}
public
function
limitQuerySq(
$sql
,
$page
=0,
$pageSize
=20,
$data
= [])
{
$page
=
intval
(
$page
);
if
(
$page
< 0) {
return
[];
}
$pageSize
=
intval
(
$pageSize
);
if
(
$pageSize
> 0) {
$sql
.= ' LIMIT ' .
$pageSize
;
if
(
$page
> 0) {
$start_limit
= (
$page
- 1) *
$pageSize
;
$sql
.= ' OFFSET ' .
$start_limit
;
}
}
return
$this
->querySq(
$sql
,
$data
);
}
public
function
countRowsSq(
$sql
,
$data
= []){
if
(
$this
->checkParams(
$sql
,
$data
) === false)
return
false;
return
$this
->pdoExecSq(
$sql
,
$data
,[2]);
}
public
function
sQCommit()
{
if
(
empty
(
$this
->pdo) || !
is_object
(
$this
->pdo))
return
false;
if
(isset(
$this
->Transaction) &&
$this
->Transaction === true)
$this
->pdo->commit();
unset(
$this
->pdo);
}
public
function
checkParams(
$sql
,
$data
)
{
if
(
empty
(
$this
->pdo) || !
is_object
(
$this
->pdo) || !
is_array
(
$data
) ||
empty
(
$sql
) || !
is_string
(
$sql
))
return
false;
return
true;
}
private
function
pdoExecSq(
$sql
,
$data
,
$select
= []){
try
{
$res
=
$this
->pdoExec(
$data
,
$sql
);
if
(
empty
(
$select
))
return
$res
;
else
{
if
(
$select
[0] === 1){
if
(
$select
[1] === true)
return
$this
->statement->fetch(PDO::FETCH_ASSOC);
else
return
$this
->statement->fetchAll(PDO::FETCH_ASSOC);
}
elseif
(
$select
[0] === 2)
return
$this
->statement->fetchColumn();
else
return
false;
}
}
catch
(Exception
$e
) {
throw
new
Exception(
$e
->getMessage());
return
false;
} finally {
if
(!
empty
(
$this
->statement))
{
$this
->statement->closeCursor();
unset(
$this
->statement);
}
}
}
private
function
execRes(
$data
,
$sql
){
$res
=
$this
->pdoExec(
$data
,
$sql
);
$in_id
=
$this
->pdo->lastInsertId();
if
(preg_match(
"/^\s*(INSERT\s+INTO|REPLACE\s+INTO)\s+/i"
,
$sql
) && !
empty
(
$in_id
))
$this
->lastInsID =
$in_id
;
else
$this
->lastInsID =
$res
;
}
private
function
pdoExec(
$data
,
$sql
){
$this
->statement =
$this
->pdo->prepare(
$sql
);
if
(false ===
$this
->statement)
return
false;
if
(!
empty
(
$data
))
{
foreach
(
$data
as
$k
=>
$v
)
{
$this
->statement->bindValue(
$k
,
$v
);
}
}
$res
=
$this
->statement->execute();
if
(!
$res
)
{
throw
new
Exception('sql:'.
$sql
.'<====>where:'.json_encode(
$data
).'<====>error:'.json_encode(
$this
->statement->errorInfo()));
}
else
{
return
$res
;
}
}
private
function
free()
{
if
(
is_null
(
$this
->pdo))
$this
->getConnect();
if
(!
empty
(
$this
->statement))
{
$this
->statement->closeCursor();
$this
->statement = null;
}
}
}
?>