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