Posted by: calsunshine | August 24, 2011

How to create a model and get data from MySQL to display in SE4

Let me cover how to create model, controller and view files to get data from a MySQL table.


First, go to phpmyadmin page and create a new table in the social engine database. You can save the following sql code as trip.sql and import it to your mysql database to auto create the table and test data. It will create a table named engine4_traveling_trips, which stores the trip information belongs to a user. You must change “engine4” to your database prefix. “traveling” is the module name I created last time. “trips” is the table name, also our model name. It also inserts 7 row of test data.

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- Host: localhost
-- Generation Time: Aug 23, 2011 at 10:56 PM
-- Server version: 5.1.44
-- PHP Version: 5.3.1

-- Table structure for table `engine4_traveling_trips`

CREATE TABLE IF NOT EXISTS `engine4_traveling_trips` (
  `trip_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `trip_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `thumb_url` varchar(2083) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime NOT NULL,
  PRIMARY KEY (`trip_id`),
  KEY `user_id` (`user_id`)

-- Dumping data for table `engine4_traveling_trips`

INSERT INTO `engine4_traveling_trips` (`trip_id`, `trip_name`, `user_id`, `thumb_url`, `creation_date`, `modified_date`) VALUES
(1, '旧金山之旅', 1, 'http://localhost/socialengine416/public/traveling/trips/trips_San_Francisco_thumb-10000000001.jpg', '2011-08-18 00:00:00', '2011-08-18 00:00:00'),
(2, '伦敦七天游', 1, 'http://localhost/socialengine416/public/traveling/trips/trips_London_thumb_10000000002.jpg', '2011-08-18 14:09:46', '2011-08-18 14:09:51'),
(3, '浪漫之都巴黎10日蜜月游', 1, 'http://localhost/socialengine416/public/traveling/trips/trips_Paris_thumb_1000000003.jpg', '2011-08-18 00:00:00', '2011-08-18 00:00:00'),
(4, '哥斯达黎加探险之旅', 2, 'http://localhost/socialengine416/public/traveling/trips/trips_Costa_Rica_thumb_1000000004.jpg', '2011-08-18 00:00:00', '2011-08-18 00:00:00'),
(5, '洛杉矶夏日一周游', 2, 'http://localhost/socialengine416/public/traveling/trips/trips_Los_Angeles_thumb_1000000005.jpg', '2011-08-18 16:40:36', '2011-08-18 16:40:39'),
(6, '香港国庆七天畅游', 3, 'http://localhost/socialengine416/public/traveling/trips/trips_Hong_Kong_thumb_1000000006.jpg', '2011-08-18 16:42:21', '2011-08-18 16:42:21');

Now we have the table, let’s create a model to represent the table and the row class. If you have followed my custom module tutorial last time, you will find there is a /Module/Traveling/Model/ folder and a DbTable subfolder. Create “trip.php” under “Model” folder and create “trips.php” under DbTable subfolder.

Code for “trip.php”:

    class Traveling_Model_Trip extends Core_Model_Item_Abstract
        protected $_owner_type = 'user';

The name of the class is in the format of “Modulename_Model_Tablename”. I don’t think the $_owner_type line is needed but just in case. This class represents each row of the trip table, so its name is singular.

Code for “trips.php”:

class Traveling_Model_DbTable_Trips extends Engine_Db_Table
        protected $_rowClass = 'Traveling_Model_Trip';

This class represents the whole trips table, so it is plural.


Now open the tripController.php created under “/Module/Traveling/controllers/”. Add a new action called viewallAction(), which is to view all the trips belong to a user.

viewallAction() code:


class Traveling_TripController extends Core_Controller_Action_Standard
    public function indexAction()
         // some code here...

    // view all trips of a user
    public function viewallAction()
        // get the table
        $table = Engine_Api::_()->getDbtable('trips', 'traveling');
        // filtering, get only the current user's trips
        $select = $table->select()
                        ->where("user_id = $id", 1)
        // get the data
        $result = $table->fetchAll($select);
        $this->view->trips = $result;

To get the table, use “Engine_Api::_()->getDbtable(‘tablename’, ‘modulename’)”. Then use select() method to define the select query like in SQL. where() defines the criteria and order() defines sorting. To execute the query use fetchAll(). These are all Zend Framework functions. Some documentation here:

In this way, the objects returned are a collection of the Traveling_Model_Trip object defined above in the trip.php model. Use $this->view->anyvariablename to pass the object to the view.


Now let’s display the results in the view. Create a subfolder “trip” for tripController under “/Traveling/views/scripts/”. Then create a view file viewall.tpl under the trip subfolder. The naming convention is actionname.tpl.

Code for viewall.tpl:

foreach($this->trips as $trip) { 
    echo "Trip id is: ".$trip->trip_id." and trip name is: ".$trip->trip_name.".";

Then go to url “http://localhost/sitename/index.php/traveling/trip/viewall/&#8221; or “http://localhost/sitename/traveling/trip/viewall/&#8221;. You should see the trip id and trip name being displayed. The naming convention of the url is “http://servername/sitename/modulename/controllername/actionname/&#8221;.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: