PHP download

PHP code example of zhangzhaowy / php-mysql

1. Go to this page and download the library: Download zhangzhaowy/php-mysql library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

zhangzhaowy / php-mysql example snippets


use Zhangzhaowy\Phpmysql\Db;


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


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


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


$db->setPrefix ('my_');


$db->autoReconnect = false;


// 创建过的Mysql链接
$db = new Db('host', 'username', 'password', 'databaseName');
...
...
// 要启用创建过的Mysql链接
$db = Db::getInstance();
...


$data = [
    "login" => "admin",
    "firstName" => "John",
    "lastName" => 'Doe'
];
$id = $db->table('users')->insert($data);
if($id) {
    echo 'user was created. Id=' . $id;
} else {
    echo 'insert failed: ' . $db->getLastError();
}


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


$data = [
    'firstName' => 'Bobby',
    'lastName' => 'Tables',
];
$db->where('id', 1)->limit(1);
if ($db->table('users')->update($data)) {
    echo $db->count . ' records were updated';
} else {
    echo 'update failed: ' . $db->getLastError();
}


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


// 包含全部用户
$users = $db->from('users')->getAll();
// 包含一个用户
$users = $db->from('users')->getOne();
// 包含所有用户的id
$users = $db->from('users')->getColumn('id');


$users = $db->from('users u')->getOne();
// select * from my_users u limit 1;
$users = $db->from(['users' => 'u'])->getOne();
// select * from my_users u limit 1;


$users = $db->from('users')->select(['id', 'name'])->getOne();
// ['id' => 1, 'name' => 'user1']
$users = $db->from('users')->select('id, name'])->getOne();
// ['id' => 1, 'name' => 'user1']


$users = $db->from('users')->select('id, name AS username'])->getOne();
$users = $db->from('users')->select(['id', 'name AS username'])->getOne();
$users = $db->from('users')->select(['id', 'name' => 'username'])->getOne();
// ['id' => 1, 'username' => 'user1']


// 左联
$users = $db->from('users')->leftJoin('score', '`score`.`uid` = `users`.`id`')->getAll();
// 右联
$users = $db->from('score s')->rightJoin('users u', 'u.`id` = s.`uid`')->getAll();
// 自定义联表
$users = $db->from('users u')->join('INNER', 'score s', 'u.`id` = s.`uid`')->getAll();
// joinWhere() 第一个参数与join的表名要一致，第二个参数与where()用法一致
$users = $db->from('users u')->leftJoin('score s', 's.`uid` = u.`id`')->joinWhere('score s', ['s.active' => 1])->getAll();


// SELECT * FROM my_users WHERE 1=1 AND 2=2
$db->from('users')->where('1=1 AND 2=2')->getAll();
// SELECT * FROM my_users WHERE id = '1' OR id = '5'
$db->from('users')->where(['id = ? OR id = ?', [1, 5]])->getAll();
// SELECT * FROM my_users WHERE name IS NULL
$db->from('users')->where(['name'])->getAll();
$db->from('users')->where(['name', 'IS', NULL])->getAll();
// SELECT * FROM my_users WHERE name IS NOT NULL
$db->from('users')->where(['name', 'IS NOT', NULL])->getAll();
// SELECT * FROM my_users WHERE id = '1' 
$db->from('users')->where(['id' => 1])->getAll();
$db->from('users')->where(['id', 1])->getAll();
// SELECT * FROM my_users WHERE id in ( '1', '2', '3' )
$db->from('users')->where(['id' => [1, 2, 3]])->getAll();
$db->from('users')->where(['id', [1, 2, 3]])->getAll();
SELECT * FROM my_users WHERE id BETWEEN '1' AND '5' 
$db->from('users')->where(['id' => ['BETWEEN' => [1, 5]]])->getAll();
$db->from('users')->where(['id', ['BETWEEN' => [1, 5]]])->getAll();
$db->from('users')->where(['id', 'BETWEEN', [1, 5]])->getAll();
// SELECT * FROM my_users WHERE name like '%zhang%'
$db->from('users')->where(['name', 'like', '%zhang%'])->getAll();
// SELECT * FROM my_users WHERE id != '1'
$db->from('users')->where(['id', '!=', 1])->getAll();
// SELECT * FROM my_users WHERE id != '1' OR id != '2'
$db->from('users')->where(['id', '!=', 1])->where(['OR', 'id', '!=', 2])->getAll();
// SELECT * FROM my_users WHERE id != '1' OR ( id > 0 AND name = 'zhang' OR ( id = '1' OR name like 'zh%' ) AND age != '10' OR name in ( 'zhang', 'wang', 'li' ) ) 
$db->from('users')->where(['id', '!=', 1])->where(['OR', [
    'id > 0',
    ['name' => 'zhang'],
    ['OR', [
        ['id' => 1],
        ['OR', 'name', 'like', 'zh%']
    ]],
    ['age', '!=', 10],
    ['OR', 'name', 'in', ['zhang', 'wang', 'li']]
]])->getAll();


