Changing the ballads pages to Symfony

Setting up this site in Symfony

Phase 4: adding db-based pages; 1: ballads

Let's start off with the Child ballads database which is stored in a MySQL database, unlike the blog which is stored in an SQLite file. Child grouped the ballads together by theme, to each of which he gave a number. Most of these themes have several variants, so the structure is number, with 1 or more variants, with 1 or more verses. I split this into 2 tables, rather facetiously calling the main one with the actual text 'children'. Each verse (i.e. row in the table) is keyed on nvv (number, variant, verse). The 2nd table contains the titles, keyed on nv (number, variant), as each variant can have a different title. There are 2 overview lists, one by title, the other by number, and a display of the text for a given number. In addition, there is a search, either in the titles or in the text.

Creating the model

As the db already exists, we can generate config/ballads-schema.yml from it, and then generate the model from that. First, we have to make sure the correct connection settings are in config/propel.ini:

propel.database.createUrl  = mysql:localhost/
propel.database.url        = mysql:host=localhost;dbname=ballads

Then we can build the schema:

symfony propel:build-schema

This creates config/schema.yml, so we rename it to config/ballads-schema.yml. However, it does not create the link between the two tables, so we edit it and add to the entry for nv in the children table:

      foreignTable: titles
      foreignReference: nv

and then build the model:

symfony propel:build-model

Adding the foreign reference gives us the methods getChildrens() in lib/model/om/BaseTitles.php and doSelectJoinChildren() in lib/model/om/BaseTitlesPeer.php, both of which we use later on.

We now have to add the ballads db to config/databases.yml:

  ballads:
    class:          sfPropelDatabase
    param:
      dsn: mysql:dbname=ballads;host=localhost 
      username: xxx
      password: yyy
      encoding: utf8
      persistent: true
      pooling: false
      classname: PropelPDO

Prelims: routing, new module and meta entries

routing: add the appropriate entries to apps/main/config/routing.yml, to correspond with the lists, display and search mentioned above. We'll also cover the eventuality that someone omits the :order or :numbr parameter, giving them the list (according to the Symfony book, you can force a default parameter value, but this logic doesn't seem to work):

ballad_list:
  url:   /ballads/list/:order
  param: { module: ballads, action: list }

ballads_list:
  url:   /ballads/list/
  param: { module: ballads, action: list }

ballad_display:
  url:   /ballads/display/:numbr
  param: { module: ballads, action: display }

ballads_display:
  url:   /ballads/display/
  param: { module: ballads, action: list }

ballad_search:
  url:   /ballads/search
  param: { module: ballads, action: search }

new module: really, this is a separate app, but I want to use the same layout and settings etc, so just make it a separate module within the existing app:
```
symfony module main ballads
```
meta entries: we can also create a view.yml for the module, something we should now be familiar with

Setting up the actions

Now we need to set up the actions that correspond to the urls set up in routing.yml.

