README

PDO 数据库类

1. 通过 composer 安装

composer require sujanshresthanet/pdo-wrapper

2. 扩展你的 db 类和对象模型

#Create class MyDb for example and extends from \sujanshresthanet\pdo\Db.
#To use the class
#if you want object model from table mysql you can use like these following class Persons with extends from Crud

require(__DIR__ . './config/database.php');

class MyDb extends \sujanshresthanet\pdo\Db {

    public function __construct() {
        parent::__construct(HOST, DB_NAME, DB_USER, DB_PASS);
    }
}

//create class Persons if we want
class Persons extends sujanshresthanet\pdo\Crud {

    protected $table = 'persons';

    # Primary Key of the table
    protected $pk = 'id';

    public $className = 'MyDb';

}

3. 在项目中引导（示例在 index.php 中）

<?php
require(__DIR__ . '/vendor/autoload.php');
include 'MyDb.php';
$db = new MyDb;

$persons = $db->query("SELECT * FROM persons");
echo "<pre>";print_r($persons);

$person = new Persons();
// Create new person
$person->Firstname = "Josh";
$person->Age = "20";
$person->Sex = "F";
$created = $person->create();

//  Or give the bindings to the constructor
$person = new Persons(array("Firstname" => "Josh", "age" => "20", "sex" => "F"));
$created = $person->create();

4. 日志 - 修改根目录的读写权限

每当数据库类抛出异常时，都会创建或修改日志文件。这些日志存储在日志目录中。这意味着数据库类需要日志文件夹的写权限。如果文件在 web 服务器上，您必须修改根目录的权限，否则您会收到“权限被拒绝”错误。

日志文件是一个简单的纯文本文件，文件名为当前日期（年-月-日）。

示例

以下是一些数据库类基本功能的示例。我包括了一个 SQL 导出，以便您可以轻松测试数据库类功能。

人员表

从表中获取所有内容

<?php
// Fetch whole table
$persons = $db->query("SELECT * FROM persons");

使用绑定获取（防止 SQL 注入）

绑定参数是防止 SQL 注入的最佳方法。该类准备您的 SQL 查询，然后绑定参数。

有三种不同的方式绑定参数。

<?php
// 1. Read friendly method
$db->bind("id","1");
$db->bind("firstname","John");
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id");

// 2. Bind more parameters
$db->bindMore(array("firstname"=>"John","id"=>"1"));
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id"));

// 3. Or just give the parameters to the method
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname",array("firstname"=>"John","id"=>"1"));

有关防止 SQL 注入的更多信息： http://indieteq.com/index/readmore/how-to-prevent-sql-injection-in-php

获取行

此方法总是返回仅一行。

<?php
// Fetch a row
$ages     =  $db->row("SELECT * FROM Persons WHERE  id = :id", array("id"=>"1"));

结果

获取单个值

此方法返回记录的单个值。

<?php
// Fetch one single value
$db->bind("id","3");
$firstname = $db->single("SELECT firstname FROM Persons WHERE id = :id");

结果

获取列

<?php
// Fetch a column
$names    =  $db->column("SELECT Firstname FROM Persons");

结果

删除/更新/插入

当通过查询方法执行删除、更新或插入语句时，将返回受影响的行数。

<?php

// Delete
$delete   =  $db->query("DELETE FROM Persons WHERE Id = :id", array("id"=>"1"));

// Update
$update   =  $db->query("UPDATE Persons SET firstname = :f WHERE Id = :id", array("f"=>"Jan","id"=>"32"));

// Insert
$insert   =  $db->query("INSERT INTO Persons(Firstname,Age) VALUES(:f,:age)", array("f"=>"Tyler","age"=>"20"));

// Do something with the data
if($insert > 0 ) {
  return 'Succesfully created a new person !';
}

方法参数

每个执行查询的方法都有一个可选参数，称为绑定。

行和查询方法有一个第三可选参数，即获取样式。默认获取样式是 PDO::FETCH_ASSOC，它返回关联数组。

这里是一个示例

<?php
  // Fetch style as third parameter
  $person_num =     $db->row("SELECT * FROM Persons WHERE id = :id", array("id"=>"1"), PDO::FETCH_NUM);

  print_r($person_num);
  // Array ( [0] => 1 [1] => Johny [2] => Doe [3] => M [4] => 19 )

有关 PDO 获取样式的更多信息： https://php.ac.cn/manual/en/pdostatement.fetch.php

EasyCRUD

easyCRUD 是一个类，您可以使用它轻松执行基本的 SQL 操作（如插入、更新、选择、删除）来操作数据库。它使用我创建的数据库类来执行 SQL 查询。

实际上，它只是一个小的 ORM 类。

如何使用 easyCRUD

1. 首先，创建一个新的类。然后需要 easyCRUD 类。

2. 将您的类扩展到基类 Crud，并向类中添加以下字段。

示例类

<?php
require_once("easyCRUD.class.php");

class YourClass  Extends Crud {

  # The table you want to perform the database actions on
  protected $table = 'persons';

  # Primary Key of the table
  protected $pk  = 'id';

}

easyCRUD 应用实例。

创建新人员

<?php
// First we"ll have create the instance of the class
$person = new person();

// Create new person
$person->Firstname  = "Josh";
$person->Age        = "20";
$person->Sex        = "F";
$created            = $person->Create();

//  Or give the bindings to the constructor
$person  = new person(array("Firstname"=>"Josh","age"=>"20","sex"=>"F"));
$created = $person->Create();

// SQL Equivalent
"INSERT INTO persons (Firstname,Age,Sex) VALUES ('Josh','20','F')"

删除人员

<?php
// Delete person
$person->Id  = "17";
$deleted     = $person->Delete();

// Shorthand method, give id as parameter
$deleted     = $person->Delete(17);

// SQL Equivalent
"DELETE FROM persons WHERE Id = 17 LIMIT 1"

保存人员数据

<?php
// Update personal data
$person->Firstname = "John";
$person->Age  = "20";
$person->Sex = "F";
$person->Id  = "4";
// Returns affected rows
$saved = $person->Save();

//  Or give the bindings to the constructor
$person = new person(array("Firstname"=>"John","age"=>"20","sex"=>"F","Id"=>"4"));
$saved = $person->Save();

// SQL Equivalent
"UPDATE persons SET Firstname = 'John',Age = 20, Sex = 'F' WHERE Id= 4"

查找人员

<?php
// Find person
$person->Id = "1";
$person->Find();

echo $person->firstname;
// Johny

// Shorthand method, give id as parameter
$person->Find(1);

// SQL Equivalent
"SELECT * FROM persons WHERE Id = 1"

获取所有人员

<?php
// Finding all person
$persons = $person->all();

// SQL Equivalent
"SELECT * FROM persons