PHP MySQL 打包类 MysqliDb

项目地址 https://github.com/ThingEngineer/PHP-MySQLi-Database-Class 初始化 默认情况下设置的 utf8 夏斯特的简单初始化: $db = new MysqliDb (host, username, password, databaseName); 高级初始化: $db = new MysqliD

项目地址 https://github.com/ThingEngineer/PHP-MySQLi-Database-Class


初始化

默认情况下设置的 utf8 夏斯特的简单初始化:

$db = new MysqliDb ('host', 'username', 'password', 'databaseName');

高级初始化:

$db = new MysqliDb (Array (
                'host' => 'host',
                'username' => 'username', 
                'password' => 'password',
                'db'=> 'databaseName',
                'port' => 3306,
                'prefix' => 'my_',
                'charset' => 'utf8'));

表前缀,端口和数据库的坐盘是可选的。如果没有夏塞特应该设置夏塞特,设置为空

此外,还可以重复使用已连接的mysqli对象:

$mysqli = new mysqli ('host', 'username', 'password', 'databaseName');
$db = new MysqliDb ($mysqli);

如果在对象创建过程中未设置表前缀,则以后可以单独调用设置表前缀:

$db->setPrefix ('my_');

如果连接到mysql将被丢弃,迈斯利德布将尝试自动重新连接到数据库一次。禁用此贝哈沃使用

$db->autoReconnect = false;

如果您需要从其他类或函数使用中获取已创建的mysqliDb对象

    function init () {
        // db staying private here
        $db = new MysqliDb ('host', 'username', 'password', 'databaseName');
    }
    ...
    function myfunc () {
        // obtain db object created in init  ()
        $db = MysqliDb::getInstance();
        ...
    }

多个数据库连接

如果您需要连接到多个数据库,请使用以下方法:

$db->addConnection('slave', Array (
                'host' => 'host',
                'username' => 'username',
                'password' => 'password',
                'db'=> 'databaseName',
                'port' => 3306,
                'prefix' => 'my_',
                'charset' => 'utf8')
);

要选择数据库使用连接()方法

$users = $db->connection('slave')->get('users');

对象映射

db对象.php是一个对象映射库内置在mysqliDb的顶部,以提供模型表示功能。有关更多信息,请参阅 db 对象手册

插入查询

简单示例

$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe'
);
$id = $db->insert ('users', $data);
if($id)
    echo 'user was created. Id=' . $id;

使用功能插入

$data = Array (
	'login' => 'admin',
    'active' => true,
	'firstName' => 'John',
	'lastName' => 'Doe',
	'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
	// password = SHA1('secretpassword+salt')
	'createdAt' => $db->now(),
	// createdAt = NOW()
	'expires' => $db->now('+1Y')
	// expires = NOW() + interval 1 year
	// Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
);

$id = $db->insert ('users', $data);
if ($id)
    echo 'user was created. Id=' . $id;
else
    echo 'insert failed: ' . $db->getLastError();

插入重复的关键更新

$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe',
               "createdAt" => $db->now(),
               "updatedAt" => $db->now(),
);
$updateColumns = Array ("updatedAt");
$lastInsertId = "id";
$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->insert ('users', $data);

同时插入多个数据集

$data = Array(
    Array ("login" => "admin",
        "firstName" => "John",
        "lastName" => 'Doe'
    ),
    Array ("login" => "other",
        "firstName" => "Another",
        "lastName" => 'User',
        "password" => "very_cool_hash"
    )
);
$ids = $db->insertMulti('users', $data);
if(!$ids) {
    echo 'insert failed: ' . $db->getLastError();
} else {
    echo 'new users inserted with following id's: ' . implode(', ', $ids);
}

如果所有数据集都只有相同的密钥,则可以简化

$data = Array(
    Array ("admin", "John", "Doe"),
    Array ("other", "Another", "User")
);
$keys = Array("login", "firstName", "lastName");

$ids = $db->insertMulti('users', $data, $keys);
if(!$ids) {
    echo 'insert failed: ' . $db->getLastError();
} else {
    echo 'new users inserted with following id's: ' . implode(', ', $ids);
}

替换查询

替换()方法实现与插入相同的API():

更新查询

