Zend Framework and Mysql

 

http://blog.csdn.net/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.

<?phpclass 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