Peter Robins, his website

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://user:pw@localhost/
propel.database.url        = mysql://user:pw@localhost/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 both tables, for example in titles:

      foreignTable: children
      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://user:pw@localhost/ballads

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

1. 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
2. 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 :-) )
3. 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);
     }

4. 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 doSelectJoinChildren() 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->add(ChildrenPeer::WORDS, '%'.$this->getRequestParameter('term').'%', Criteria::LIKE);
         $this->titles = TitlesPeer::doSelectJoinChildren($c);
       }
     }

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

• the home page contains the search form, so we use Symfony's helpers to set up the 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; ?>

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');
    $query = 'set names utf8';
    $statement = $connection->prepareStatement($query);
    $statement->executeQuery();
  }