由浅入深详细剖析Zend Framework 2 数据库连接,获取数据

2014年02月24日

第一步:通过代码直接连接数据库,获取数据

注意,这里为了简单举例,在业务逻辑层(Controller)直接处理数据层(Model),实际项目开发时请不要这样做,违背MVC原则.

module/Application/src/Application/Controller/IndexController.php
<?php
/**
 * This source file is part of Qiai.
 *
 * PHP Version >=5.3
 *
 * @category   Qiai-Project
 * @package    Application
 * @subpackage Controller
 * @author     Sai (QIAI) <sai@qiais.com>
 * @license    Free-License http://www.qiai.com/license-free
 * @link       http://www.qiais.com
 */
namespace Application\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;
use Zend\Debug\Debug;
use Zend\Db\Adapter\Adapter;


class IndexController extends AbstractActionController
{
    /**
     * getDataByCode Action
     * 不通过zf2配置文件,直接从代码连接数据库,
     * 实际项目开发时应将数据库操作置于Model中。
     *
     * @return false
     */
    public function getDataByCodeAction()
    {
        //创建数据库适配器
        $adapter = new Adapter(array(
            'driver' => 'Mysqli',
            'database' => 'qiais_cn',
            'username' => 'qiais',
            'password' => 'qiais312',
         ));
        //获取数据
        $users = $adapter->query("select * from users", Adapter::QUERY_MODE_EXECUTE);

        return new ViewModel(array('users' => $users));
    }
    /**
     * index action
     *
     * @return view
     */
    public function indexAction()
    {
        return new ViewModel();
    }
}

接下来添加view文件module/Appcation/view/application/index/get-data-by-code.phtml

<table class="table table-bordered table-striped">
    <tr><th>姓名</th><th>邮箱</th></tr>
<?php foreach ($this->users as $key => $user): ?>
    <tr>
    <td><?php echo $user['name']; ?></td>
    <td><?php echo $user['email']; ?></td>
    </tr>
<? endforeach; ?>
</table>

访问http://zf2-tutorial/application/index/get-data-by-code

db-by-code

因为从数据库取出数据时没有设置编码,这里出现乱码,暂时不管它,

第二步:通过配置文件连接数据库,获取数据

配置根目录下的config/autoload/global.php文件

config/autoload/global.php
<?php
/**
 * This source file is part of Qiai.
 *
 * PHP Version >=5.3
 *
 * @category   Qiais-Project
 * @package    Config
 * @subpackage Global
 * @author     Sai (QIAI) <sai@qiais.com>
 * @license    Free http://www.qiai.com/license-free
 * @link       http://www.qiais.com
 */

return array(
    'db' => array(
        'driver' => 'Pdo',
        'dsn' => 'mysql:dbname=qiais;host=localhost',
        'username' => 'qiais_cn',
        'password' => 'qiais312',
        'driver_options' => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
        ),
    ),
    'service_manager' => array(
        'factories' => array(
            'Zend\Db\Adapter\Adapter' => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
    ),
);

注意,这里我们配置了读取数据库的编码方式为UTF8,就不会出现乱码了。

通过配置文件连接数据库,获取数据

module/Application/src/Application/Controller/IndexController.php
<?php
/**
 * This source file is part of Qiai.
 *
 * PHP Version >=5.3
 *
 * @category   Qiais-Project
 * @package    Application
 * @subpackage Controller
 * @author     Sai (QIAI) <sai@qiais.com>
 * @license    Free http://www.qiai.com/license-free
 * @link       http://www.qiais.com
 */
namespace Application\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;
use Zend\Debug\Debug;
use Zend\Db\Adapter\Adapter;

/**
 * Login controller for user module
 *
 * @category   Qiais-Project
 * @package    Application
 * @subpackage Controller
 */
class IndexController extends AbstractActionController
{
    /**
     * getDataByConfiguration Action
     * 通过zf2配置文件,连接数据库,获取数据
     *
     * @return false
     */
    public function getDataByConfigurationAction()
    {
        $db = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $users = $db->query("select * from users", Adapter::QUERY_MODE_EXECUTE);

        $viewModel = new ViewModel(array('users' => $users));
        //这里调用get-data-by-code.phtml文件作为输出模版
        $viewModel->setTemplate('application/index/get-data-by-code.phtml');
        return $viewModel;
    }
    /**
     * getDataByCode Action
     * 不通过zf2配置文件,直接从代码连接数据库,
     * 实际项目开发时应将数据库操作置于Model中。
     *
     * @return false
     */
    public function getDataByCodeAction()
    {
        //创建数据库适配器
        $adapter = new Adapter(array(
            'driver' => 'Mysqli',
            'database' => 'qiais_cn',
            'username' => 'qiais',
            'password' => 'qiais312'
         ));
        //获取数据
        $results = $adapter->query("select * from users", Adapter::QUERY_MODE_EXECUTE);
        foreach ($results as $result) {
            Debug::dump($result, 'qiai-query-code');
        }

        return false;
    }

因为我们设置了view的模版文件,所以不用新建view文件。

访问http://zf2-tutorial/application/index/get-data-by-configuration

db-by-configuration