Zend framework与数据库操作

查询操作:

返回单个值

/**
* 获取未读邮件数量
* @param $username 用户名
*/

public function getWeiduCount($username)
{
$sql = “SELECT COUNT(*) FROM $this->_name WHERE `display` = ‘1’ AND `uatype` = ‘1’ AND (`username` = ? AND `is_read` = ‘0’) OR (`username` = ‘onall’ AND `id` NOT IN (SELECT bid FROM union_inboxread WHERE `username` = ?) AND `id` NOT IN (SELECT bid FROM union_inboxdisplay WHERE `username` = ?)) “;
$result = $this->_db->fetchOne($sql, array($username,$username,$username));
return $result;

}

说明: fetchOne()方法所返回的是一条数据.
        使用?的方式,有效的防止的SQL的注入.

 

传递某个值来占位时的情况

单个值的传递

public function getValueByKey($keyname)
{
$sql = ‘SELECT `value` FROM ‘ . $this->_name . ‘ WHERE `key` = ?’;
$result = $this->_db->fetchOne($sql, $keyname);
return unserialize($result);
}

基于select对象来返回的数组【连接查询】

连接查询

/**
* 单个网吧详细信息
*/
public function allinfoNetbar($id)
{
//$sql = “SELECT * FROM gdb_netbars as a left join gdb_members as b on a.managerid = b.id WHERE a.id = ?”;
$select = $this->_db->select()
->from( array(‘a’ => $this->_name ))
->join( array(‘b’ => ‘gdb_members’), ‘a.managerid = b.id’ )
->join( ‘area’, ‘a.area = area.areaID’ )
->join( ‘city’, ‘a.city = city.cityID’ )
->join( ‘province’, ‘a.province = province.provinceID’ )
->where( ‘a.id = ?’, $id );
return $this->_db->fetchRow($select);
}

有排序,分组,过滤等功能的SELECT对象操作

分组以及排序功能

/**
* 获取自从上次支付的时间
* @param $uid
*/
public function getDateFromLastPay($uid)
{
$select = $this->_db->select()
->from($this->_name, ‘square_date’)
->where(‘member_uid=’ . $uid)
->where(‘status=2’)
->order(‘square_date DESC’)
->limit(1);
return $this->_db->fetchOne($select);
}

并没有单独的模型的数据插入

/**
* 插入数据到union_inboxread表中
* @param $row 插入数据数组
*/
public function addinboxread($row = array())
{
$returns = $this->getAdapter()->insert(‘union_inboxread’,$row);

return $returns;
}
说明:$this->getAdapter为Zend_Db_Table_Abstract类的一个抽象方法,它的源码为:
public function getAdapter()
{
return $this->_db;
}

更新操作:
/**
* 置顶娱乐资讯
*
*/
public function topfun($id, $row, $type){
$where = $this->_db->quoteInto(‘id = ?’, $id. ‘ and type = ?’, $type);
$this->update($row, $where);

}

说明: $row为关联数组

quoteInto()方法应用的目的也是有效的防止SQL的注入.
删除操作
 /**
* 删除
*
*/
public function delfun($id ,$type){
$where = $this->_db->quoteInto(‘id = ?’, $id. ‘ and type = ?’, $type);
$this->delete($where);
}

Leave a Reply

Your email address will not be published. Required fields are marked *