Create, Read, Update and Delete with Magento database

10 steps to create a Magento module that demonstrates how to create a table using install script, and create, read, update, and delete records from the table by Magento’s way.

1. Create the xml file app/etc/modules/Magentotuorial_Weblog.xml with the contents below

<config>
    <modules>
        <Magentotutorial_Weblog>
            <active>true</active>
            <codePool>local</codePool>
        </Magentotutorial_Weblog>
    </modules>
</config>

2. Create the another xml file app/code/local/Magentotutorial/Weblog/etc/config.xml

<config>
    <modules>
        <Magentotutorial_Weblog>
            <version>0.1.0</version>
        </Magentotutorial_Weblog>
    </modules>
	<frontend>
		<routers>
			<weblog>
				<use>standard</use>
				<args>
					<module>Magentotutorial_Weblog</module>
					<frontName>weblog</frontName>
				</args>
			</weblog>
		</routers>
	</frontend>
	<global>
	    <models>
			<weblog>
				<class>Magentotutorial_Weblog_Model</class>
				<resourceModel>weblog_resource</resourceModel>
			</weblog>
			<weblog_resource>
				<class>Magentotutorial_Weblog_Model_Resource</class>
				<entities>
					<blogpost>
						<table>blog_posts</table>
					</blogpost>
				</entities>
			</weblog_resource>
		</models>
		<resources>
			<weblog_setup>
				<setup>
					<module>Magentotutorial_Weblog</module>
					<class>Magentotutorial_Weblog_Model_Resource_Setup</class>
				</setup>
			</weblog_setup>
		</resources>
    </global>
</config>

3. Create a Action Controller app/code/local/Magentotutorial/Weblog/controllers/IndexController.php

<?php
class Magentotutorial_Weblog_IndexController extends Mage_Core_Controller_Front_Action {
    
	public function createNewPostAction() {
		$blogpost = Mage::getModel('weblog/blogpost');
		$blogpost->setTitle('Code Post!');
		$blogpost->setPost('This post was created from code!');
		$blogpost->setCreatedAt(now());
		$blogpost->save();
		echo 'Post with ID ' . $blogpost->getId() . ' created.';
		$this->showAllBlogPostsAction();
	}
	
	public function readPostAction() {
        $params = $this->getRequest()->getParams();
		$blogpost = Mage::getModel('weblog/blogpost');
		echo("Loading the blogpost with an ID of ".$params['id']);
		$blogpost->load($params['id']);
		$data = $blogpost->getData();
		var_dump($data);
		$this->showAllBlogPostsAction();
    }

	public function updatePostAction() {
		$blogposts = Mage::getModel('weblog/blogpost')->getCollection();
		foreach($blogposts as $post)
		{
			if($post->getId()%2==0)
			{
				$post->setPost("This is updated by updatePostAction!!!!");
				$post->setUpdatedAt(now());
				$post->save();
			}
		}
		echo 'Posts with even number id has been updated.';
		$this->showAllBlogPostsAction();
	}
	
	public function deletePostAction() {
		$params = $this->getRequest()->getParams();
		$blogpost = Mage::getModel('weblog/blogpost');
		$blogpost->load($params['id']);
		echo("Deleting the blogpost with an ID of ".$params['id']."<br/>");
		$blogpost->delete();
		echo("The blogpost with an ID of ".$params['id']." has been deleted"."<br/>");
		
		$this->showAllBlogPostsAction();
	}
	
	public function showAllBlogPostsAction() {
		$posts = Mage::getModel('weblog/blogpost')->getCollection();
		echo "<table border='1'><tr><th>Post ID</th><th>Post Title</th><th>Content</th><th>Updated At</th><th>Created At</th></tr>";
		foreach($posts as $blogpost){
			echo "<tr><td>".$blogpost->getId()."</td>";
			echo "<td>".$blogpost->getTitle()."</td>";
			echo "<td>".$blogpost->getPost()."</td>";
			echo "<td>".$blogpost->getUpdatedAt()."</td>";
			echo "<td>".$blogpost->getCreatedAt()."</td>";
			echo "</tr>";
		}
		echo "</table>";
	}
}

4. Create a model class app/code/local/Magentotutorial/Weblog/Model/Blogpost.php

<?php
class Magentotutorial_Weblog_Model_Blogpost extends Mage_Core_Model_Abstract
{
    protected function _construct()
    {
        $this->_init('weblog/blogpost');
    }
}

5. Create a resource class app/code/local/Magentotutorial/Weblog/Model/Resource/Blogpost.php

<?php
class Magentotutorial_Weblog_Model_Resource_Blogpost extends Mage_Core_Model_Resource_Db_Abstract{
    protected function _construct()
    {
        $this->_init('weblog/blogpost', 'blogpost_id');
    }
}

6. Create a collection class app/code/local/Magentotutorial/Weblog/Model/Resource/Blogpost/Collection.php

<?php
class Magentotutorial_Weblog_Model_Resource_Blogpost_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract {
    protected function _construct()
    {
            $this->_init('weblog/blogpost');
    }
}

7. Create a set up php class app/code/local/Magentotutorial/Weblog/Model/Resource/Setup.php

<?php
class Magentotutorial_Weblog_Model_Resource_Setup extends Mage_Core_Model_Resource_Setup {
}

8. Create the install script app/code/local/Magentotutorial/Weblog/sql/weblog_setup/mysql4-install-0.1.0.php, this script will be triggered if any magento page is requested. The commented code does the same as the code above it. It should be used if the database is not MySQL. 

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
    CREATE TABLE `{$installer->getTable('weblog/blogpost')}` (
      `blogpost_id` int(11) NOT NULL auto_increment,
      `title` text,
      `post` text,
      `updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `created_at` datetime default NULL,
      PRIMARY KEY  (`blogpost_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `{$installer->getTable('weblog/blogpost')}` VALUES (1,'My New Title','This is a blog post','2009-07-01 00:00:00','2009-07-02 23:12:30');
");
$installer->endSetup();
/*
$installer = $this;
$installer->startSetup();
$table = $installer->getConnection()->newTable($installer->getTable('weblog/blogpost'))
    ->addColumn('blogpost_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'identity' => true,
        ), 'Blogpost ID')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => false,
        ), 'Blogpost Title')
    ->addColumn('post', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => true,
        ), 'Blogpost Body')
    ->addColumn('date', Varien_Db_Ddl_Table::TYPE_DATETIME, null, array(
        ), 'Blogpost Date')
    ->addColumn('timestamp', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Timestamp')
    ->setComment('Magentotutorial weblog/blogpost entity table');
$installer->getConnection()->createTable($table);

$installer->endSetup();
*/

9. Trigger the code to create, read, update and delete posts

    Create New Posts
    http://localhost/weblog/index/createNewPost
    http://localhost/weblog/index/createNewPost

    Read Posts
    http://localhost/weblog/index/readPost/id/1
    http://localhost/weblog/index/readPost/id/2

    Update Posts
    http://localhost/weblog/index/updatePost

    Delete Posts
    http://localhost/weblog/index/deletePost/id/2

10. The install script will only run once, to run it again, run the queries below first.

select * from core_resource where code like "%web%";
select * from blog_posts;
DELETE from core_resource where code = 'weblog_setup';
DROP TABLE blog_posts;

To update the table using the install script, do not run the above queries, need to create a new script file with name upgrade-0.1.0-0.1.2.php and place appriate update code, and the version number in the app/code/local/Magentotutorial/Weblog/etc/config.xml has to be 0.1.2

Search within Codexpedia

Custom Search

Search the entire web

Custom Search