首先composer安装
表结构如下
示例代码:
增删改
浏览器显示
[img]http://dl2.iteye.com/upload/attachment/0125/4790/9605120a-7f3a-3b81-92b6-fab7e0cec30e.png[/img]
{
"require": {
"doctrine/dbal":"2.5.12"
}
}
表结构如下
CREATE TABLE `test_databases` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`db_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '库名',
`user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '测试用户id',
`created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `test_databases_db_name_user_id_unique` (`db_name`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
示例代码:
<?php
require ("../vendor/autoload.php");
$config = new \Doctrine\DBAL\Configuration();
//..
$connectionParams = array(
'dbname' => 'test1',
'user' => 'root',
'password' => 'root',
'host' => 'localhost',
'driver' => 'pdo_mysql',
'charset'=>'UTF8',
);
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);
echo "<h1>fetchAll(获取全部), fetchAssoc(获取行), fetchColumn(获取单个)示例</h1>";
$users = $conn->fetchAll('SELECT * FROM test_databases limit 2');
var_dump($users);
echo "<hr>";
$sql = 'SELECT * FROM test_databases';
$result = $conn->fetchAssoc($sql);
var_dump($result);
echo "<hr>";
$sql = 'SELECT db_name FROM test_databases limit 3';
$result = $conn->fetchColumn($sql);
var_dump($result);
echo "<hr>";
// ,没有单独的取某列的值。好遗憾。
$sql = 'SELECT db_name FROM test_databases order by id asc limit 3';
$result = $conn->fetchAll($sql);
var_dump($result);
echo "<hr>";
echo "<h1>迭代获取全部,适用于数据量超大的场合</h1>";
//迭代获取全部,适用于数据量超大的场合
$stat = $conn->query('SELECT * FROM test_databases limit 2');
while ($row= $stat->fetch()) {
var_dump($row);
}
//var_dump($users);
echo "<hr>";
// 参数绑定
echo "<h1>动态参数绑定,也可以使用另外几个fetch函数</h1>";
$sql = 'SELECT * FROM test_databases WHERE db_name = ? limit 2';
$statement = $conn->executeQuery($sql, array('db1'));
$result = $statement->fetchAll();
var_dump($result);
echo "<hr>";
echo "<h1>查询构造器使用示例,也可以使用另外几个fetch函数</h1>";
// 下面是查询构造器使用示例
$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
->select('id', 'db_name')
->from('test_databases')
->where("db_name = :db_name")
->setParameter('db_name', 'db1');
$sql = $queryBuilder->getSQL();
var_dump($sql);
echo "<hr>";
$result = $queryBuilder->execute()->fetchAll(); //返回 executeQuery对象。
var_dump($result);
增删改
$conn->insert('user', array('username' => 'jwage'));
$conn->lastInsertId();// 得到最后插入的行
$conn->delete('user', array('id' => 1));
$conn->update('user', array('username' => 'jwage'), array('id' => 1));
浏览器显示
[img]http://dl2.iteye.com/upload/attachment/0125/4790/9605120a-7f3a-3b81-92b6-fab7e0cec30e.png[/img]