README

目标是简化并确保使用PDO和预编译语句执行数据库操作（MySQL）的安全性。

• 应用数据净化和准备来预防XSS和SQL注入等潜在漏洞。
• 采用语义方法。
• 允许以简单的方式处理多个数据库。
• 只需几行代码，您就可以轻松执行“CRUD”操作。轻松注册、读取、更新和删除记录。

安装

通过 composer

composer require hnqca/database-php

示例

<?php

require_once __DIR__ . '/vendor/autoload.php';

use Hnqca\Database\Connection;
use Hnqca\Database\Database;

/**
 * Configures the database connection data:
 */
$connection = new Connection([
    'driver'     => 'mysql',
    'host'       => 'localhost',
    'name'       => 'your_database',
    'user'       => 'root',
    'pass'       => '',
    'port'       => '3306',
    'charset'    => 'utf8'
]);

try {

    /**
     * Initializes the database connection:
     */
    $database = new Database($connection);

    /** 
     * Handles the table in the currently set database during class instantiation:
     */
    $results = $database->from('users')->select(true);

    /**
     * Displays the results found in the table:
     */
    echo '<pre>';
    print_r($results);


    /***
     * Checks if any error occurred during the process:
     */
} catch (\Exception $e) {
    die('error: ' . $e->getMessage()); // Displays the reason for the error.
}

连接

use Hnqca\Database\Connection;
use Hnqca\Database\Database;

/**
 * Configures the database connection data:
 */
$connection = new Connection([
    'driver'     => 'mysql',
    'host'       => 'localhost',
    'name'       => 'your_database',
    'user'       => 'root',
    'pass'       => 'password',
    'port'       => '3306',
    'charset'    => 'utf8'
]);

/**
 * Initializes the database connection:
 */
$database = new Database($connection);

选择

/**
 * Selecting multiple records:
 */
$users = $database->from('users')->select(true);

/**
 * Limiting the columns to be selected:
 */
$users = $database->from('users')->select(true, ['id', 'age']);

/**
 * Limiting the number of records to be selected:
 */
$users = $database->from('users')->limit(30)->select(true);

/**
 * Selecting data from a specific user:
 */
$userId = 24;
$user = $database->from("users")->where("id = {$userId}")->select();

/**
 * Selecting multiple records with one or more conditions:
 */
$name  = "John";
$users = $database->from('users')->where("first_name = {$name}, age >= 18")->select(true);

/**
* You can also use "ORDER BY"
*/
$users = $database->from("users")->where("first_name = {$name}, age >= 18")->orderBy(['age' => 'DESC'])->select(true);


/**
 * Displaying the obtained data:
 */

echo "Total users found: " . count($users);

foreach ($users as $user) {
    echo "Id:   {$user->id}"                            . '<br/>';
    echo "Name: {$user->first_name} {$user->last_name}" . '<br/>';
    echo '<hr />';
    // ...
}

LIMIT, OFFSET 和 "PAGE"（页码）

LIMIT（限制）

$results = $database->from("products")->limit(2)->select(true);

LIMIT 和 OFFSET（限制和偏移量）

通常将 offset 方法与 limit 方法结合使用，以允许对结果进行分页。

$results = $database->from("products")->limit(2)->offset(0)->select(true);

$results = $database->from("products")->limit(2)->offset(2)->select(true);

LIMIT 和 "PAGE"（页码）

page 是一种更简单的方法来实现结果分页。

接受页面大小（每页的记录数）和所需页码。

它将自动计算适当的偏移量。

$result = $database->from("products")->limit(limit: 2, page: 1)->select(true);

$result = $database->from("products")->limit(limit: 2, page: 2)->select(true);

插入

$userId = $database->from('users')->insert([
    'first_name' => "John",
    'last_name'  => "Doe",
    'email'      => "user@example.com",
    'age'        => 32
    // ...
]);

if (!$userId) {
    die ("Unable to register the user in the database.");
}

echo "User registered successfully! ID: {$userId}";

更新

$database->from('users')->where("id = 123")->update([
    'email' => "new.email@example.com"
    // ...
]);

删除

/**
 * Delete specific records:
 */
$database->from('users')->where("id = 123")->delete();

/**
 * Delete all records from the table. (be careful):
 */
$database->from('users')->delete();

聚合

计数（COUNT）

/**
 * Counting the number of records:
 */
$totalUsers = $database->from("users")->count();

求和（SUM）

 /**
  * Summing up some value from the column:
  */
$totalPrice = $database->from("products")->sum("price");

平均值（AVG）

/**
 * To calculate the average of values in a numeric column of a table.
 */
$averageAge = $database->from("users")->avg("age");

最小值（MIN）

/**
 * Finding the lowest value in the column:
 */
$lowestAge = $database->from("users")->min("age");

最大值（MAX）

/**
 * Finding the highest value in the column:
 */
$highestAge = $database->from("users")->max("age");