README

简单快速的查询构建器，让你的代码中不再有查询字符串

特性

• PDO 支持
• 简单的数据检索结果
• 易于使用
• 通过 composer 简单安装
• 支持 SQL 事务
• 支持存储过程
• 支持表别名
• 超级 where 方法 :\/

未来特性

• 避免在事务中自动提交
• 支持连接之间的子查询
• 在此处添加你的特性 ;)

安装

composer require sairoko/query-builder "1.*"

或者在 composer.json 文件中添加依赖

{
	...
	"require": {
		"sairoko/query-builder": "1.*"
	},
	...
}

SELECT 示例

组装方法始终返回当前的 SQL 查询字符串

// Set database connection
require './vendor/autoload.php';

$config = array(
	'driver' => 'mysql',
	'host' => '127.0.0.1',
	'port' => '3306',
	'database' => 'DATABASENAME',
	'user' => 'root',
	'password' => 'root'
);

$database = new \Sairoko\QB($config);

// You can use only query builder without connection
$database = new \Sairoko\QB;

// Simple select *
$database->from('table');
// Produce SELECT * FROM table;
$database->table('tablename');
// You can use alias 'table'

// Select fields to fetch
$database->select(['field1', 'field2'])->from('table');
// Produce SELECT field1,field2 FROM table
$database->select('field1');
// Support string as parameter

// Select with where condition
$database->from('table')->where('field2', 'foo');
// Produce SELECT * FROM table WHERE field2 = 'foo';

// Fetch all rows result
$database->from('table')->all();
// Select fields with 'all' method
$database->from('table')->all(['field1', 'field2']);
// Fetch result for SELECT field1,field2 FROM table;

//Fetch first row
$database->from('table')->row();
// Select fields with 'row' method
$database->from('table')->row(['field1','field2']);
// Fetch first row for SELECT field1,field2 FROM table;

// Get SQL string
$query = $database->select(['field1', 'field2'])->from('table')->assemble();
echo $query;

强大的 WHERE

where 方法返回 QB 对象

// Instance
$database = new \Sairoko\QB;

$database->from('table')->where('field1', 10);
// Produce SELECT * FROM table WHERE field1 = 10;
$database->from('table')->where('field1', 'foo');
// Produce SELECT * FROM table WHERE field1 = 'foo';
// Autodetection for numeric values, in string add quotes

$database->from('table')->where('field1 <> ?', 15);
// Use '?' as wildcard

$database->from('table')->whereIn('field1', [1,'foo',3]);
// Produce SELECT * FROM table WHERE field1 IN(1,'foo',3);

$database->from('table')->whereNotIn('field1', [1,2,3]);
// Produce SELECT * FROM table WHERE field1 NOT IN(1,2,3);

$database->from('table')->whereBetween('field1', [10, 100]);
// Produce SELECT * FROM table WHERE field1 BETWEEN 10 AND 100;

$database->from('table')->whereIsNotNull('field1');
// Produce SELECT * FROM table WHERE field1 IS NOT NULL;

$database->from('table')->whereIsNull('field1');
// Produce SELECT * FROM table WHERE field1 IS NULL;

// Recursive mode you can call any times
$database->from('table')->where('field1', 10)->orWhere('field2 < ?', 1);
// Produce SELECT * FROM table WHERE field1 = 10 OR field2 < 1;

// Support grouped where
$database->from('table')->where('field1', 10)->where(function($q) {
	$q->where('field2', 20);
	$q->orWhere('field3 <> ?', 15);
});
// Produce SELECT * FROM table WHERE field1 = 10 AND (field2 = 20 OR field3 <> 15);

/** 
 * Methods availabes with 'or' prefix
 * orWhere
 * orWhereBetween
 * orWhereNotNull
 */

JOIN 示例

// Instance
$database = new \Sairoko\QB;

// Inner Join
$database->from('table1')->innerJoin('table2', 'table1.id = table2.id');
// Use alias
$database->select(['a.id','b.id'])->from(['a' => 'table1'])
->innerJoin(['b' => 'table2'], 'a.id = b.id');
// Produce SELECT a.id,b.id FROM table1 AS a INNER JOIN table2 AS b ON a.id = b.id;

// Left Join
$database->from('table1')->leftJoin('table2', 'table1.id = table2.id');
// Use alias
$database->select(['a.id','b.id'])->from(['a' => 'table1'])
->leftJoin(['b' => 'table2'], 'a.id = b.id');
// Produce SELECT a.id,b.id FROM table1 AS a LEFT JOIN table2 AS b ON a.id = b.id;

// Right Join
$database->from('table1')->rightJoin('table2', 'table1.id = table2.id');
// Use alias
$database->select(['a.id','b.id'])->from(['a' => 'table1'])
->rightJoin(['b' => 'table2'], 'a.id = b.id');
// Produce SELECT a.id,b.id FROM table1 AS a RIGHT JOIN table2 AS b ON a.id = b.id;

