cakePHP常用数据操作方法总结_基本操作总结

cakePHP常用数据操作方法总结由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“基本操作总结”。

CakePHP常用数据操作方法总结

0.测试相关信息

1.数据表(1)zmkm_tests Id int(11)primary key Name char(10)Paword char(32)Type tinyint(2)(2)zmkm_relations Id int(11)primary key Test_id int(11)Content text 2.Model:Test,Relation 3.Controller:test,relation 一.查询

1.find($type,$params)I.$params,查询的限制条件,格式如下

$params = array('conditions' => array('SQL中的where条件'), 'fields' => array('查询字段'), 'order' => array('排序字段,SQL中的order by 条件'), 'group' => array('分组字段,SQL中的group by 条件'), 'limit' => 'm,n',// 查询记录条数,SQL中limit 'page' =>'n' // 还有recursive和callbacks等不常用的参数,略过);

II.$type,查询的类型

(1)$type = 'first',find的默认参数,查找符合条件的第一条数据.示例1: 查找数据表中第一条记录

$lists = $this->Test->find();SQL:SELECT `Test`.`Id`, `Test`.`name`, `Test`.`paword`, `Test`.`type` FROM `zmkm_tests` AS `Test`

WHERE 1 = 1

LIMIT 1

结果: $lists = array（[“Test”]=>

array(“Id”=>“1”

“name”=>“37892533”

“paword”=>“2f441e566f1bbb7d1faaf32e85035048”

“type”=>“3”))示例2:查找一条type=1的记录

$lists = $this->Test->find('first',array('conditions'=>array('Test.type' => '1')));SQL:SELECT `Test`.`Id`, `Test`.`name`, `Test`.`paword`, `Test`.`type` FROM `zmkm_tests` AS `Test`

WHERE `Test`.`type` = 1

LIMIT 1

结果:$lists = array(“Test”=>

array(“Id”=>“3”

“name”=>“37892533”

“paword”=>“5997124ee980322f8a62a758dda4deee”

“type”=>“1”))

(2)$type = 'all',查找所有符合条件的记录 示例:查找所有type为[1,2,3]的记录

$lists = $this->Test->find('all',array('conditions' => array('Test.type' => array('1','2','3'))));SQL:SELECT `Test`.`Id`, `Test`.`name`, `Test`.`paword`, `Test`.`type` FROM `zmkm_tests` AS `Test`

WHERE `Test`.`type` IN(1, 2, 3)

结果: $lists = array(0 => array('Test'=>array('id'=>1,'name'=>.....)), 1=>array(.....)...);

(3)$type = 'list',返回一个索引数组,key第一列字段,若不指定fields,则value为第二列的字段

示例:查找 10

$condition = array('conditions'=>array('Test.id Between ? And ? ' => array(10,20)), 'order' => 'Test.type desc');$lists = $this->Test->find('list',$condition);SQL:SELECT `Test`.`id`, `Test`.`name` FROM `zmkm_tests` AS `Test`

WHERE `Test`.`id` Between 10 And 20

ORDER BY `Test`.`type` DESC 结果: $lists = array('12' => '47192536' , '10'=>'68192536' ,.....);

(4)$type = 'count' , 计数,返回一个整数 示例:查找type!=(1,2)的记录条数

$con['conditions'] = array('not'=>array('Test.type' =>array(1,2)));$num = $this->Test->find('count',$con);SQL:SELECT COUNT(*)AS `count` FROM `zmkm_tests` AS `Test`

WHERE NOT(`Test`.`type` IN(1, 2))

结果: int(14)(5)$type = 'neighbors',查询符合条件的记录的前后两条记录 示例:查找id=33 且满足 type

$condition = array（'field' => 'id',// *(必须 筛选字段)

'value' => '33', // *(必须 field的值)

'conditions' => array('type '3'),'fields'=> 'id,name,type', // *(必须 查找字段));

$lists = $this->Test->find('neighbors',$condition);SQL:执行两条SQL: Query: SELECT `Test`.`id`, `Test`.`name`, `Test`.`type` FROM `zmkm_tests` AS `Test`

WHERE `type`

ORDER BY `Test`.`id` DESC LIMIT 1 Query: SELECT `Test`.`id`, `Test`.`name`, `Test`.`type` FROM `zmkm_tests` AS `Test`

WHERE `type` '33'

ORDER BY `Test`.`id` ASC LIMIT 1 结果: array(2){

“prev”=> array（“Test”=>rray（“id”=>“32”,“name”=>“44392536”,“type”=>“2”))

“next”=> array（“Test”=>array（“id”=>“39”,“name”=>“14292536”,“type”=>“1”)))

2.findByField($params):通过字段查找记录,只返回一条记录,函数名需要用驼峰式书写

示例1:通过数据表中的id查找

$lists = $this->Test->findById('3');SQL:SELECT `Test`.`Id`, `Test`.`name`, `Test`.`paword`, `Test`.`type` FROM `zmkm_tests` AS `Test`

WHERE `Test`.`id` ='3' LIMIT 1 结果:略