introductory/home page: this is a simple static page, just like the ones already set up, so this needs an executeShow() like the other modules
lists: the routing above defines this as a 'list' action, with an 'order' parameter to define whether the list is by title or no; we'll make title the default:
```
  public function executeList()
  {
    $order = 'titles'; // default order
    $order = $this->getRequestParameter('order');
    $c = new Criteria();
    $c->addGroupByColumn(TitlesPeer::TITLE);
    if ($order == 'nos')
	$c->addAscendingOrderByColumn(TitlesPeer::NUMBR);
    else
	$c->addAscendingOrderByColumn(TitlesPeer::TITLE);
    $this->titles = TitlesPeer::doSelect($c);
  }
```
This is a simple read of the titles table, returning an array of Titles objects. Because the table has 1 entry per variant, not 1 per title, we do not want all the rows, so we group by title to only get the unique ones. In the previous setup, I was using 'select distinct title, numbr' to do this. You can do this in the criteria with setDistinct(), but then you are only returning specific columns and so the result no longer corresponds to the object model. This means you would have to use doSelectRS(), which returns a record-set, which would have to be handled differently by the template. (I suppose you could also extend the model, only including certain columns, but that's rather a lot of work for little result; using 'group by' is a much simpler option :-) )
display: this has 'numbr' as the parameter. This is not the primary key (which is 'nv'), so we cannot use retrieveByPK(), but must set up the criteria using 'numbr'; in this case, we do want all the variants for the given number:
```
  public function executeDisplay()
  {
    $c = new Criteria();
    $c->add(TitlesPeer::NUMBR, $this->getRequestParameter('numbr'));
    $this->titles = TitlesPeer::doSelect($c);
    $this->forward404Unless($this->titles);
  }
```

search: here the user enters the search term and clicks a choice of titles or text to search on, so we need a 'like' criteria. The titles search is a simple search on the titles table, but the textual search is on the children, so we need to set up the join for that:

  public function executeSearch()
  {
    $c = new Criteria();
    $c->addGroupByColumn(TitlesPeer::TITLE);
    $c->addAscendingOrderByColumn(TitlesPeer::TITLE);
    if ($this->getRequestParameter('type') == 'titles') {
      $c->add(TitlesPeer::TITLE, '%'.$this->getRequestParameter('term').'%', Criteria::LIKE);
      $this->titles = TitlesPeer::doSelect($c);
    }
    else {
      $c->addJoin(TitlesPeer::NV, ChildrenPeer::NV);
      $c->add(ChildrenPeer::WORDS, '%'.$this->getRequestParameter('term').'%', Criteria::LIKE);
      $this->titles = TitlesPeer::doSelect($c);
    }
  }

Adapting the templates

Now we set up the templates, adapting the existing html:

the home page contains the search form. Previously, I used Symfony's form helpers to set up the tags, but Symfony now uses a special forms setup which is complete overkill for a small form like this, so I now just use normal HTML tags.

<?php echo form_tag('ballads/search') ?>
<?php echo input_tag('term', null, array ( 'size = 20, maxlength = 100' )) ?>
<br /><?php echo radiobutton_tag('type', 'titles', true) ?>titles
<?php echo radiobutton_tag('type', 'text', false) ?>text
<br /><?php echo submit_tag('Search') ?>
</form>

the list and search templates have a similar structure, listing the titles found, so we can put the common code in a partial. Because $titles is an object array, you can simply use the get() methods for the values, and use count() to get the no of objects/titles:

<p><?php echo count($titles); ?> titles found<br />Clicking on a title will fetch all variants for that title</p>
<?php foreach ($titles as $title): ?>
      <?php echo link_to($title->getTitle(), 'ballads/display?numbr='.$title->getNumbr()) ?> (#<?php echo $title->getNumbr() ?>)<br />
<?php endforeach; ?>

the ballad display also receives an array of Titles objects, but it needs an additional loop to process the children:

<?php foreach ($title->getChildrens() as $child): ?>
<div><div id="left" class="verse"><?php echo $child->getVerse(); ?></div><div class="words"><?php echo nl2br($child->getWords()); ?></div></div>
<?php endforeach; ?>

The previous/next control uses variables set up in the action:

    $this->prev    = $this->getRequestParameter('numbr') - 1;
    $this->next    = $this->getRequestParameter('numbr') + 1;

and then output as appropriate with 'if' in the template:

<?php if ($next != 306): ?>
 	<?php echo link_to('Next ballad', 'ballads/display?numbr='.$next) ?>
<?php endif; ?>

Changing the character set

There's one additional problem that needs to be resolved. The database was originally set up several years ago when MySQL didn't have any character-set logic. Consequently, it is in Latin-1. The site layout is however in UTF-8. In a normal php script, you would do mysql_query("set names utf8", $con); after setting up the connection to get it to translate the output. Although this is database access, it has nothing to do with the model. You can however do it in the action by sending 'raw' SQL directly to the connection and bypassing the model. As it has to be executed before each action, it can be put in a preExecute function:

  public function preExecute()
  {
    $connection = Propel::getConnection('ballads');
    $stmt = $connection->prepare("set names utf8");
    $stmt->execute();
  }

Peter Robins, his website