$data = Array (
	'firstName' => 'Bobby',
	'lastName' => 'Tables',
	'editCount' => $db->inc(2),
	// editCount = editCount + 2;
	'active' => $db->not()
	// active = !active;
);
$db->where ('id', 1);
if ($db->update ('users', $data))
    echo $db->count . ' records were updated';
else
    echo 'update failed: ' . $db->getLastError();

update()还支持限制参数:

$db->update ('users', $data, 10);
// Gives: UPDATE users SET ... LIMIT 10

选择查询

在任何选择/获取功能呼叫量或返回行后,存储在$count变量中

$users = $db->get('users'); //contains an Array of all users 
$users = $db->get('users', 10); //contains an Array 10 users

或选择自定义列集。也可以使用功能

$cols = Array ("id", "name", "email");
$users = $db->get ("users", null, $cols);
if ($db->count > 0)
    foreach ($users as $user) { 
        print_r ($user);
    }

或只选择一行

$db->where ("id", 1);
$user = $db->getOne ("users");
echo $user['id'];

$stats = $db->getOne ("users", "sum(id), count(*) as cnt");
echo "total ".$stats['cnt']. "users found";

或选择一个列值或功能结果

$count = $db->getValue ("users", "count(*)");
echo "{$count} users found";

从多个行中选择一个列值或函数结果:

$logins = $db->getValue ("users", "login", null);
// select login from users
$logins = $db->getValue ("users", "login", 5);
// select login from users limit 5
foreach ($logins as $login)
    echo $login;

插入数据

您也可以加载。简历或.XML 数据放入特定表中。要插入.csv数据,请使用以下语法:

$path_to_file = "/home/john/file.csv";
$db->loadData("users", $path_to_file);

这将加载一个名为文件的.csv文件.csv文件夹/家庭/约翰/(约翰的家庭目录)您还可以附加可选的选项阵列。有效的选项是:

Array(
	"fieldChar" => ';', 	// Char which separates the data
	"lineChar" => 'rn', 	// Char which separates the lines
	"linesToIgnore" => 1	// Amount of lines to ignore at the beginning of the import
);

使用附加它们

$options = Array("fieldChar" => ';', "lineChar" => 'rn', "linesToIgnore" => 1);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA ...

您可以指定使用本地数据而不是数据

$options = Array("fieldChar" => ';', "lineChar" => 'rn', "linesToIgnore" => 1, "loadDataLocal" => true);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA LOCAL ...

插入XML

要将 XML 数据加载到表中,您可以使用加载 XML 的方法。语法是微笑的负载数据语法。

$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file);

您还可以添加可选参数。有效参数:

Array(
	"linesToIgnore" => 0,		// Amount of lines / rows to ignore at the beginning of the import
	"rowTag"	=> "<user>"	// The tag which marks the beginning of an entry
)

用法:

$options = Array("linesToIgnore" => 0, "rowTag"	=> "<user>"):
$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file, $options);

分页

使用帕金茨()而不是获取()获取受精结果

$page = 1;
// set page limit to 2 results per page. 20 by default
$db->pageLimit = 2;
$products = $db->arraybuilder()->paginate("products", $page);
echo "showing $page out of " . $db->totalPages;

结果转换/映射

与其获得纯粹的结果阵列,不如在关联阵列中获得与所需密钥的结果。如果只有 2 个字段取取将设置在 get(), 方法将返回结果阵列 ($k => $v) 和阵列 ($k = >阵列 ($v, $v)) 在其余的情况下。

$user = $db->map ('login')->ObjectBuilder()->getOne ('users', 'login, id');
Array
(
    [user1] => 1
)

$user = $db->map ('login')->ObjectBuilder()->getOne ('users', 'id,login,createdAt');
Array
(
    [user1] => stdClass Object
        (
            [id] => 1
            [login] => user1
            [createdAt] => 2015-10-22 22:27:53
        )

)

定义返回类型

MysqliDb 可以返回导致 3 种不同的格式:阵列阵列、对象阵列和 Json 字符串。要选择返回类型,请使用阵列建设器()、对象建设者 () 和 JsonBuilder () 方法。请注意,阵列建设者()是默认返回类型

// Array return type
$u= $db->getOne("users");
echo $u['login'];
// Object return type
$u = $db->ObjectBuilder()->getOne("users");
echo $u->login;
// Json return type
$json = $db->JsonBuilder()->getOne("users");

运行原始 SQL 查询

