mg3lo/mysql-crud

MySql Crud 库

维护者

详情

github.com/markusoft/mysql-crud

源代码

问题

安装次数: 23

依赖: 1

建议者: 0

安全: 0

星标: 1

关注者: 1

分支: 0

开放问题: 0

v1.0.0 2023-03-30 10:59 UTC

MIT fc66681d1115c33c6fdc3459dd2c965f76295e81

  • Mark Angelo Angulo <markangeloangulo.woop@yahoo.com>

This package is auto-updated.

Last update: 2024-09-24 08:17:51 UTC

README

MySql Crud 是一个即插即用的 PHP 库，用于自动构建 MySql 数据库的 CRUD 功能。尽管它与查询构建器和 Eloquent ORM 有相似之处，但更加强大，它并不是要取代它们，而是要与它们互补并协同工作。

简单总结

基本功能

查询构建器

高级获取功能

高级获取相关功能

高级添加功能

实用函数

目录

安装

要安装此项目，您有两个选项

通过 Composer 安装

要使用 Composer 安装，请运行以下命令

composer require mg3lo/mysql-crud

手动安装

要手动安装，请按照以下步骤操作

  1. 从开发工具下载库。
  2. 将下载的文件解压到您的扩展目录。

系统要求

  • PHP 7.0 或更高版本
  • MySQL 5.0 或更高版本

注意：已在以下版本上进行了测试，但可能在旧版本上也能工作。

PHP 用户

  1. 下载示例安装或通过 composer 安装

    composer require mg3lo/mysql-crud

  2. 在 PHP 文件上加载库

    <?php
// Load library installed via composer
require_once './vendor/autoload.php'; 

// Or load library installed manually
require_once './Mg3lo/vendor/autoload.php'; 

use Mg3lo\MySqlCrud;

  3. 连接到您的数据库

    <?php

require_once './vendor/autoload.php'; 

use Mg3lo\MySqlCrud;

// connect to your database
$crud = new MySqlCrud([
  'username' => 'root',
  'password' => '',
  'database' => 'my_database'
]);

// do your magic
$products = $crud->table('products')->get();

  4. 享受！

Codeigniter 用户

  1. 为Codeigniter 3或Codeigniter 4解压示例库或通过 composer 安装

    composer require mg3lo/mysql-crud

  2. 在您的控制器上加载库

    <?php 
  // Load library installed via composer
  require_once FCPATH . 'vendor/autoload.php'; 

  // Or load library installed manually
  require_once APPPATH . 'third_party/Mg3lo/vendor/autoload.php';
	
  use Mg3lo\MySqlCrud;

  3. 连接到您的数据库

    <?php 
  require_once APPPATH . 'third_party/Mg3lo/vendor/autoload.php';
	
  use Mg3lo\MySqlCrud;
	
  class Crud extends CI_Controller {

	public function index()
	{
	  // connect to your mysql database
	  $crud = new MySqlCrud([
		'username' => 'root',
		'password' => '',
		'database' => 'my_database'
	  ]);
	  
	  // do your magic
	  $products = $crud->table('products')->get();
	}
  }

  4. 享受！

Laravel 用户

  1. 根据文件夹结构通过 composer 安装或解压库

    composer require mg3lo/mysql-crud

  2. 在您的路由或控制器上加载库

    // load the library if you did not install it via composer
require_once app_path('Mg3lo/vendor/autoload.php');
use Mg3lo\MySqlCrud;

  3. 连接到您的数据库

    use Mg3lo\MySqlCrud;

// connect to your mysql database
$crud = new MySqlCrud([
  'host' => env(DB_HOST),
  'username' => env(DB_USERNAME),
  'password' => env(DB_PASSWORD),
  'database' => env(DB_DATABASE),
]);

// do your magic
$products = $crud->table('products')->get();

  4. 享受！

其他 PHP 框架

  1. 下载示例安装或通过 composer 安装

    composer require mg3lo/mysql-crud

  2. 加载库

    <?php
// Load library installed via composer
require_once './vendor/autoload.php'; 

// Or load library installed manually
require_once '.Your_Directory/Mg3lo/vendor/autoload.php'; 

use Mg3lo\MySqlCrud;

  3. 连接到您的数据库

    <?php

require_once './vendor/autoload.php'; 

