原生操作
$sql = "select * from b2b2c_shop where mobile=18812101090";
$a= $this->getReadConnection()->fetchAll($sql);
$sql = 'update ' . $this->getSource() . ' set field_status = 1 where shop_id = ' . $_SESSION['shop_id'] .' and field_id = '.$v;
$this->getReadConnection()->execute($sql);
#原生
// 使用原始SQL语句插入数据
$sql = 'INSERT INTO `robots`(`name`, `year`) VALUES ('Astro Boy', 1952)';
$success = $connection->execute($sql);
// 占位符
$sql = 'INSERT INTO `robots`(`name`, `year`) VALUES (?, ?)';
$success = $connection->execute(
$sql,
[
'Astro Boy',
1952,
]
);
// 动态生成必要的SQL
$success = $connection->insert(
'robots',
[
'Astro Boy',
1952,
],
[
'name',
'year',
],
);
// 动态生成必要的SQL(另一种语法)
$success = $connection->insertAsDict(
'robots',
[
'name' => 'Astro Boy',
'year' => 1952,
]
);
// 使用原始SQL语句更新数据
$sql = 'UPDATE `robots` SET `name` = 'Astro boy' WHERE `id` = 101';
$success = $connection->execute($sql);
// 占位符
$sql = 'UPDATE `robots` SET `name` = ? WHERE `id` = ?';
$success = $connection->execute(
$sql,
[
'Astro Boy',
101,
]
);
// 动态生成必要的SQL
$success = $connection->update(
'robots',
[
'name',
],
[
'New Astro Boy',
],
'id = 101' // 警告！在这种情况下，值不会被转义
);
// 动态生成必要的SQL(另一种语法)
$success = $connection->updateAsDict(
'robots',
[
'name' => 'New Astro Boy',
],
'id = 101' // 警告！在这种情况下，值不会被转义
);
// 转义条件
$success = $connection->update(
'robots',
[
'name',
],
[
'New Astro Boy',
],
[
'conditions' => 'id = ?',
'bind' => [101],
'bindTypes' => [PDO::PARAM_INT], // 可选参数
]
);
$success = $connection->updateAsDict(
'robots',
[
'name' => 'New Astro Boy',
],
[
'conditions' => 'id = ?',
'bind' => [101],
'bindTypes' => [PDO::PARAM_INT], // Optional parameter
]
);
// 使用原始SQL语句删除数据
$sql = 'DELETE `robots` WHERE `id` = 101';
$success = $connection->execute($sql);
// 占位符
$sql = 'DELETE `robots` WHERE `id` = ?';
$success = $connection->execute($sql, [101]);
// 动态生成必要的SQL
$success = $connection->delete(
'robots',
'id = ?',
[
101,
]
);
查找行
$sql = 'SELECT id, name FROM robots ORDER BY name';
// 将SQL语句发送到数据库系统
$result = $connection->query($sql);
// 打印每个 robot name
while ($robot = $result->fetch()) {
echo $robot['name'];
}
// 获取数组中的所有行
$robots = $connection->fetchAll($sql);
foreach ($robots as $robot) {
echo $robot['name'];
}
// 只获得第一行
$robot = $connection->fetchOne($sql);
设置返回类型
常量
描述
Phalcon\Db::FETCH_NUM
返回带有数字索引的数组
Phalcon\Db::FETCH_ASSOC
返回带关联索引的数组
Phalcon\Db::FETCH_BOTH
返回包含关联索引和数字索引的数组
Phalcon\Db::FETCH_OBJ
返回一个对象而不是一个数组
$sql = "select id,name from robots order ny name";
$result = $connection->query($sql);
$result->setFetchMode(Phalcon\Db::FETCH_NUM);
while($robot = $result->fetch()){
echo $robot[0]
}
绑定参数
// 使用数字占位符绑定
$sql = 'SELECT * FROM robots WHERE name = ? ORDER BY name';
$result = $connection->query(
$sql,
[
'Wall-E',
]
);
// 与命名占位符绑定
$sql = 'INSERT INTO `robots`(name`, year) VALUES (:name, :year)';
$success = $connection->query(
$sql,
[
'name' => 'Astro Boy',
'year' => 1952,
]
);
#类型限制
use Phalcon\Db\Column;
// ...
$phql = "SELECT * FROM Store\Robots LIMIT :number:";
$robots = $this->modelsManager->executeQuery(
$phql,
['number' => 10],
Column::BIND_PARAM_INT
);
类型占位符
$phql = "SELECT * FROM Store\Robots WHERE id > :id:";
$robots = $this->modelsManager->executeQuery($phql, ['id' => 100]);
接受请求数据
#控制器中接收数据
#在浏览器中访问 http://localhost/index/test1?a=1&b=2
$this->request->get() 方法能同时获取 GET 和 POST 请求的数据；
$this->request->getQuery() 只能获取 GET 方式的请求数据；
$this->request->getPost() 只能获取 POST 方式的请求数据。
#在Phalcon的路由匹配规则中，我们可以通过 $dispatcher来接收数据：
#但是需要注意的是这个需要在路由配置中进行设置
public function test3Action(){
$a = $this->dispatcher->getParam('a');
$b = $this->dispatcher->getParam('b');
var_dump($a);
var_dump($b);
}
#路由规则如下( app/config/routes.php)：
'/index/test3/(\d+)/(\d+)' => array(
'module' => 'frontend',
'controller'=>'index',
'action'=>'test3',
'a' => 1,
'b' => 2,
),
响应数据格式
#返回json格式数据
public function test6Action(){
return $this->response->setJsonContent(array(
'code' => 1,
'message' => 'success',
));
}
页面跳转
#redirect(),仔细观察会发现浏览器中的URL地址已经发生了变化。
public function test4Action(){
return $this->response->redirect('https://www.marser.cn');
}
#forward(),此种方式的页面跳转不会改变URL地址，只是将请求转发到另一个控制器的action。
public function test5Action(){
return $this->dispatcher->forward(array(
'controller' => 'test',
'action' => 'index',
));
}
调用DI中注册的服务
#DI中注册的所有服务，在控制器中都可以直接调用：
public function test7Action(){
var_dump($this->session);
var_dump($this->cookies);
var_dump($this->request);
var_dump($this->response);
var_dump($this->db);
var_dump($this->logger);
//...
}
#我们可以在这里发散一下，在DI中注册我们的全局配置对象：
$di -> setShared('config', function() use($config){
return $config;
});
#在控制器中直接调用( $this->config)即可。
跟踪sql
$di->setShared('db', function () use ($config, $di) {
$connection = new Phalcon\Db\Adapter\Pdo\Mysql($config->toArray()["db"]);
$profiler = $di['profiler'];
if( $profiler ){
$eventsManager = new EventsManager();
$eventsManager->attach('db', function($event, $connection) use ($profiler) {
if ($event->getType() == 'beforeQuery') {
//Start a profile with the active connection
$profiler->startProfile($connection->getSQLStatement());
}
if ($event->getType() == 'afterQuery') {
//Stop the active profile
$profiler->stopProfile();
}
});
$connection->setEventsManager($eventsManager);
}
return $connection;
});
$di->setShared("profiler", function () use ($config) {
if (!$config->global->debug){
return false;
}
$profiler = new \Phalcon\Db\Profiler();
return $profiler;
});
function getLastSQLStatement()
{
$profile_init = $di['profiler'];
if(!$profile_init){
return "please set your debug mode true";
}
$profile = $profile_init->getLastProfile();
$db = $di['db'];
return $arr = [
'SQL Statement' => $profile->getSQLStatement(),
'Params' => $db->getSQLVariables(),
'Start Time' => $profile->getInitialTime(),
'Final Time' => $profile->getInitialTime(),
'Total Elapsed Time:' => $profile->getTotalElapsedSeconds(),
];
}
打印当前sql
$profiler = new \Phalcon\Db\Profiler();
// Set the connection profiler
$connection->setProfiler($profiler);
$sql = "SELECT buyer_name, quantity, product_name
FROM buyers LEFT JOIN products ON
buyers.pid=products.id";
// Execute a SQL statement
$connection->query($sql);
// Get the last profile in the profiler
$profile = $profiler->getLastProfile();
echo "SQL Statement: ", $profile->getSQLStatement(), "\n";
echo "Start Time: ", $profile->getInitialTime(), "\n";
echo "Final Time: ", $profile->getFinalTime(), "\n";
echo "Total Elapsed Time: ", $profile->getTotalElapsedSeconds(), "\n";
url