Projektant - Programista PHP, Python

Today i will show you how to put together: Zend Framework, Doctrine and simple jQuery Grid in easy way. All i want to do is to get some data from database with doctrine, format that data and send it to the template with Zend_Json:) Really nothing special.

Ok. Let’s start. First of all we need to create database schema.yml. It is realy easy country table:

Country:
  tableName: country
  columns:
    id:
      primary: true
      autoincrement: true
      type: integer(10)
    name: string(50)
    description: string(255)

We also have to set up the database configuration in the config.ini:

type       = “mysql”     // database adapter
dbname = “doctrine_grid”  // database name
host       = “localhost”  // host
username = “user”
password = “pass”

Then we must create database, generate models and sql, create tables and load simple data fixtures.

From the command line in the main application directory we call the following code:

1) php doctrine-cli.php create-db

2) php doctrine-cli.php generate-models-yaml

3) php doctrine-cli.php generate-sql //it is not necessary

4) php doctrine-cli.php create-tables

or simply: php doctrine-cli.php build-all

Now we need some data for testing.

Create data.yml file in directory: data/fixtures

Country:
  Country_1:
    name: 'Poland'
    description: 'Description for Poland'
  Country_2:
    name: 'Germany'
    description: 'Description for Germany'
  Country_3:
    name: 'Brzail'
    description: 'Description for Brazil'
  Country_4:
    name: 'USA'
    description: 'Description for USA'
  Country_5:
    name: 'Ireland'
    description: 'Description for Ireland'
  Country_6:
    name: 'Canada'
    description: 'Description for Canada'
  Country_7:
    name: 'India'
    description: 'Description for India'
  Country_8:
    name: 'Italy'
    description: 'Description for Italy'
  Country_9:
    name: 'Spain'
    description: 'Description for Spain'
  Country_10:
    name: 'Portugal'
    description: 'Description for Portugal'
  Country_11:
    name: 'Argentina'
    description: 'Description for Argentina'
  Country_12:
    name: 'Estonia'
    description: 'Description for Estonia'
  Country_13:
    name: 'Bulgaria'
    description: 'Description for Bulgaria'
  Country_14:
    name: 'France'
    description: 'Description for France'
  Country_15:
    name: 'Belgium'
    description: 'Description for Belgium'
  Country_16:
    name: 'Austria'
    description: 'Description for Austria'
  Country_17:
    name: 'Australia'
    description: 'Description for Australia'
  Country_18:
    name: 'Japan'
    description: 'Description for Japan'
  Country_19:
    name: 'Russia'
    description: 'Description for Russia'
  Country_20:
    name: 'Slovakia'
    description: 'Description for Slovakia'
  Country_21:
    name: 'Ireland'
    description: 'Description for Ireland'

And we call:

php doctrine-cli.php load-data

to import data to the database.

Next step is to download jQuery Flexi grid (yesterday i had some problem because site was suspended) and put it into public/js/ (js files I put into js/ and css files into /css folder). We also need to download latest jQuery version.

Now we can take care of php scripts. We can simply create new controller, for example GridController and one action: indexAction.

class GridController extends Core_Controller_Action
{
    public function indexAction(){

    }
}
?>

We don’t need to put any code in the indexAction. Zend will automatic call the index.tpl template where we have to put the following code:

<h1> Testing jQuery FlexiGrid </h1>

<table id="myGrid"> </table>
	<!-- updated to work with IE -->

{literal}

<link rel="stylesheet" type="text/css" href="public/css/flexigrid/flexigrid.css">
<script type="text/javascript" src="public/js/jquery-1.2.6.js"></script>
<script type="text/javascript" src="public/js/flexigrid/flexigrid.js"></script>

<script language="javascript" type="text/javascript">

$("#myGrid").flexigrid({
  url: 'grid/list',
  dataType: 'json',
  colModel : [
    {display: 'Id', name : 'id', width : 40, sortable : true, align: 'center'},
    {display: 'Name', name : 'name', width : 100, sortable : true, align: 'left'},
    {display: 'Description', name : 'description', width : 200, sortable : true, align: 'center'}
  ],
  buttons : [
    {name: 'Add', bclass: 'add', onpress : test},
    {name: 'Delete', bclass: 'delete', onpress : test},
    {separator: true}
  ],
  searchitems : [
    {display: 'Name', name : 'name'}
  ],
  sortname: "name",
  sortorder: "asc",
  usepager: true,
  title: 'List',
  useRp: true,
  rp: 10,
  showTableToggleBtn: true,
  width: 700,
  height: 200
});

function test(com, grid){

  $(".trSelected > td:first-child > div:first-child").each( function(index, element){

  alert($(element).html());

}
);

}

</script>

{/literal}

I have created the empty div for my flexi grid, then imported javascript libaries and called flexigrid method on div element. I just set the most important options (unfortunately there is realy small documentation about flexigrid, so sometimes we need to read the library code).

One of the required option is url. Flexi grid needs that url for retrieving data from the database - for example: sorting and pagination. We need to create function listAction where we put code responsible for getting data, sorting and pangination.

public function listAction(){

    # We don't want to render Layout
    $this->_helper->layout()->disableLayout();

    # rendering view is also not necessary
    $this->_helper->viewRenderer->setNoRender();

    $req = $this->getRequest();

    # Pagination parameters
    $currentPage 	= $req->getPost('page', 1);
    $resultsPerPage = $req->getPost('rp', 10);

    # Sorting parameters
    $orderBy 		= $req->getPost('sortname', 'name');
    $orderType 	= $req->getPost('sortorder', 'ASC');

    # Query parameters - for Searching
    $query 			= $req->getPost('query', '');
    $qtype 			= $req->getPost('qtype', 'name');

    # Creating Doctine pager object
    $pager = new Doctrine_Pager(
      Doctrine_Query::create()
      ->from( 'Country' )
      ->addWhere("$qtype LIKE ?", "%$query%")
      ->orderby( "$orderBy $orderType" ),
    $currentPage, // Current page of request
    $resultsPerPage // (Optional) Number of results per page. Default is 10
    );

    $items = $pager->execute();

    $elements = array();
    # We also have to format our data array that flexifrid could retrieve itt
    foreach($items as $item){

        $elements[] = array(
          'id'	=>	$item->id,
          'cell'	=>	array_values($item->toArray())
        );
    }

    echo Zend_Json::encode(array(
        'page'	=>	$currentPage,
        'total'	=>	$pager->getNumResults(),
        'rows'	=>	$elements
    ));

}
?>

That’s all. We can test our grid calling for example http://your-domain.com/grid/

You should see something like that:

You can also check my example on: www.test.adryjanek.eu/grid