use Mg3lo\MySqlCrud;

// connect to your database
$crud = new MySqlCrud([
  'username' => 'root',
  'password' => '',
  'database' => 'my_database'
]);

// do your magic
$products = $crud->table('products')->get();

  4. 享受！

基本功能

以下是基础知识

表

设置数据库表名

$crud->table('products')
     ->get();

查找

查找特定记录

$crud->table('products')
     ->find(1)
     ->get();

我们还可以使用查找来检索子记录。在这个例子中，我们获取属于类别 1 的产品

$crud->table('categories')
     ->find(1, 'products')
     ->get();

添加

添加记录

$product = [
	'name' => 'iphone',
	'description' => 'cellphone',
	'price' => 999
];

$crud->table('products')
     ->add($product);

添加多个记录

$products = [
  [
	'name' => 'iphone',
	'description' => 'cellphone',
	'price' => 999
  ],
  [
	'name' => 'android',
	'description' => 'cellphone',
	'price' => 499
  ]
];

$crud->table('products')
     ->add($products);

编辑

编辑记录

$product = [
  'id' => 1,
  'name' => 'iphone',
  'description' => 'cellphone',
  'price' => 999
];

$crud->table('products')
     ->edit($product);

编辑多条记录

$products = [
  [
    'id' => 1,
	'name' => 'iphone',
	'description' => 'cellphone',
	'price' => 999
  ],
  [
    'id' => 2,
	'name' => 'android',
	'description' => 'cellphone',
	'price' => 499
  ]
];

$crud->table('products')
     ->add($products);

删除

删除记录

$crud->table('products')
	 ->where('id', 1)
     ->delete();

删除多条记录

$crud->table('products')
     ->delete([1,2,3]);

获取

执行获取查询

$crud->table('products')
	 ->where('price', '<', 1000)
     ->get();

重置

重置查询

$crud->reset();

关闭

关闭数据库连接

$crud->close();

查询构建器

查询构建器

Where

Where子句

$crud->table('products')
	 ->where('price', 999)
     ->get();

您还可以使用Eloquent语法中的where

$crud->table('products')
	 ->where('price', '=', 999)
     ->get();

或者多个where子句

$crud->table('products')
	 ->where([
		['price', '=', 999],
		['status', '=' 'active']
	  ])
     ->get();

Where Raw

Where子句

$crud->table('products')
	 ->where_raw('id = 1')
     ->get();

或者Where

或者where子句

$crud->table('products')
	 ->where('description', 'cellphone')
	 ->or_where('price', '<', 1000)
     ->get();

Where In

Where in子句

$crud->table('products')
	 ->where_in('id', [1,2,3])
     ->get();

或者Where In

或者where in子句

