音乐外链生成器:zf zend_DB笔记

set_include_path('D:\apache\htdocs\zend\\');//zend 路径

require_once 'zend/db.php';
//数据库参数配置
$params = array(
'host'=>'127.0.0.1',
'username'=>'root',
'password'=>'root',
'dbname'=>'test1'
);
$db = Zend_Db::factory('pdo_mysql',$params);
//if($db){
// echo 'ok';
//}

//=========================================================================================================
$tbl = 'tbl1';
$select = $db->select();
$select->from('tbl1','*');
$where = $db->quoteInto("age<(?)",30);
$select->where($where);
$select->where($db->quoteInto('age in (?)',array('23','25')));
$rows = $db->fetchAll($select);
foreach ($rows as $key=>$val){
echo 'name:'.$rows[$key][name].'   age:'.$rows[$key][age];
echo '
';
}
$value = $db->quote('my name is "tom"');
echo $value;
$value = $db->quote(array('a','b','c'));
require 'zend/debug.php';
zend_debug::dump($rows,'zend_dubut::dump :
',1);
//echo $where;
$sql = $db->query('select * from tbl1 where age>(?)','30');
$rows = $sql->fetchall();
zend_debug::dump($rows);

//使用占位符

$sql = $db->query('select * from tbl1 where age>:zw and age<:zw2 ',array('zw'=>23,'zw2'=>40));
$rows = $sql->fetchall();
zend_debug::dump($rows);

//pdostatement 方法

$stm = $db->prepare('select * from tbl1 where age>:zw');
$stm->bindvalue('zw','23');
$stm->execute();
$rows = $stm->fetchall();
zend_debug::dump($rows);

echo "

try {
$db->query("select * from tbl1 where age>234");
$db->commit();
}
catch (Exception $e) {
$db->rollBack();
echo $e->getMessage();
}

//插入数据
$rand = rand(59,100);
$rows = array('name'=>'liming','age'=>$rand);
$tblname = 'tbl1';
$db->insert($tblname,$rows);
$lastinsertid = $db->lastInsertId();
if ($db)
echo $lastinsertid;
echo "

//更新数据
$tblname ='tbl1';
$set = array('age'=>222);
$where = $db->quoteInto('name=?','liming');
$db->update($tblname,$set,$where);

//删除数据
$where = $db->quoteInto('name=?','liming');
$db->delete($tblname,$where);

//几种不同取得结果方法
$sql = $db->quoteInto('select * from tbl1 where age>?',23);
$rows = $db->fetchAll($sql);
zend_debug::dump($rows,'fecthall方法（取回所有结果集以连续数组显示）：',1);//取回所有结果集以连续数组显示
$rows = $db->fetchAssoc($sql);
zend_debug::dump($rows,'fetchassoc方法（取回所有结果集以关联数组显示）：',1);
$rows = $db->fetchCol($sql);//取回所有0
zend_debug::dump($rows,'fetchcol方法（取回所有结果行的第一个字段值）:',1);
$rows = $db->fetchOne($sql);
zend_debug::dump($rows,'fetchone方法（只取回第一个字段值）：',1);
$rows = $db->fetchRow($sql);
zend_debug::dump($rows,'fetchrow方法（取回结果集第一行）',1);
$rows = $db->fetchPairs($sql);
zend_debug::dump($rows,'fetchpairs方法（取回一对结果集 第一个一）');

//================================================================================================================
//select_db方法

$select = $db->select();
$tblname = 'tbl1';
$select->from($tblname,'*')
->where('age>?',20)
// ->where('name=?','csad')
->order('age desc')
->limit(10,0);
$rows = $db->fetchAll($select);
//也可以试用——tostring方法
$sql = $select->__tostring($select);
echo $sql;
$rows = $db->fetchAll($sql);
zend_debug::dump($rows,'select_db方法(__tostring())：',1);

//fetchall读取结果使用绑定的参数

$select = $db->select();
$select->from($tblname,'*')
->where('age>:zw')
->order('age asc');
$params = array('zw'=>'20');
$rows = $db->fetchAll($select,$params);
zend_debug::dump($rows,'select_db方法(参数绑定)：',1);

//查询多列数据（不同字段,可加别名）

$select = $db->select();
$select->from('tbl1 as t',array('t.name as n','t.age as a'));
echo $select;
$rows = $db->fetchAll($select);
zend_debug::dump($rows,'查询不同字段下数据
',1);

//查询多列数据（不同字段,可加别名）
$select = $db->select();
$select->from('tbl1',array("name","age"))
->where('age=?','25')
->orwhere("age in (?)",array('34','25'))//or
->order('age desc')
->limit(0,2);
echo $select;
$rows = $db->fetchAll($select);
zend_debug::dump($rows,'查询不同字段下数据
',1);

//多表联合查询
$select = $db->select();
$select->from('tbl1','*')
->where('tbl1.age>=?','23')
->join('tbl2','tbl1.age=tbl2.age','*');
echo $select;
$rows = $db->fetchAll($select);
zend_debug::dump($rows,'多表联合查询(JOIN)：
'.$select.'
',1);

//group 分组查询
$select = $db->select();
$select->from('tbl1','*,count(id)')
->group('name')
->having("age>=25");
$rows = $db->fetchAll($select);
zend_debug::dump($rows,'
group分组查询：'.$select,1);

//order by 排序
$select = $db->select();
$select->from('tbl1','*')
->order('id asc,age');
// ->order(array('age desc','id'));
$rows = $db->fetchAll($select);
zend_debug::dump($rows,'order by 排序查询(首先按ID降序后按age升序)：',1);

//偏移量查询
$select = $db->select();
$select ->from ('tbl1','*')
->limit(10,20);//10为每页数据量 20为从第几条开始查
$rows = $db->fetchAll($select);
zend_debug::dump($rows,'limit偏移量查询：'.$select,1);

//按页数查询
$select = $db->select();
$select->from('tbl1')
->limitpage(2,10);//
$rows = $db->fetchAll($select);
zend_debug::dump($rows,'limitpage按页数查询：'.$select,1);