emagombe/json-qb

从 JSON 请求生成 SQL 查询

维护者

详细信息

github.com/emagombe/JsonQB

源代码

问题

安装: 23

依赖: 0

建议: 0

安全性: 0

星标: 5

关注者: 2

分支: 0

开放问题: 0

2.2.0 2019-12-30 12:40 UTC

Requires

  • php: ^7.0

MIT 1a54eff16d2588fbd536eaaad1596c45e1fc96fb

  • emagombe <edson.patricio.39.woop@gmail.com>

query builderquery generatorJsonQB

This package is auto-updated.

Last update: 2024-09-21 20:31:01 UTC

README

描述

从 JSON 请求生成 sql 查询

安装

使用 composer

composer require emagombe/json-qb

不使用 composer

克隆 项目或从 https://github.com/emagombe/JsonQB/releases 下载一个版本，并将 autoload.php 文件导入您的项目

require_once 'JsonQB/autoload.php';

数据库设置

use queryBuilder\JsonQB as JQB;

JQB::connect([
	'database' => '',	# Database name
	'host' => '',		# Host name
	'port' => '',		# Connection port
	'username' => '',	# Username
	'password' => '',	# Password
	'charset' => '',	# Charset
]);

生成查询

插入

use queryBuilder\JsonQB as JQB;

$sql = JQB::Insert('user',
	array(
		"value" => array(
			"username" => "JsonQB",
			"password" => "123",
			"email" => "example@example.net"
		)
	)
)->sql;

print_r($sql);

返回值

INSERT INTO user(username, password, email) VALUES ('JsonQB','123','example@example.net');

从 html 表单插入

<form method="POST" action="myfile.php">
	<input type="email" name="value[email]" value="example@example.net">
	<input type="password" name="value[password]" value="123">
</form>

在 myfile.php

$sql = JQB::Insert('user', $_POST)->sql;

print_r($sql);

返回值

INSERT INTO user(email, password) VALUES ('example@example.net', '123');

执行查询

要执行 SQL 查询，您只需从 JQB::Insert 函数的响应中调用 execute 函数

$result = JQB::Insert('user', $_POST)->execute();

if($result->success) { echo "success"; } else { echo "failure" }

print_r($result->sql);		# Returns the executed sql query
print_r($result->id);		# Returns the last insert id
print_r($result->success);	# Returns 1 if success

更新

use queryBuilder\JsonQB as JQB;

$sql = JQB::Update('user', [
	'value' => array(
		'username' => 'example'
	), 
	'where' => array(
		array(
			'columns' => array('user.id' => 1)
		)
	)
])->sql;

print_r($sql);

返回值

UPDATE user SET username = 'example' WHERE id = '1';

执行查询

要执行 SQL 查询，您只需从 JQB::Update 函数的响应中调用 execute 函数

$result = JQB::Update('user', [
	'value' => array(
		'username' => 'example'
	), 
	'where' => array(
		array(
			'columns' => array('user.id' => 1)
		)
	)
])->execute();

if($result->success) { echo "success"; } else { echo "failure" }

print_r($result->sql);		# Returns the executed sql query
print_r($result->success);	# Returns 1 if success

删除

use queryBuilder\JsonQB as JQB;

$sql = JQB::Delete('user', [
	'where' => array(
		array(
			'columns' => array('id' => 1)
		),
		array(
			'column' => 'user.id',
			'between' => array(1, 7)
		)
	)
])->sql;

print_r($sql);

返回值

DELETE FROM user WHERE id = '1' AND user.id BETWEEN 1 AND 7;

执行查询

要执行 SQL 查询，您只需从 JQB::Delete 函数的响应中调用 execute 函数

$result = JQB::Delete('user', [
	'where' => array(
		array(
			'columns' => array('id' => 1)
		),
		array(
			'column' => 'user.id',
			'between' => array(1, 7)
		)
	)
])->execute();

if($result->success) { echo "success"; } else { echo "failure" }

print_r($result->sql);		# Returns the executed sql query
print_r($result->success);	# Returns 1 if success

截断

use queryBuilder\JsonQB as JQB;

$sql = JQB::Truncate('user')->sql;

print_r($sql);

返回值

TRUNCATE user;

执行查询

要执行 SQL 查询，您只需从 JQB::Truncate 函数的响应中调用 execute 函数

$result = JQB::Truncate('user')->execute();

if($result->success) { echo "success"; } else { echo "failure" }

print_r($result->sql);		# Returns the executed sql query
print_r($result->success);	# Returns 1 if success

选择

简单选择

use queryBuilder\JsonQB as JQB;

$sql = JQB::Select(array(
	"columns" => array("user.*", "user_type.*"),
	"from" => array("user", "user_type"),
	"where" => array(
		array(
			"columns" => array(
				"user.id" => "1"
			)
		),
	)
))->sql;

print_r($sql);

返回值

SELECT user.*, user_type.* FROM user, user_type WHERE user.id = '1';

执行查询

要执行 SQL 查询，您只需从 JQB::Truncate 函数的响应中调用 execute 函数