$crud->table('products')
	 ->where_in('id', [1,2,3])
	 ->or_where_in('status', ['deleted','archived']
     ->get();

Where Not In

where not in子句

$crud->table('products')
	 ->where_not_in('id', [1,2,3])
     ->get();

Like

Like子句

$crud->table('products')
	 ->like('name', 'phone')
     ->get();

或者Like

或者like子句

$crud->table('products')
	 ->like('name', 'phone')
	 ->or_like('description', 'phone')
     ->get();

Having

Having子句

$crud->table('products')
	 ->where('price', '<', 1000)
	 ->having('status', 'active')
     ->get();

或者Having

或者having子句

$crud->table('products')
	 ->where('price', '<', 1000)
	 ->having('status', 'active')
	 ->or_having('status', 'archived')
     ->get();

Having In

Having in子句

$crud->table('products')
	 ->where('price', '<', 1000)
	 ->having_in('status', ['archived','deleted'])
     ->get();

或者Having In

或者having in子句

$crud->table('products')
	 ->where('price', '<', 1000)
	 ->having_in('status', ['archived','deleted'])
	 ->or_having_in('description', ['phone','tablet']
     ->get();

Having Not In

Having not in子句

$crud->table('products')
	 ->where('price', '<', 1000)
	 ->having_not_in('status', ['archived','deleted'])
     ->get();

Group By

Group by子句

$crud->table('products')
	 ->where('price', '<', 1000)
	 ->group_by('status')
     ->get();

Order By

Order by子句默认为升序

$crud->table('products')
	 ->order_by('price')
     ->get();

我们可以传递第二个排序参数

$crud->table('products')
	 ->order_by('price', 'desc')
     ->get();

排序

与order by子句相同

$crud->table('products')
	 ->order_by('price', 'desc')
     ->get();

Limit

Limit子句

$crud->table('products')
	 ->limit(100)
     ->get();

我们可以传递第二个参数作为偏移量

$crud->table('products')
	 ->limit(100, 100)
     ->get();

Take

与limit子句相同

$crud->table('products')
	 ->take(100, 100)
     ->get();

Offset

Offset子句

$crud->table('products')
	 ->offset(100)
     ->get();

Skip

与offset子句相同

$crud->table('products')
	 ->skip(100)
     ->get();

高级获取功能

搜索

搜索记录

$crud->table('products')
	 ->search('name', 'phone')
     ->get();

筛选

从URL参数限制、偏移量、排序、顺序或案例筛选结果

$crud->table('products')
	 ->filter()
     ->get();

Raw

使用原始SQL查询

$crud->raw('SELECt * FROM products')
     ->get();

查询

与raw相同

$crud->query('SELECt * FROM products')
     ->get();

筛选Raw

使用URL参数筛选原始查询

$crud->raw('SELECt * FROM products')
	 ->filter_raw()
     ->get();

筛选Query

与筛选raw相同

$crud->query('SELECt * FROM products')
	 ->filter_query()
     ->get();

Switch Case

更改查询记录的案例

$crud->table('products')
	 ->switch_case('pascal')
     ->get();

高级获取相关功能

检索相关记录

With

检索包含相关表的记录

$crud->table('categories')
	 ->with('products')
     ->get();

With Recursive

检索递归记录

$crud->table('categories')
	 ->with('categories')
     ->get();

Where With

进一步查询相关表

$crud->table('categories')
	 ->where_with('products', static function($query){
	    return $query->where('status', 'active')
	 })
     ->get();

Where Pivot

进一步查询交叉表

$crud->table('categories')
	 ->where_pivot('products', static function($query){
	    return $query->where('status', 'active')
	 })
     ->get();

Where With Pivot

进一步查询相关和交叉表

$crud->table('categories')
	 ->where_with_pivot('products', static function($related_query){
	   return $related_query->where('price', '<', 1000);
	 }, static function($pivot_query){
	   return $pivot_query->where('status', 'active);
	 })
     ->get();

With Custom

向记录添加额外信息

$crud->table('users')
	 ->with_custom('products', static function($user){
	   $user['foo'] = 'bar';
	   return user;
	 })
     ->get();

Has

仅检索具有相关记录的记录

$crud->table('categories')
	 ->has('products')
     ->get();

Where Has

进一步查询has表

$crud->table('categories')
	 ->where_has('products', static function($query){
	    return $query->where('price', '<', 1000)
	 })
     ->get();

Has No

仅检索没有相关记录的记录

$crud->table('categories')
	 ->has_no('products')
     ->get();

Where Has No

进一步查询has no表

$crud->table('categories')
	 ->where_has_no('products', static function($query){
	    return $query->where('price', '<', 1000)
	 })
     ->get();

With Count

仅检索相关记录的记录数

$crud->table('categories')
	 ->with_count('products')
     ->get();

Has Count

仅检索具有相关记录的记录数

$crud->table('categories')
	 ->has_count('products')
     ->get();

高级添加功能

Push

检查记录是否存在，如果不存在则更新它

$product = [
  'id' => 1,
  'name' => 'iphone',
  'description' => 'cellphone'
];

$crud->table('products')
	 ->push($product);

Attach

将现有记录附加到相关表

$crud->table('categories')
	 ->attach('products', [1,2]);
	 ->save();

Attach New

将新记录附加到相关表

$product = [
  'name' => 'iphone',
  'description' => 'cellphone'
];

$crud->table('categories')
	 ->attach_new('products', $product);
	 ->save();

Detach

删除与相关记录的关系

$crud->table('categories')
	 ->detach('products', [1,2]);
	 ->save();

Sync

将关系附加到相关记录并删除所有之前的关联

$crud->table('categories')
	 ->sync('products', [1,2]);
	 ->save();

Sync New

将新记录附加到相关记录并删除所有之前的关联

$product = [
  'name' => 'iphone',
  'description' => 'cellphone'
];

$crud->table('categories')
	 ->sync_new('products', $product);
	 ->save();

Save

执行高级添加功能

实用函数

Get Result Count

检索结果计数

$crud->table('products')
	 ->get_result_count();

Get DB Table Case

检索数据库表案例

$crud->get_db_table_case();

Get DB Column Case

检索数据库列案例

$crud->get_db_column_case();

Change Case

将文本从一种案例转换为另一种案例

$crud->change_case('My Text', 'pascal');

Recursive Change Array Case

将数组案例从一种案例转换为另一种案例

$crud->recursive_change_array_case($arr, 'pascal');

Recursive Change Array Keys Case

将数组键案例从一种案例转换为另一种案例

$crud->recursive_change_array_case($assoc, 'pascal');

Flatten Json

将JSON文件展开为单维数组

Validate

内置表单验证

$product = [
  'name' => 'iphone',
  'description' => 'cellphone'
];

$validations = [
  'name' => [
    'rules' => 'required'
  ],
  'description' => [
    'rules' => 'max:255'
  ]
];

$crud->validate($product, $validations);

Get Validation Errors

检索错误

$crud->get_validation_errors();

Built-in Validation

内置验证

$validations = [
  'column' => [
    'rename' => 'New Name',
	'rules' => ['required', 'min:8'],
	'custom' => [
	  'pci' => static function($val) {
	    return preg_match('/^(?=^.{6,99}$)(?=.*[0-9])(?=.*[A-Z])(?=.*[a-z])(?=.*[!@#$%*-_=+.])(?!.*?(.)\1{1,})^.*$/', $val);
	  }
	],
	'messages' => [
	  'pci' => static function($val){ return "{$val} should be pci"; },
	  'required' => static function(){ return 'This field is required'; },
	  'min' => static function(){ return 'Minimum is 6 characters'; }
	]
  ]
]

Rules

配置请确保您的配置正确

Database Table Configuration

您可以选择配置数据库表

$config['database_tables'] =  [
'users' => 
[
  'primary_key' => 'id',
  'accessors' => 
    [
      'birthdate' => static function($value) {
        return date('M d, Y', strtotime($value));
      }
    ],
  'mutators' => 
    [
      'password' => static function($value) {
        return password_hash($value, PASSWORD_BCRYPT, ["cost" => 12]);
      },
    ],
  'fractals' => 
    [
      'add' => static function($record) {
        return [
          'id' => (int) $record['id'],
          'email' => $record['username'],
          'first_name' => $record['first_name'],
          'last_name' => $record['last_name'],
          'gender' => $record['gender'],
          'birthdate' => $record['birthdate'],
          'is_active' => $record['is_active'],
          'date_created' => $record['date_created'],
          'date_updated' => $record['date_created'],
          'avatar' => $record['avatar'],
          'first_time_log_in' => $record['first_time_log_in'],
          'numbers_of_login' => $record['numbers_of_login']
        ];
      },
      'read' => static function($record) {
        return [
          'id' => (int) $record['id'],
          'email' => $record['username'],
          'first_name' => $record['first_name'],
          'last_name' => $record['last_name'],
          'full_name' => $record['first_name'] . ' ' . $record['last_name'],
          'gender' => $record['gender'],
          'birthdate' => $record['birthdate'],
          'is_active' => $record['is_active'],
          'date_created' => $record['date_created'],
          'date_updated' => $record['date_created'],
          'avatar' => $record['avatar'],
          'login' => [
            'first_time_log_in' => $record['first_time_log_in'],
            'numbers_of_login' => $record['numbers_of_login']
          ]
        ];
      }
    ],
  'related_tables' => 
    [
      'students' => [
        'table' => 'users',
        'referencing_key' => 'user_id',
        'referenced_key' => 'id'
      ]
    ],
  'validations' => 
    [
      'add' => [
        'id' => "required|int",
        'email' => "required|email",
      ],
      'edit' => [
        'id' => "required|int|greater_than[0]",
        'email' => "email"
      ]
    ],
  // note that additional validations will only apply if validations is not present
  'additional_validations' =>
    [
      'add' => [
        'id' => "greater:0",
        'email' => "unique:users.email",
      ],
      'edit' => [
        'id' => "greater:0",
        'email' => "email"
      ]
    ]
],

];