在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
开源软件名称:cakephp/database开源软件地址:https://github.com/cakephp/database开源编程语言:PHP 100.0%开源软件介绍:A flexible and lightweight Database Library for PHPThis library abstracts and provides help with most aspects of dealing with relational databases such as keeping connections to the server, building queries, preventing SQL injections, inspecting and altering schemas, and with debugging and profiling queries sent to the database. It adopts the API from the native PDO extension in PHP for familiarity, but solves many of the inconsistencies PDO has, while also providing several features that extend PDO's capabilities. A distinguishing factor of this library when compared to similar database connection packages, is that it takes the concept of "data types" to its core. It lets you work with complex PHP objects or structures that can be passed as query conditions or to be inserted in the database. The typing system will intelligently convert the PHP structures when passing them to the database, and convert them back when retrieving. Connecting to the databaseThis library is able to work with the following databases:
The first thing you need to do when using this library is create a connection object. Before performing any operations with the connection, you need to specify a driver to use: use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;
$driver = new Mysql([
'database' => 'test',
'username' => 'root',
'password' => 'secret'
]);
$connection = new Connection([
'driver' => $driver
]); Drivers are classes responsible for actually executing the commands to the database and correctly building the SQL according to the database specific dialect. Drivers can also be specified by passing a class name. In that case, include all the connection details directly in the options array: use Cake\Database\Connection;
$connection = new Connection([
'driver' => Cake\Database\Driver\Sqlite::class,
'database' => '/path/to/file.db'
]); Connection optionsThis is a list of possible options that can be passed when creating a connection:
Using connectionsAfter creating a connection, you can immediately interact with the database. You can choose
either to use the shorthand methods The easiest way of executing queries is by using the $statement = $connection->execute('SELECT * FROM articles');
while($row = $statement->fetch('assoc')) {
echo $row['title'] . PHP_EOL;
} Binding values to parametrized arguments is also possible with the execute function: $statement = $connection->execute('SELECT * FROM articles WHERE id = :id', ['id' => 1], ['id' => 'integer']);
$results = $statement->fetch('assoc'); The third parameter is the types the passed values should be converted to when passed to the database. If no types are passed, all arguments will be interpreted as a string. Alternatively you can construct a statement manually and then fetch rows from it: $statement = $connection->prepare('SELECT * from articles WHERE id != :id');
$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc'); The default types that are understood by this library and can be passed to the
More types can be added dynamically in a bit. Statements can be reused by binding new values to the parameters in the query: $statement = $connection->prepare('SELECT * from articles WHERE id = :id');
$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc');
$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc'); Updating RowsUpdating can be done using the $connection->update('articles', ['title' => 'New title'], ['id' => 1]); The concept of data types is central to this library, so you can use the last parameter of the function to specify what types should be used: $connection->update(
'articles',
['title' => 'New title'],
['created >=' => new DateTime('-3 day'), 'created <' => new DateTime('now')],
['created' => 'datetime']
); The example above will execute the following SQL: UPDATE articles SET title = 'New Title' WHERE created >= '2014-10-10 00:00:00' AND created < '2014-10-13 00:00:00'; More on creating complex where conditions or more complex update queries later. Deleting RowsSimilarly, the $connection->delete('articles', ['created <' => DateTime('now')], ['created' => 'date']); Will generate the following SQL DELETE FROM articles where created < '2014-10-10' Inserting RowsRows can be inserted using the $connection->insert(
'articles',
['title' => 'My Title', 'body' => 'Some paragraph', 'created' => new DateTime()],
['created' => 'datetime']
); More complex updates, deletes and insert queries can be generated using the Query BuilderOne of the goals of this library is to allow the generation of both simple and complex queries with ease. The query builder can be accessed by getting a new instance of a query: $query = $connection->newQuery(); Selecting FieldsAdding fields to the $query->select(['id', 'title', 'body']);
// Results in SELECT id AS pk, title AS aliased_title, body ...
$query->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);
// Use a closure
$query->select(function ($query) {
return ['id', 'title', 'body'];
}); Where ConditionsGenerating conditions: // WHERE id = 1
$query->where(['id' => 1]);
// WHERE id > 2
$query->where(['id >' => 1]); As you can see you can use any operator by placing it with a space after the field name. Adding multiple conditions is easy as well: $query->where(['id >' => 1])->andWhere(['title' => 'My Title']);
// Equivalent to
$query->where(['id >' => 1, 'title' => 'My title']); It is possible to generate $query->where(['OR' => ['id >' => 1, 'title' => 'My title']]); For even more complex conditions you can use closures and expression objects: $query->where(function ($exp) {
return $exp
->eq('author_id', 2)
->eq('published', true)
->notEq('spam', true)
->gt('view_count', 10);
}); Which results in: SELECT * FROM articles
WHERE
author_id = 2
AND published = 1
AND spam != 1
AND view_count > 10 Combining expressions is also possible: $query->where(function ($exp) {
$orConditions = $exp->or(['author_id' => 2])
->eq('author_id', 5);
return $exp
->not($orConditions)
->lte('view_count', 10);
}); That generates: SELECT *
FROM articles
WHERE
NOT (author_id = 2 OR author_id = 5)
AND view_count <= 10 When using the expression objects you can use the following methods to create conditions:
Aggregates and SQL Functions// Results in SELECT COUNT(*) count FROM ...
$query->select(['count' => $query->func()->count('*')]); A number of commonly used functions can be created with the func() method:
When providing arguments for SQL functions, there are two kinds of parameters you can use, literal arguments and bound parameters. Literal parameters allow you to reference columns or other SQL literals. Bound parameters can be used to safely add user data to SQL functions. For example: $concat = $query->func()->concat([
'title' => 'literal',
' NEW'
]);
$query->select(['title' => $concat]); The above generates: SELECT CONCAT(title, :c0) ...; Other SQL ClausesRead of all other SQL clauses that the builder is capable of generating in the official API docs Getting Results out of a QueryOnce you’ve made your query, you’ll want to retrieve rows from it. There are a few ways of doing this: // Iterate the query
foreach ($query as $row) {
// Do stuff.
}
// Get the statement and fetch all results
$results = $query->execute()->fetchAll('assoc'); Official APIYou can read the official official API docs to learn more of what this library has to offer. |
2022-08-15
2022-08-17
2022-09-23
2022-08-18
2023-10-27
请发表评论