// SELECT * FROM my_users GROUP BY id, age
$db->from('users')->groupBy('id, age')->getAll();
$db->from('users')->groupBy(['id', 'age'])->getAll();


// SELECT * FROM my_users GROUP BY age HAVING 1=1 AND 2=2
$db->from('users')->groupBy('age')->having('1=1 AND 2=2')->getAll();
// SELECT * FROM my_users GROUP BY age HAVING age = '10' 
$db->from('users')->groupBy('age')->having(['age' => '10'])->getAll();


// SELECT * FROM my_users ORDER BY id DESC
$db->from('users')->orderBy('id DESC')->getAll();
$db->from('users')->orderBy(['id DESC'])->getAll();
$db->from('users')->orderBy(['id' => 'DESC'])->getAll();
// SELECT * FROM my_users ORDER BY id DESC, age ASC
$db->from('users')->orderBy('id DESC,age ASC')->getAll();
$db->from('users')->orderBy(['id' => 'DESC', 'age' => 'ASC'])->getAll();
// SELECT * FROM my_users ORDER BY FIELD (id, "1","3","2") ASC
$db->from('users')->orderBy('id', [1, 3, 2])->getAll();
$db->from('users')->orderBy(['id'], [1, 3, 2])->getAll();
// SELECT * FROM my_users ORDER BY id REGEXP '^[a-z]' ASC
$db->from('users')->orderBy('id', "^[a-z]")->getAll();
$db->from('users')->orderBy(['id'], "^[a-z]")->getAll();


// SELECT * FROM my_users LIMIT 1
$db->from('users')->limit(1)->getAll();
// SELECT * FROM my_users LIMIT 1, 10
$db->from('users')->limit('1, 10')->getAll();
$db->from('users')->limit(['1', '10'])->getAll();
$db->from('users')->limit(['1' => '10'])->getAll();


$users = $db->from('users')->getAll();
// 输出 [['id' => 1, 'name' => 'user1'], ['id' => 2, 'name' => 'user2']]
$users = $db->map('name')->from('users')->getAll();
// 输出 ['user1' => ['id' => 1, 'name' => 'user1'], 'user2' => ['id' => 2, 'name' => 'user2']]


// 结果集返回数组（默认）
$users = $db->from('users')->asArray()->getAll();
// 结果集返回对象
$users = $db->from('users')->asObject()->getAll();
// 结果集返回Json
$users = $db->from('users')->asJson()->getAll();


$db->from('users')->limit('0,2')->withTotalCount()->getAll();
// 结果输出2条数据
// $db->totalCount 显示总记录数


// 每页显示5条，显示第一页数据
$users = $db->from('users')->paginate(1, 5);
echo $db->totalCount; // 总记录数
echo $db->currentPage; // 当前页数
echo $db->pageLimit; // 每页记录数
echo $db->totalPages; // 总页数


$sub = $db->subQuery($db->getPrefix());


// SELECT id FROM my_users WHERE age = '10'
$sub->from('users')->select('id')->where(['age' => 10])->getAll();


// SELECT * FROM my_users WHERE id in ( (SELECT id FROM my_users WHERE age = '10' ) ) 
$db->from('users')->where(['id', 'in', $sub])->getAll();


$users = $db->query('select * from my_users limit 1');


try {
    // 开启事务
    $db->startTransaction();

    // 插入一条数据
    $id = $db->table('users')->insert(['name' => 'user', 'age' => 10]);
    if ($id <= 0) {
        // 失败，报错
        throw new \Exception('ERROR:'.$db->getLastErrno().' '.$db->getLastError());
    }

    // 提交
    $db->commit();
} catch(\Exception $e) {
    // 获取错误消息
    // $e->getMessage();
    // 回滚
    $db->rollback();
}


$db->setTrace(true);
$db->from('users')->getAll();
$db->from('users')->select(['id', 'name'])->getOne();
var_dump($db->trace);
// 打印输出结果
// [
//     0 => [
//         0 => 'SELECT * FROM my_users',
//         1 => 0.020965814590454,
//         2 => 'Zhangzhaowy\Phpmysql\Db->getAll() >>  file "**\controller\Test.php" line #214'
//     ],
//     1 => [
//         0 => 'SELECT  id,name FROM my_users LIMIT 1',
//         1 => 0.0006251335144043,
//         2 => 'Zhangzhaowy\Phpmysql\Db->getOne() >>  file "**\controller\Test.php" line #215'
//     ],
// ]


$db->table($table)->setQueryOption('LOW_PRIORITY')->insert($param);
// INSERT LOW_PRIORITY INTO table ...


$db->table($table)->setQueryOption('FOR UPDATE')->get('users');
// SELECT * FROM my_users FOR UPDATE;


$db->table($table)->setQueryOption(['LOW_PRIORITY', 'IGNORE'])->insert($param);
// INSERT LOW_PRIORITY IGNORE INTO table ...


if ($db->getLastErrno() === 0) {
    echo 'Succesfull';
} else {
    echo 'Failed. Error: '. $db->getLastError();
}


$db->disconnect();


if (!$db->ping()) {
    $db->connect();
}


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


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


composer