$result = JQB::Select(array(
	"columns" => array("user.*", "user_type.*"),
	"from" => array("user", "user_type"),
	"where" => array(
		array(
			"columns" => array(
				"user.id" => "1"
			)
		),
	)
))->execute();

if($result->success) { echo "success"; } else { echo "failure" }

print_r($result->sql);		# Returns the executed sql query
print_r($result->data);		# Returns array of data result
print_r($result->json);		# Returns data result encoded to json string
print_r($result->object);	# Returns data in object Ex: from $data['id'] to $data->id

选择之间

use queryBuilder\JsonQB as JQB;

$sql = JQB::Select(array(
	"columns" => array("user.*", "user_type.*"), # for all columns use array("*")
	"from" => array("user", "user_type"),
	"where" => array(
		array(
			"columns" => array(
				"user.id" => "1"
			)
		),
		array(
			"column" => "user.id",
			"between" => array(1, 7) # Between 1 and 7
		)
	)
))->sql;

print_r($sql);

返回值

SELECT user.*, user_type.* FROM user, user_type WHERE user.id = '1' AND user.id BETWEEN 1 AND 7

自定义运算符

use queryBuilder\JsonQB as JQB;

$sql = JQB::Select(array(
	"columns" => array("user.*", "user_type.*"), # for all columns use array("*")
	"from" => array("user", "user_type"),
	"where" => array(
		array(
			"operator" => "like", # It may be =, !=, <>, >= or <=
			"columns" => array(
				"user.id" => "1"
			)
		),
	)
))->sql;

print_r($sql);

返回值

SELECT user.*, user_type.* FROM user, user_type WHERE user.id = '1';

ORDER BY

use queryBuilder\JsonQB as JQB;

$sql = JQB::Select(array(
	"columns" => array("*"),
	"from" => array("user", "user_type"),
	"where" => array(
		array(
			"operator" => "like", # It may be =, !=, <>, >= or <=
			"columns" => array(
				"user.id" => "1"
			)
		),
	),
	"order" => array("by" => "user.id", "order" => "asc"),
))->sql;

print_r($sql);

返回值

SELECT * FROM user, user_type WHERE user.id like '1' ORDER BY user.id asc

ORDER BY

use queryBuilder\JsonQB as JQB;

$sql = JQB::Select(array(
	"columns" => array("*"),
	"from" => array("user", "user_type"),
	"where" => array(
		array(
			"operator" => "like", # It may be =, !=, <>, >= or <=
			"columns" => array(
				"user.id" => "1"
			)
		),
	),
	"group" => array('by' => 'user.id'),
))->sql;

print_r($sql);

返回值

SELECT * FROM user, user_type WHERE user.id like '1' GROUP BY user.id

连接

use queryBuilder\JsonQB as JQB;

$sql = JQB::Select(array(
	"columns" => array("*"),
	"from" => array("user", "user_type"),
	'join' => array(
		'LEFT' => array(
			'table' => 'bank',
			'on' => array(
				array(
					'columns' => array(
						'bank.user_created' => 'user.id'
					),
				),
			),
		),
		'INNER' => array(
			'table' => 'cash',
			'on' => array(
				array(
					'operator' => 'like',
					'columns' => array(
						'cash.user_created' => 'user.id'
					),
				)
			),
		),
	),
	"where" => array(
		array(
			"operator" => "=", # It may be !=, like, <>, >= or <=
			"columns" => array(
				"user.id" => "1"
			)
		),
	)
))->sql;

print_r($sql);

返回值

SELECT * FROM user, user_type INNER JOIN `cash` ON cash.user_created like user.id LEFT JOIN `bank` ON bank.user_created = user.id WHERE user.id = '1'

IN

use queryBuilder\JsonQB as JQB;

$sql = JQB::Select(array(
	"columns" => array("*"),
	"from" => array("user", "user_type"),
	"where" => array(
		array(
			"operator" => "like", # It may be =, !=, <>, >= or <=
			"columns" => array(
				"user.id" => "1"
			)
		),
		array(
			'column' => 'user.id',
			'in' => JQB::Select(array(
				'columns' => ['user.id'],
				'from' => ['user']
			))->sql,
		)
	),
	"group" => array('by' => 'user.id'),
))->sql;

print_r($sql);

返回值

SELECT * FROM user, user_type WHERE user.id like '1' AND user.id IN (SELECT user.id FROM user) GROUP BY user.id

事务

JsonQB 也支持事务

use queryBuilder\JsonQB as JQB;

JQB::connect([
	'database' => '',	# Database name
	'host' => '',		# Host name
	'port' => '',		# Connection port
	'username' => '',	# Username
	'password' => '',	# Password
	'charset' => '',	# Charset
]);

JQB::begin();		# Creates a new transaction

/* Execunting query */
$result = JQB::Insert('user', $_POST)->execute();

JQB::commit();		# Commits the created transaction

if($result->success) { echo "success"; } else { echo "failure" }

回滚

要回滚事务，您需要调用 JQB::rollback() 函数

JQB::begin();

/* Execunting query */
$result = JQB::Insert('user', $_POST)->execute();

JQB::rollback();

if($result->success) { echo "success"; } else { echo "failure" }