Zend Framework and Mysql

http://blog.youkuaiyun.com/islq/archive/2006/05/09/713939.aspx

Connecting with MySQL; sample code included [message #9539]	Fri, 05 May 2006 20:47
Jared Messages: 4 Registered: May 2006
To connect with MySQL using the Framework Tutorial code, the changes from SQLite are surprisingly minor. Here are the changes which worked for me: 1. Modify your htdocs/index.php. I included this code in an if branch for testing, but you could entirely replace the SQLite call: $params = array ('host' => 'localhost', 'username' => 'zend', 'password' => 'zend', 'dbname' => 'zend'); $db = new DatabaseMySQL($params); Zend::register('db', $db); 2. Then create a copy of Database.php, calling it DatabaseMySQL.php, and modify the new one as follows. The first 9 lines should be something like this: class DatabaseMySQL extends Zend_Db_Table { private $db; public function __construct($params) { $this->db = Zend_Db::factory('pdoMysql', $params); Zend_Db_Table::setDefaultAdapter($this->db); } While you are still in this file, do a global search-replace on _db, changing it to db, and then save the file. 3. Create 2 tables in your MySQL database: create database if not exists `zend`; USE `zend`; /*Table structure for table `comments` */ drop table if exists `comments`; CREATE TABLE `comments` ( `id` int(11) NOT NULL default '0', `name` varchar(255) default NULL, `comment` text, `newsId` int(11) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; /*Table structure for table `news` */ drop table if exists `news`; CREATE TABLE `news` ( `id` int(11) NOT NULL default '0', `title` varchar(255) default NULL, `content` text, `approval` char(1) default 'F', PRIMARY KEY (`id`) ) ENGINE=MyISAM; 4. Create a new user for MySQL manually, or run these statements: insert into mysql.user ( host, user, password, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv, process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv ) values ( 'localhost', 'zend', PASSWORD('zend'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y' ); flush privilege; The completed source files are attached. I'm just learning much of this, so if I have made any errors, please let me know. -Jared

A few refinements:

1. It turns out you need to make the ID columns in MySQL autoincrement. SQLite does this automatically, but it must be specified in MySQL. Here, then is the corrected SQL CREATE:

create database if not exists `zend`;

USE `zend`;

/*Table structure for table `comments` */

drop table if exists `comments`;

CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `comment` text,
  `newsId` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

/*Table structure for table `news` */

drop table if exists `news`;

CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `content` text,
  `approval` char(1) default 'F',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

2. Next, I made the DatabaseMySQL.php code match the Zend Framework 1.3 recommendations from the manual. Use the following code instead of the file attached above.

<?php

class DatabaseMySQL extends Zend_Db_Table
{
    private $db;

    public function __construct($params)
    {
        $this->db = Zend_Db::factory('pdoMysql', $params);
        Zend_Db_Table::setDefaultAdapter($this->db);
    }

    public function addComment($name, $comment, $newsId)
    {

      $row = array(
        'name'    => $name,
        'comment' => $comment,
        'newsId'  => $newsId
      );

      $table = 'comments';

      return $this->db->insert($table, $row);

    }

    public function addNews($title, $content)
    {

      $row = array(
        'title'     => $title,
        'content'   => $content
      );

      $table = 'news';

      return $this->db->insert($table, $row);

    }

    public function approveNews($ids)
    {

        $rows_affected = 0;

        foreach ($ids as $id) {

          $set = array(
            'id'       => $id,
            'approval' => 'T'
          );

          $table = 'news';

          $where = $this->db->quoteInto('id = ?', $id);

          $rows_affected += $this->db->update($table, $set, $where);

        }

        return $rows_affected;

    }

    public function getComments($newsId)
    {

        $sql = $this->db->quoteInto(
                "SELECT name, comment
                 FROM   comments
                 WHERE  newsId = ?",
                 $newsId);

        if ($result = $this->db->query($sql)) {
            return $result->fetchAll();
        }

        return FALSE;
    }

    public function getNews($id = 'ALL')
    {
        $id = sqlite_escape_string($id);

        switch ($id) {
            case 'ALL':
                $sql = $this->db->quoteInto(
                       "SELECT id,
                               title
                        FROM   news
                        WHERE  approval = ?",
                        'T');
                break;
            case 'NEW':
                $sql = $this->db->quoteInto(
                       "SELECT *
                        FROM   news
                        WHERE  approval != ?",
                        'T');
                break;
            default:
                $sql = $this->db->quoteInto(
                       "SELECT *
                        FROM   news
                        WHERE  id = ?",
                        $id);
                break;
        }

        if ($result = $this->db->query($sql)) {
            if ($result->rowCount() != 1) {
                return $result->fetchAll();
            } else {
                // wrap the result in an array
                return array($result->fetch());
            }
        }

        return FALSE;
    }
}

?>

Let me know if you find anything I've done wrong.

-Jared