示例2:通过数据表中的type查找

$lists = $this->Test->findByType(array('3','5','7'));

SQL:SELECT `Test`.`Id`, `Test`.`name`, `Test`.`paword`, `Test`.`type` FROM `zmkm_tests` AS `Test` WHERE `Test`.`type` IN('3', '5', '7')LIMIT 1 结果:略

注:对于数据表中类似“user_status”的字段,方法名为findByUserStatus()3.Field($field,$condition),查找一个字段,函数返回字段值

示例:查找id=34的paword $paword = $this->Test->field('paword',array('Test.id'=>'34'));SQL:SELECT `Test`.`paword` FROM `zmkm_tests` AS `Test` WHERE `Test`.`id` = '34' LIMIT 1 结果: string(32)“2f441e566f1bbb7d1faaf32e85035048”

二.添加数据

1.save($data),插入一条记录,返回以model名为key值的$data数组(1).$data中无数据表的主键对应的字段值,则插入一条新纪录 示例:插入一条记录

$data = array（'name' => 'zhongsou','paowrd' => md5(time()),'type' => rand(0,9));$this->Test->save($data);$lastInsertId = $this->Test->id;

// 插入的一条记录的id SQL: INSERT INTO `zmkm_tests`(`name`, `paword`, `type`)VALUES('zhongsou', '0674ab9bda247f85a5e4aa49ecb73b38', 9)

注:如果$data中存在数据表中没有的字段,则插入时忽略(2).saveAll($data),插入单个模型的多条记录,或者是该模型的关联记录 A.向同一个model插入多条记录,在cakePHP手册中介绍有误,$data数组的格式应该如下: $data = array('0' => array('name' => 'abc' , 'paword' => 'b2c'), '1' => array('name' => '123' , 'paword' => 'p2p'));或者: $data = array('0' => array('Test' =>array('name' => 'abc' , 'paword' => 'b2c')), '1' => array('Test' => array('name' => '123' , 'paword' => 'p2p')));SQL:执行多条insert语句

B.插入关联记录,新建关联表M_relation,test_id为外键,关联M_test(id),关联关系为一对一(hasOne),关联关系需要在model中定义,$data数据格式 $data = array('Relation' => array（'test_id' => 3020,'content' => 'www.daodoc.comMIT

(3).$data中有数据表的主键对应的字段值,则修改该记录的相应字段值

$data['Test'] = array（'id' => '78','name' => 'zhongsou','paword' => md5(time()),'type' => rand(0,9));$this->Test->save($data);$updataId = $this->Test->id;

// 修改的记录的id SQL:执行两条SQL,进行插入和更新的判断

SELECT COUNT(*)AS `count` FROM `zmkm_tests` AS `Test` WHERE `Test`.`id` = '78' UPDATE `zmkm_tests` SET `name` = 'zhongsou', `paword` = '2816c19a87b69d758799054dc8b4cc2e', `type` = 9 WHERE `zmkm_tests`.`id` = '78'

三.修改

1.updateAll($data,$condition),更改所有符合条件的记录 示例:修改name字段

$data = array('name' => “'souhu'”);$condition = array('Test.name' => 'zhongsou');$result = $this->Test->updateAll($data,$condition);SQL:UPDATE `zmkm_tests` AS `Test` SET `Test`.`name` = 'souhu' WHERE 'Test'.`name` = 'zhongsou'

2.saveField('field',$value),修改一个字段值,需要先指定id 示例:修改id=89 的name $this->Test->id = 89;$this->Test->saveField('name','other');SQL:UPDATE `zmkm_tests` SET `name` = 'other' WHERE `zmkm_tests`.`id` = 89

四.删除

1.delete(),删除一条记录,需要先指定id,返回boolean 示例:删除id=105的记录

$this->Test->id = '106';$result = $this->Test->delete();SQL: SELECT COUNT(*)AS `count` FROM `zmkm_tests` AS `Test` WHERE `Test`.`id` = '106' DELETE `Test` FROM `zmkm_tests` AS `Test` WHERE `Test`.`id` = '106' 2.deleteAll($condition),删除多条记录,$condition为删除条件 示例:删除id>100 & type!= [1,2,3] 的记录

$condition = array('id >' => 100, 'not' => array('type'=>array('1','2','3')));$this->Test->deleteAll($condition);SQL: 分成两条SQL执行的SELECT `Test`.`id` FROM `zmkm_tests` AS `Test`

WHERE `id` > 100 AND NOT(type IN(1, 2, 3))DELETE `Test` FROM `zmkm_tests` AS `Test`

WHERE `Test`.`id` IN('102', '103', '104')

附:若把cakePHP的debug设为2依然无法在页面上打印SQL语句,则可以通过调用如下方法: /**

* 获取SQL执行的日志

* return array */

function printSQL(){ $sources = ConnectionManager::sourceList();$logs = array();foreach($sources as $source){

$db =& ConnectionManager::getDataSource($source);

if(!$db->isInterfaceSupported('getLog'))continue;

$logs[$source] = $db->getLog();} return $logs;}