ZendFrameset 数据库CURD操作

CurdController.php

<?php

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2016/4/6
 * Time: 15:57
 * 某表格CURD操作
 */
require_once 'DBController.php';
require_once APPLICATION_PATH . '/models/student.php';
require_once APPLICATION_PATH . '/models/course.php';
require_once APPLICATION_PATH . '/models/sc.php';

class CurdController extends DBController
{
    public function indexAction() {
        $studentModel = new student();
        $courseModel = new course();
        $scModel = new sc();
        $db = $courseModel->getAdapter();
        $db2 = $studentModel->getAdapter();
        $db3 = $scModel->getAdapter();

        //检索全部信息字段
        //创建数据库适配器
        $courses = $courseModel->fetchAll()->toArray();
        $this->view->courses = $courses;
        //CURD
        //增加记录
        /*$data = array(
            'id' => '1',
            'name' => 'ZF',
            'credit' => '3'
        );
        $courseModel->insert($data);*/
        //修改记录
/*        $set = array(
            'credit' => 2
        );
        $where = "id = 1";
        $courseModel->update($set, $where);*/
        //删除记录
/*        $where = "id = 1";
        $courseModel->delete($where);*/
        //根据主键查询
        //查询学生号位20040001学生
        $students1 = $studentModel->find(20040001)->toArray();
        $this->view->students1 = $students1;
        //查询多个学生
        $students2 = $studentModel->find(array(20040001,'20040002'))->toArray();
        $this->view->students2 = $students2;
        //取回一条记录
        $students3 = $studentModel->fetchRow("sex = 'M'",'age')->toArray();
        $this->view->students3 = $students3;
        //用Model与Adapter时间差别
        //简单原则：
        //1.若查询部分字段，用Adapter
        //2.若涉及多表查询，用Adapter
        //3.若考虑SQL注入问题 用Adapter $adapter->query()->fetchall();
        //4.若增加、删除、修改、查询某张单表的全部字段(包括分页),用表模型
        //过滤重复值distinct
        //显示所有学生性别、年龄
        $students4 = $db2->query('SELECT DISTINCT dept FROM student')->fetchAll();
        $this->view->students4 = $students4;
        $students5 = $db2->query('SELECT COUNT(DISTINCT dept) c FROM student')->fetchAll();
        $this->view->students5 = $students5;
        //查询各系总人数
        //SELECT COUNT( * ) , dept FROM student GROUP BY dept
        //查询计算机系总人数
        $students6 = $db2->query("SELECT count(*) c, dept FROM student WHERE dept = '计算机系' GROUP BY dept")->fetchAll();
        $this->view->students6 = $students6;
        //查询各科总分
        //SELECT SUM( grade ) s, cid FROM sc GROUP BY cid
        //查询总分
        $sc1 = $db3->query("SELECT sum(grade) s FROM sc");
        $this->view->sc1 = $sc1;
        //查询选修11号课程的最高和最低分
        $sc2 = $db3->query("SELECT max(grade) x, min(grade) n, cid FROM sc WHERE cid=11")->fetchAll();
        $this->view->sc2 = $sc2;
        //显示各科考试不及格学生名字，科目和分数
        $sc3 = $db3->query("SELECT student.name sn, course.name cn, grade 
            FROM student, course, sc 
            WHERE grade < 60 
            AND sc.sid = student.id 
            AND sc.cid = course.id")->fetchAll();
        $this->view->sc3 = $sc3;
        //计算各科不及格的学生数
        //不显示学生数为0的科目
        //SELECT name, COUNT( sid ) c FROM sc, course WHERE grade <60 AND sc.cid = course.id GROUP BY cid
        //显示学生数为0的科目
        $sc4 = $db3->query("
            SELECT name, count(grade) c FROM course 
            LEFT JOIN (
                SELECT cid, grade FROM course, sc 
                WHERE grade<60 AND course.id = sc.cid
            ) t
            ON course.id = t.cid GROUP BY name
        ")->fetchAll();
        $this->view->sc4 = $sc4;
    }
}

FetchallController.php

<?php

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2016/4/6
 * Time: 9:57
 * Zend_Table
 */
require_once 'DBController.php';
require_once APPLICATION_PATH . '/models/student.php';
require_once APPLICATION_PATH . '/models/course.php';
require_once APPLICATION_PATH . '/models/sc.php';

class FetchallController extends DBController
{
    public function indexAction()
    {
        //检索全部信息字段
        $studentModel = new student();
        //创建数据库适配器
        $db = $studentModel->getAdapter();
        $students = $studentModel->fetchAll()->toArray();
        $this->view->students = $students;
        //显示前三个男学生，按年龄排序
        //$students->fetchAll($where, $order, $count, $offset);
        // SELECT * FROM student
        //     WHERE sex = 'M'
        //     ORDER BY age DESC
        //     LIMIT 3 OFFSET 0
        $where = $db->quoteInto('1=1 AND sex = ?', M);
        $order = 'age DESC';
        $count = '3';
        $offset = 0;
        $fstudents = $studentModel->fetchAll($where, $order, $count, $offset)->toArray();
        $this->view->fstudents = $fstudents;
        //显示前三个计算机、化学系学生，按年龄排序
        //转码 utf-8格式 quoteInfo在截取奇数个中文utf-8格式时乱码
        $ph = mb_convert_encoding('化学系', 'GBK', 'UTF-8');
        $dept = mb_convert_encoding('计算机系', 'GBK', 'UTF-8');
        $where = $db->quoteInto('1=1 AND dept IN(?)', array($dept, $ph));
        $where = mb_convert_encoding($where, 'UTF-8', 'GBK');
        $order = 'age DESC';
        $count = '3';
        $offset = 0;
        $jhstudents = $studentModel->fetchAll($where, $order, $count, $offset)->toArray();
        $this->view->jhstudents = $jhstudents;
        //显示前三个计算机系女生，按年龄排序
        $where = $db->quoteInto('1=1 AND dept=?', '计算机系')
            . $db->quoteInto(' AND sex = ?', F);
        $order = 'age DESC';
        $count = '3';
        $offset = 0;
        $students3 = $studentModel->fetchAll($where, $order, $count, $offset)->toArray();
        $this->view->students3 = $students3;
        //检索部分字段信息
        //取出所有student姓名、性别
        $db = $studentModel->getAdapter();
        //使用适配器查询返回数组而非对象，无需toArray()
        $sql = $db->quoteInto('SELECT name, sex FROM student');
        $students4 = $db->query($sql)->fetchAll();
        $this->view->students4 = $students4;
        //取出所有student姓名、性别 参数
        $db = $studentModel->getAdapter();
        //使用适配器查询返回数组而非对象，无需toArray()
        $sql = $db->quoteInto('SELECT name, sex FROM student WHERE sex = ?', 'F');
        $students5 = $db->query($sql)->fetchAll();
        $this->view->students5 = $students5;
        //取出所有student姓名、性别 多参数
        $db = $studentModel->getAdapter();
        //使用适配器查询返回数组而非对象，无需toArray()
        //符号为"=:"   不是">:"!!! “:xxx”不可有空隙
        $students6 = $db->query('SELECT name, sex FROM student WHERE sex = :fm AND dept = :de'
            , array('fm' => 'M', 'de' => 'blabla'))->fetchAll();
        $this->view->students6 = $students6;
    }
}

参考链接：http://blog.csdn.net/u010139093/article/details/24140265