cakePHP常用数据操作方法总结.doc

CakePHP常用数据操作方法总结0. 测试相关信息1. 数据表(1)zmkm_testsId int(11) primary key Name char(10) Password char(32)Type tinyint(2)(2) zmkm_relationsId int(11) primary keyTest_id int(11) Content text2. Model:Test,Relation3. Controller:test,relation1. 查询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中limitpage =n/ 还有recursive和callbacks等不常用的参数,略过);II.$type,查询的类型(1) $type = first,find的默认参数,查找符合条件的第一条数据.示例1: 查找数据表中第一条记录$lists = $this-Test-find();SQL:SELECT Test.Id, T, Test.password, Test.type FROM zmkm_tests AS Test WHERE 1 = 1 LIMIT 1结果: $lists = array( Test= array(Id=1 name=37892533 password=2f441e566f1bbb7d1faaf32e85035048 type=3 )示例2:查找一条type=1的记录$lists = $this-Test-find(first,array(conditions=array(Test.type = 1);SQL:SELECT Test.Id, T, Test.password, Test.type FROM zmkm_tests AS Test WHERE Test.type = 1 LIMIT 1结果:$lists = array(Test= array(Id=3 name=37892533 password=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, T, Test.password, 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为第二列的字段示例:查找 10idarray(Test.id Between ? And ? = array(10,20),order = Test.type desc);$lists = $this-Test-find(list,$condition);SQL:SELECT Test.id, T 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) 的记录条数$conconditions = 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 id,/ * (必须 筛选字段)value = 33,/ * (必须 field的值)conditions = array(type 3),fields= id,name,type,/ * (必须 查找字段);$lists = $this-Test-find(neighbors,$condition);SQL:执行两条SQL:Query: SELECT Test.id, T, Test.type FROM zmkm_tests AS Test WHERE type 3 AND Test.id 33 ORDER BY Test.id DESC LIMIT 1Query: SELECT Test.id, T, 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, T, Test.password, 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, T, Test.password, 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的password$password = $this-Test-field(password,array(Test.id=34);SQL:SELECT Test.password FROM zmkm_tests AS Test WHERE Test.id = 34 LIMIT 1结果: string(32) 2f441e566f1bbb7d1faaf32e850350482. 添加数据1. save($data),插入一条记录,返回以model名为key值的$data数组(1) .$data中无数据表的主键对应的字段值,则插入一条新纪录示例:插入一条记录$data = array(name = zhongsou,passowrd = md5(time(),type = rand(0,9);$this-Test-save($data);$lastInsertId = $this-Test-id;/ 插入的一条记录的idSQL:INSERT INTO zmkm_tests (name, password, type) VALUES (zhongsou, 0674ab9bda247f85a5e4aa49ecb73b38, 9)注:如果$data中存在数据表中没有的字段,则插入时忽略(2) .saveAll($data),插入单个模型的多条记录,或者是该模型的关联记录A. 向同一个model插入多条记录,在cakePHP手册中介绍有误,$data数组的格式应该如下:$data = array(0 = array(name = abc , password = b2c),1 = array(name = 123 , password = p2p);或者:$data = array(0 = array(Test =array(name = abc , password = b2c) ),1 = array(Test = array(name = 123 , password = p2p) );SQL:执行多条insert语句B. 插入关联记录,新建关联表M_relation,test_id为外键,关联M_test(id),关联关系为一对一(hasOne),关联关系需要在model中定义,$data数据格式$data = array(Relation = array(test_id = 3020,content = ),Test = array(id = 3020,name = zhongsou,password = md5(time(),type = 1) );$this-Relation-saveAll($data);SQL:显示的事务处理,在MyISAM引擎中同样可用1.START TRANSACTION2.INSERT INTO M_relations (test_id, content) VALUES (111, )3.SELECT LAST_INSERT_ID() AS insertID4.SELECT COUNT(*) AS count FROM zmkm_tests AS Test WHERE Test.id = 1115.INSERT INTO zmkm_tests (name, password, type) VALUES (zhongsou, 7248ba8995187e38f404239f787d252c, 1)7.SELECT LAST_INSERT_ID() AS insertID8.COMMIT(3) .$data中有数据表的主键对应的字段值,则修改该记录的相应字段值$dataTest = array(id = 78,name = zhongsou,password = md5(time(),type = rand(0,9);$this-Test-save($data);$updataId = $this-Test-id;/ 修改的记录的idSQL:执行两条SQL,进行插入和更新的判断SELECT COUNT(*) AS count FROM zmkm_tests AS Test WHERE Test.id = 78 UPDATE zmkm_tests SET name = zhongsou, password = 2816c19a87b69d758799054dc8b4cc2e, type = 9 WHERE zmkm_tests.id = 783. 修改1. updateAll($data,$condition),更改所有符合条件的记录示例:修改name字段$data = array(name = souhu);$condition = array(T = zhongsou);$result = $this-Test-updateAll($data,$condition);SQL:UPDATE zmkm_tests AS Test SET T = souhu WHERE T = zhongsou2. 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 = 894. 删除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 = 106DELETE Test FROM zmkm_tests AS Test WHERE Test.id = 1062. deleteAll($condition),删除多条