$users = $db->rawQuery('SELECT * from users where id >= ?', Array (10));
foreach ($users as $user) {
    print_r ($user);
}

为了避免长时间检查,如果有夫妇帮手功能,以处理原始查询选择结果:

获取 1 行结果:

$user = $db->rawQueryOne('SELECT * from users where id=?', Array(10));
echo $user['login'];
// Object return type
$user = $db->ObjectBuilder()->rawQueryOne('SELECT * from users where id=?', Array(10));
echo $user->login;

获取 1 列值作为字符串:

$password = $db->rawQueryValue('SELECT password from users where id=? limit 1', Array(10));
echo "Password is {$password}";
NOTE: for a rawQueryValue() to return string instead of an array 'limit 1' should be added to the end of the query.

从多行获取 1 列值:

$logins = $db->rawQueryValue('SELECT login from users limit 10');
foreach ($logins as $login)
    echo $login;

更高级示例:

$params = Array(1, 'admin');
$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);
print_r($users); // contains Array of returned rows

// will handle any SQL query
$params = Array(10, 1, 10, 11, 2, 10);
$q = "(
    SELECT a FROM t1
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
) UNION (
    SELECT a FROM t2 
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
)";
$results = $db->rawQuery ($q, $params);
print_r ($results); // contains Array of returned rows

方法/方法

where(),和方法允许您指定查询的地点和条件。所有条件都由()由()有()支持的所有条件提供支持。orWhere()having()orHaving()

警告:为了使用列与列的比较,仅原始条件应用作列名称或功能,不能作为绑定变量传递。

常规==具有变量的操作员:

$db->where ('id', 1);
$db->where ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 AND login='admin';
$db->where ('id', 1);
$db->having ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 HAVING login='admin';

常规==操作员,具有列与列的比较:

// WRONG
$db->where ('lastLogin', 'createdAt');
// CORRECT
$db->where ('lastLogin = createdAt');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE lastLogin = createdAt;
$db->where ('id', 50, ">=");
// or $db->where ('id', Array ('>=' => 50));
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id >= 50;

中间/不在:

$db->where('id', Array (4, 20), 'BETWEEN');
// or $db->where ('id', Array ('BETWEEN' => Array(4, 20)));

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20

在/不在:

$db->where('id', Array(1, 5, 27, -1, 'd'), 'IN');
// or $db->where('id', Array( 'IN' => Array(1, 5, 27, -1, 'd') ) );

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id IN (1, 5, 27, -1, 'd');

或案例:

$db->where ('firstName', 'John');
$db->orWhere ('firstName', 'Peter');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE firstName='John' OR firstName='peter'

空对比:

$db->where ("lastName", NULL, 'IS NOT');
$results = $db->get("users");
// Gives: SELECT * FROM users where lastName IS NOT NULL

喜欢比较:

$db->where ("fullName", 'John%', 'like');
$results = $db->get("users");
// Gives: SELECT * FROM users where fullName like 'John%'

此外,您可以在条件条件下使用原始:

$db->where ("id != companyId");
$db->where ("DATE(createdAt) = DATE(lastLogin)");
$results = $db->get("users");

或具有变量的原始条件:

$db->where ("(id = ? or id = ?)", Array(6,2));
$db->where ("login","mike");
$res = $db->get ("users");
// Gives: SELECT * FROM users WHERE (id = 6 or id = 2) and login='mike';

查找匹配行的总数。简单的铺纸示例:

$offset = 10;
$count = 15;
$users = $db->withTotalCount()->get('users', Array ($offset, $count));
echo "Showing {$count} from {$db->totalCount}";

查询关键字

添加低优先级|延迟|高优先级|忽略和mysql关键字的其余部分插入(),替换(),获取(),更新(),删除()方法或更新|锁定共享模式以选择():

$db->setQueryOption ('LOW_PRIORITY')->insert ($table, $param);
// GIVES: INSERT LOW_PRIORITY INTO table ...
$db->setQueryOption ('FOR UPDATE')->get ('users');
// GIVES: SELECT * FROM USERS FOR UPDATE;

您还可以使用一系列关键字:

$db->setQueryOption (Array('LOW_PRIORITY', 'IGNORE'))->insert ($table,$param);
// GIVES: INSERT LOW_PRIORITY IGNORE INTO table ...

同样的方式关键字也可以用于选择查询:

$db->setQueryOption ('SQL_NO_CACHE');
$db->get("users");
// GIVES: SELECT SQL_NO_CACHE * FROM USERS;

可选地,您可以使用方法链调用多次,而无需一遍又一遍地引用对象:

$results = $db
	->where('id', 1)
	->where('login', 'admin')
	->get('users');

删除查询

$db->where('id', 1);
if($db->delete('users')) echo 'successfully deleted';

订购方法

$db->orderBy("id","asc");
$db->orderBy("login","Desc");
$db->orderBy("RAND ()");
$results = $db->get('users');
// Gives: SELECT * FROM users ORDER BY id ASC,login DESC, RAND ();

按值顺序示例:

$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));
$db->get('users');
// Gives: SELECT * FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;

如果您使用的是设置Prefix()功能,并且需要按顺序使用表名,请确保表名与""一起逃逸。

$db->setPrefix ("t_");
$db->orderBy ("users.id","asc");
$results = $db->get ('users');
// WRONG: That will give: SELECT * FROM t_users ORDER BY users.id ASC;

$db->setPrefix ("t_");
$db->orderBy ("`users`.id", "asc");
$results = $db->get ('users');
// CORRECT: That will give: SELECT * FROM t_users ORDER BY t_users.id ASC;

分组方法

$db->groupBy ("name");
$results = $db->get ('users');
// Gives: SELECT * FROM users GROUP BY name;

加入方法

加入表产品与表用户与左加入租户ID

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->where("u.id", 6);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT u.name, p.productName FROM products p LEFT JOIN users u ON p.tenantID=u.tenantID WHERE u.id = 6

加入条件

添加和条件加入语句

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT  u.name, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

添加或条件加入语句

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinOrWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID OR u.tenantID = 5)

属性共享

也可以复制属性

$db->where ("agentId", 10);
$db->where ("active", true);

$customers = $db->copy ();
$res = $customers->get ("customers", Array (10, 10));
// SELECT * FROM customers WHERE agentId = 10 AND active = 1 LIMIT 10, 10

$cnt = $db->getValue ("customers", "count(id)");
echo "total records found: " . $cnt;
// SELECT count(id) FROM customers WHERE agentId = 10 AND active = 1

子查询

子查询

子查询,无需别名用于插入/更新/其中 Eg。(从用户中选择*)

$sq = $db->subQuery();
$sq->get ("users");

带有指定别名的子查询,用于JOIN。例如.(从用户中选择*) 平方

$sq = $db->subQuery("sq");
$sq->get ("users");

在选择中的子查询:

$ids = $db->subQuery ();
$ids->where ("qty", 2, ">");
$ids->get ("products", null, "userId");

$db->where ("id", $ids, 'in');
$res = $db->get ("users");
// Gives SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)

插入中的子查询:

$userIdQ = $db->subQuery ();
$userIdQ->where ("id", 6);
$userIdQ->getOne ("users", "name"),

$data = Array (
    "productName" => "test product",
    "userId" => $userIdQ,
    "lastUpdated" => $db->now()
);
$id = $db->insert ("products", $data);
// Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());

加入中的子查询:

$usersQ = $db->subQuery ("u");
$usersQ->where ("active", 1);
$usersQ->get ("users");

$db->join($usersQ, "p.userId=u.id", "LEFT");
$products = $db->get ("products p", null, "u.login, p.productName");
print_r ($products);
// SELECT u.login, p.productName FROM products p LEFT JOIN (SELECT * FROM t_users WHERE active = 1) u on p.userId=u.id;

存在/不存在条件

$sub = $db->subQuery();
$sub->where("company", 'testCompany');
$sub->get ("users", null, 'userId');
$db->where (null, $sub, 'exists');
$products = $db->get ("products");
// Gives SELECT * FROM products WHERE EXISTS (SELECT userId FROM users WHERE company='testCompany')

有方法

如果存在至少满足此方法之前指定条件的元素,则返回 TRUE 的方便功能。

$db->where("user", $user);
$db->where("password", md5($password));
if($db->has("users")) {
    return "You are logged";
} else {
    return "Wrong user/password";
}

帮手方法

与数据库断开连接:

    $db->disconnect();

重新连接,以防mysql连接死亡:

if (!$db->ping())
    $db->connect()

