PHP code example of lite-view / sql

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

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

    

lite-view / sql example snippets

use LiteView\SQL\Config;

Config::set('mysql', [
    "driver"   => "mysql",
    "host"     => "127.0.0.1",
    "port"     => 3306,
    "username" => "root",
    "password" => "password",
    "dbname"   => "database",
    "charset"  => "utf8mb4",
    "prepares" => true
]);

const MYSQL_CONNECTION = [
    'mysql' => [
        "driver"   => "mysql",
        "host"     => "127.0.0.1",
        "port"     => 3306,
        "username" => "root",
        "password" => "password",
        "dbname"   => "database",
        "charset"  => "utf8mb4",
        "prepares" => true
    ]
];

use LiteView\SQL\Crud;

// 插入单条
$id = Crud::db()->insert('users', [
    'name'  => 'John',
    'email' => '[email protected]'
]);

// 插入并忽略重复
$id = Crud::db()->insert('users', ['id' => 1, 'name' => 'John'], true);

// 批量插入
$lastId = Crud::db()->insertAll('users', [
    ['name' => 'Alice', 'email' => '[email protected]'],
    ['name' => 'Bob',   'email' => '[email protected]']
]);

// 查询单条
$user = Crud::db()->select('users', 'id = ?', '*', [1])->one();

// 查询列表
$users = Crud::db()->select('users', 'status = ?', '*', [1])->all();

// 查询列表（带 limit）
$users = Crud::db()->select('users', 'status = ?', '*', [1])->all(10);

// 获取单个值
$count = Crud::db()->select('users', 'status = ?', 'count(*)', [1])->column();

$affected = Crud::db()->update('users', 
    ['name' => 'New Name'], 
    'id = ?', 
    [1]
);

$affected = Crud::db()->delete('users', 'id = ?', [1]);

// 存在则更新，不存在则插入
[$status, $id] = Crud::db()->updateOrInsert('users', 
    ['id' => 1],           // 唯一索引条件
    ['name' => 'Updated']  // 更新的字段
);
// $status: 0=插入, 1=更新

$result = Crud::db()->select('users', 'status = ?', '*', [1])->paginate(15);

// 返回结构
[
    'paging' => [
        'total'       => 100,  // 总条数
        'pageSize'    => 15,   // 每页条数
        'currentPage' => 1,    // 当前页
        'pageCount'   => 7,    // 总页数
    ],
    'list' => [...]  // 数据列表
]

// 指定页码
$result = Crud::db()->select('users', '1')->paginate(15, 'page', 2);

$orders = Crud::db()
    ->select('users', 'users.id = ?', 'users.name, orders.*', [1])
    ->join([
        ['table' => 'orders', 'on' => 'users.id = orders.user_id', 'way' => 'left']
    ])
    ->all();

// 多表 JOIN
$data = Crud::db()
    ->select('orders', 'orders.id = ?', 'orders.*, users.name, products.title', [1])
    ->join([
        ['table' => 'users',    'on' => 'orders.user_id = users.id'],
        ['table' => 'products', 'on' => 'orders.product_id = products.id', 'way' => 'inner'],
    ])
    ->all();

// 排序
$users = Crud::db()->select('users', '1')
    ->order('id', 'desc')
    ->all(10);

// 分组 + HAVING
$stats = Crud::db()->select('users', '1', 'status, count(*) as cnt')
    ->group('status')
    ->having('cnt > 5')
    ->all();

use LiteView\SQL\Connect;

$result = Connect::db()->transaction(function () {
    $db = Crud::db();
    $db->insert('orders', ['user_id' => 1, 'amount' => 100]);
    $db->update('users', ['balance' => 900], 'id = ?', [1]);
    return true;
});

// 执行原生查询
$result = Connect::db()->query('SELECT VERSION()')->fetch();

// 原生预处理
$stmt = Connect::db()->prepare('SELECT * FROM users WHERE id = ?', [1]);
$user = $stmt->fetch();

// 获取原始 SQL（用于调试）
$sql = Crud::db()->select('users', 'id = ? AND status = ?', '*', [1, 1])
    ->getRawStatement(true);

// 返回可执行的 MySQL PREPARE 语句
echo $sql;