// Alias methods
$database->select(['a.id','b.id'])->from(['a' => 'table1'])
->join(['b' => 'table2'], 'a.id = b.id');
// Produce inner join by default

$database->select(['a.id','b.id'])->from(['a' => 'table1'])
->join(['b' => 'table2'], 'a.id = b.id', 'left' || 'right');
// Pass the third parameter for select type join

GROUP BY & ORDER BY 示例

// Instance
$database = new \Sairoko\QB;

$database->from('table')->groupBy('field1');
// Produce SELECT * FROM table GROUP BY field1;

$database->from('table')->orderBy('field', 'ASC');
// Produce SELECT * FROM table ORDER BY field ASC;

LIMIT & OFFSET 示例

// Instance
$database = new \Sairoko\QB;

$database->from('table')->limit(1);
// Produce SELECT * FROM table LIMIT 1;

$database->from('table')->limit(10)->offset(2);
// Produce SELECT * FROM table LIMIT 10 OFFSET 2;

INSERT 示例

// Instance
$database = new \Sairoko\QB($config);

$lastInsertId = $database->insert('tablename', [
	'field1' => 'foo',
	'field2' => 'bar',
	'field3' => 'baz'
]);
// Produce INSERT INTO tablename(field1,field2,field3) VALUES('foo','bar','baz');
// This method return last insert id

// Set table name before insert
$database->table('tablename')->insert([
	'field' => 'foo',
	...
]);
// This option is not supported in batch

// Support insert batch
$database->insertBatch('tablename', [
	[
		'field1' => 'foo',
		'field2' => 'bar'
	],
	[
		'field1' => 'foo2',
		'field2' => 'bar2'
	],
	...
]);
// Produce INSERT INTO tablename(field1,field2) VALUES ('foo','bar'),('foo2','bar2');

// Support update on duplicate key
$database->insertBatch('tablename', [
	[
		'id' => 1,
		'field1' => 'foo',
		'field2' => 'bar'
	],
	[
		'id' => 1,
		'field1' => 'foo2',
		'field2' => 'bar2'
	],
	...
], 'id = id+1');
// Produce INSERT INTO tablename(id,field1,field2) VALUES (1,'foo','bar'),(1,'foo2','bar2') ON DUPLICATE KEY UPDATE id = id+1;

// Get only insert query string
$sql = $database->insertQuery('tablename', [
	'field1' => 100
]);

echo $sql;

$sql = $database->insertBatchQuery('tablename', [
	[
		'field' => 1
	],
	...
]);

echo $sql;

UPDATE 示例

// Instance
$database = new \Sairoko\QB($config);

// Associative array with fields and new values
$dataUpdate = ['field1' => 'foo', ...];

$database->update('tablename', $dataUpdate);
// Produce UPDATE tablename SET field1 = 'foo';

$database->update('tablename', $dataUpdate, 'field2 = 1');
// Produce UPDATE tablename SET field1 = 'foo' WHERE field2 = 1;

$database->where('field2 IN(?)',[1,2,'foo'])->orWhere('field1', 10)->update('tablename', $dataUpdate);
// Produce UPDATE tablename SET field1 = 'foo' WHERE field2 IN(1,2,'foo') OR field1 = 10;
// You can use method where any times

// Set table name before update
$datbase->table('tablename')->update([
	'field1' => 'bar'
]);
// and use where condition
$database->table('tablename')->where('field2', 1)->update([
	'field1' => 'bar',
	...
]);


// Get only update query string
$sql = $database->where('field1', 1)->updateQuery('tablename', $dataUpdate);
echo $sql;

DELETE 示例

// Instance
$database = new \Sairoko\QB($config);

$database->delete('tablename');
// Produce DELETE FROM tablename;

$database->delete('tablename', 'field1 = 1');
// Produce DELETE FROM tablename WHERE field1 = 1;

$database->where('field1', 1)->delete('tablename');
// Use where method

// Set table name before delete
$database->table('tablename')->delete();
// and use where condition
$database->table('tablename')->where('field1', 10)->delete();

// Get only delete query string
$sql = $database->where('field1', 10)->deleteQuery('tablename');
echo $sql;

CALL PROCEDURE 示例

此方法用于完整 PDO 资源

更多信息请访问 PDO 文档

// Instance
$database = new \Sairoko\QB($config);

$status = $database->call('procedure_name', [1, 2]);
// Execute CALL procedure_name(1,2);
// Return true or false

$result = $database->call('sp_test', ['foo', 'bar'], true);
// Fetch rows result

事务 示例

// Instance
$database = new \Sairoko\QB($config);

$database->transaction(function($q){
	$q->table('tablename')->insert(['field1' => 'foo']);
});

获取 PDO 实例

// Instance
$database = new \Sairoko\QB($config);

$pdo = $database->db();
// db method return PDO instance

享受！