获取最后执行的 SQL 查询:请注意,此方法仅返回 SQL 查询以进行调试,因为其执行很可能因字符变量周围的报价缺失而失败。

    $db->get('users');
    echo "Last executed query was ". $db->getLastQuery();

检查表是否存在:

    if ($db->tableExists ('users'))
        echo "hooray";

mysqli_real_escape_string()包装:

    $escaped = $db->escape ("' and 1=1");

交易帮手

请记住,交易正在无开发中心表上工作。如果插入失败,则回滚交易:

$db->startTransaction();
...
if (!$db->insert ('myTable', $insertData)) {
    //Error while saving, cancel new record
    $db->rollback();
} else {
    //OK
    $db->commit();
}

错误帮助者

执行查询后,您可以选择检查是否有错误。您可以获取MySQL错误字符串或最后执行的查询的错误代码。

$db->where('login', 'admin')->update('users', ['firstName' => 'Jack']);

if ($db->getLastErrno() === 0)
    echo 'Update succesfull';
else
    echo 'Update failed. Error: '. $db->getLastError();

查询执行时间基准

应调用跟踪()功能来跟踪查询执行时间设置。。

$db->setTrace (true);
// As a second parameter it is possible to define prefix of the path which should be striped from filename
// $db->setTrace (true, $_SERVER['SERVER_ROOT']);
$db->get("users");
$db->get("test");
print_r ($db->trace);
    [0] => Array
        (
            [0] => SELECT  * FROM t_users ORDER BY `id` ASC
            [1] => 0.0010669231414795
            [2] => MysqliDb->get() >>  file "/avb/work/PHP-MySQLi-Database-Class/tests.php" line #151
        )

    [1] => Array
        (
            [0] => SELECT  * FROM t_test
            [1] => 0.00069189071655273
            [2] => MysqliDb->get() >>  file "/avb/work/PHP-MySQLi-Database-Class/tests.php" line #152
        )

桌面锁定

要锁定表,您可以使用锁定方法与设置锁定模组。下列示例将锁定表用户进行书写访问。

$db->setLockMethod("WRITE")->lock("users");

调用另一个->锁()将删除第一个锁。您也可以使用

$db->unlock();

解锁之前锁定的表。要锁定多个表,您可以使用数组。例:

$db->setLockMethod("READ")->lock(array("users", "log"));

这将锁定表用户仅记录 READ访问。请确保您使用*解锁()之后,否则您的表将保持锁定!
 

转载请注明:https://www.yooym.com/blog/jianzhanjiaocheng/tongyongjiaocheng/2021/0531/1695.html
------分隔线----------------------------
文章分类
推荐内容
  • php通用实用文件上传类随机命名后缀限制

    不多说直接上代码,支持随机命名,路径,文件大小,后缀限制 ?php class UploadFile{ //完整实例 $upload = new UploadFile(true, '../yuming/', 'xls|xlsx'); 随机命名、路径、...

  • 解决SSL因为使用RC4密码套件或支持弱Diffie-Hellman(DH)密钥交换降级为B

    解决SSL因为使用RC4密码套件或支持弱Diffie-Hellman(DH)密钥交换降级为B 服务器环境IIS7 通过修改注册表关闭不安全的支持 复制下列文件 保存为 reg格式 直接导...

  • 实现HTTP跳转到HTTPS 的另一种方法

    1 首先在您的网站下新建一个站点,名称随意,在属性中分配 TCP 端口为 80 , SSL 不分配 然后在属性》主目录下配置 将此资源的内容来自:改为重定向到...

  • SSL证书安装指南 - Winodws 2003 Server IIS 6.0

    S SL证书安装指南 - Winodws 2003 Server IIS 6.0 一旦您申请的SSL证书成功颁发,您会收到一个邮件通知您取回证书,点击邮件中的取回证书链接,就可以得到您的...

  • SSL证书安装指南 - IIS 7.0

    一、生成证书请求 1.进入IIS控制台 进入IIS控制台,并选择服务器的服务器证书设置选项。 2.添加证书请求 进入服务器证书配置页面,并选择“创建证书申...

  • 网页启用Gzip压缩 提高浏览速度

    Gzip压缩我很早已经就启用了,不过从未与大家分享过。近期由于备案问题,临时更换服务器,结果忘了启用Gzip压缩,周末利用空余时间启用了Gzip压缩,并...