1. General Setup
    1. Setting up Propel
    2. Generating the Code
    3. Prepairing the database
    4. Creating the documentation
    5. Putting it all together
  2. Example Code
    1. Creating a set of Publishers
    2. Creating an Author
    3. Creating a Book
    4. Get a book joined to author
    5. Using a custom SQL query

A bookshop application

This is an example application of a bookshop.

Throughout this tutorial I will use Var_Dump::display rather then var_dump, this is just force of habit. PEAR's Var_Dump controller makes dumps look nice.

Please note that a lot of this document reflects how I (Ian, aka [pookey]) use propel. You may choose to put files in different places, not use Var_Dump from PEAR, use your mouse in your left hand - whatever you want to change, feel free!

This guide is not a replacement for the [User Guide]. If you have not read the [User Guide], please do go and read it now.

General Setup

Here are things I keep reasonably common with my web applications.

I keep 3 directories 

htdocs/   lib/   propel/

The propel directory is a symlink into the propel projects directry 

bookshop # ln -s /usr/lib/php/data/propel-generator/projects/bookshop/ ./propel

Inside lib, I do a symlink to the build data files 

lib # ln -s ../propel/build/classes/bookshop/ ./Data

This is my init script, this does not go in the htdocs folder, but in the parent folder 

<?php
  define('SITE_ROOT', dirname(__FILE__) . '/');
  define('LIB_ROOT', SITE_ROOT . 'lib/');
  // my use of PEAR_ROOT might seem odd to some people, remove it if you don't like it :)
  // I like to make things clear, if it's coming from PEAR I like to know that simply by
  // looking at the include line
  define('PEAR_ROOT', '');

  require_once PEAR_ROOT . 'propel/Propel.php';
  require_once PEAR_ROOT . 'Var_Dump.php';

  Var_Dump::displayInit(
    array(
        'display_mode' => 'HTML4_Table'
    ),
    array(
        'show_caption'   => FALSE,
        'bordercolor'    => '#DDDDDD',
        'bordersize'     => '2',
        'captioncolor'   => 'white',
        'cellpadding'    => '4',
        'cellspacing'    => '0',
        'color1'         => '#FFFFFF',
        'color2'         => '#F4F4F4',
        'before_num_key' => '<font color="#CC5450"><b>',
        'after_num_key'  => '</b></font>',
        'before_str_key' => '<font color="#5450CC">',
        'after_str_key'  => '</font>',
        'before_value'   => '<i>',
        'after_value'    => '</i>'
    )
  );

  // -------------------------------------
  // all the init code...

  // change the include path for propel
  set_include_path(
                   SITE_ROOT . 'propel/build/classes/' . PATH_SEPARATOR .
                   get_include_path()
                  );

  Propel::init(SITE_ROOT . 'propel/build/conf/bookshop-conf.php');

Setting up Propel

In your propel directroy, you'll need a schema.xml. For more information on the following 3 files, please look at the [User Guide]. For this example, here is the schema I'm using: 

<?xml version="1.0" encoding="utf-8"?>
<database name="bookshop">
  <table name="author" idMethod="native">
    <column name="author_id" type="INTEGER" required="true" autoIncrement="true" primaryKey="true" />
    <column name="name" type="VARCHAR" size="255" required="true" />
  </table>
  <table name="publisher" idMethod="native">
    <column name="publisher_id" type="INTEGER" required="true" autoIncrement="true" primaryKey="true" />
    <column name="name" type="VARCHAR" size="255" required="true" />
  </table>
  <table name="book" idMethod="native">
    <column name="book_id" type="INTEGER" required="true" autoIncrement="true" primaryKey="true" />
    <column name="title" type="VARCHAR" size="255" required="true" />
    <column name="author_id" type="INTEGER" required="true" />
    <column name="publisher_id" type="INTEGER" required="true" />
    <foreign-key foreignTable="author">
      <reference
        local="author_id"
        foreign="author_id" />
    </foreign-key>
    <foreign-key foreignTable="publisher">
      <reference
        local="publisher_id"
        foreign="publisher_id" />
    </foreign-key>
  </table>
</database>

build.properties 

propel.project = bookshop
propel.database = mysql
propel.database.url = mysql://user:password@localhost/bookshop
propel.targetPackage = bookshop

runtime-conf.xml 

<?xml version="1.0" encoding="ISO-8859-1"?>
<config>
        <log>
                <ident>propel-bookshop</ident>
                <level>7</level>
        </log>
        <propel>
                <datasources default="bookshop">
                        <datasource id="bookshop">
                                <adapter>mysql</adapter>
                                <connection>
                                        <phptype>mysql</phptype>
                                        <hostspec>localhost</hostspec>
                                        <database>bookshop</database>
                                        <username>user</username>
                                        <password>password</password>
                                </connection>
                        </datasource>
                </datasources>
        </propel>
</config>

Generating the Code

In the propel-generator directory, use phing to generate your classes 

# phing -Dproject=bookshop

This will create the following files 

# ls projects/bookshop/* -R
projects/bookshop/build.properties  projects/bookshop/runtime-conf.xml  projects/bookshop/schema.xml

projects/bookshop/build:
classes  conf  sql

projects/bookshop/build/classes:
bookshop

projects/bookshop/build/classes/bookshop:
Author.php  AuthorPeer.php  Book.php  BookPeer.php  map  om

projects/bookshop/build/classes/bookshop/map:
AuthorMapBuilder.php  BookMapBuilder.php

projects/bookshop/build/classes/bookshop/om:
BaseAuthor.php  BaseAuthorPeer.php  BaseBook.php  BaseBookPeer.php

projects/bookshop/build/conf:
bookshop-conf.php

projects/bookshop/build/sql:
schema.sql  sqldb.map

Prepairing the database

Create the datbase in mysql 

mysql> create database bookshop;
Query OK, 1 row affected (0.05 sec)

Get propel to create the tables in the database 

# phing -Dproject=bookshop insert-sql

Creating the documentation

It's almost essential to build documentation at this point for your propel classes. For information on how to do this, have a look [here].

[click here] for the API docs for this example.

Putting it all together

All of this allows me to include propel's generated classes into my project by doing this: 

<?php
  // this is htdocs/index.php
  require_once '../init.php';
  require_once LIB_ROOT . 'Data/Book.php';
  $book = new Book();
  Var_Dump::display($book);
?>

This should show something like this:

book_id:protected NULL
title:protected NULL
author_id:protected NULL
aAuthor:protected NULL
alreadyInSave:private bool false
alreadyInValidation:protected bool false
_new:private bool true
_deleted:private bool false
modifiedColumns:protected array(0)
_log:private NULL

Example Code

Creating a set of Publishers

Ths code snippet shows looping though an array and creating a new publisher for each one. Ater inserting each one it will print out the PublisherId that was gererated by the database on insertion of the data. 

  $publishers = array(  
                        'Acme Publisher',
                        'Penguin Books',
                        'Wibble Press',
                     );

  echo "creating publishers...\n";
  foreach($publishers as $publisherName) {
    echo "\t -  creating publisher: '{$publisherName}'... ";
    $publisher = new Publisher();
    $publisher->setName($publisherName);
    $publisher->save();
    echo "done! (id: {$publisher->getPublisherId()})\n";
  }
  echo "done!\n";

Creating an Author

This will insert a new author, and then print out the autonumber id generated by the database. 

  $author = new Author();
  $author->setName('Ian P. Christian');
  $author->save();
  echo $author->getAuthorId();

Creating a Book

This will create a book, and link it to an author. 

  echo "creating a book: 'Propel Example Bookshop Book!'...\n";
  $book = new Book();
  $book->setTitle('Propel Example Bookshop Book!');
 
  echo "\t - Linking book to author... ";
  $book->setAuthor($author);
  echo "done\n";

  echo "\t - Linking book to publisher... ";
  $book->setPublisher($publisher);
  echo "done\n";

  echo "\t - saving book...";
  $book->save();
  echo "done\n";

Get a book joined to author

This shows how to get a specific book, and print it's author's name. 

  $c = new Criteria();
  $c->add(BookPeer::BOOK_ID, $bookId);
  // the following function returns an array of objects, but
  // we know we only want the first one, so list() is used.
  list($book) = BookPeer::doSelectJoinAuthor($c);

  echo $book->getTitle() . ' was written by ' . $book->getAuthor()->getName() . "\n";

Alternativly, if you don't mind 2 queries, you could do this: 

  $book = BookPeer::retrieveByPK($bookId);
  echo $book->getTitle() . ' was written by ' . $book->getAuthor()->getName() . "\n";

The difference between these two is that propel does a join in the first example, so saves on a databasea lookup. The second looks far simpler, but should be avoided, especially if you are looping though books, as each loop itteration would result in an extra query.

Using a custom SQL query

This will affectivly achive the same as the above two functions, returning an array of books and the author who wrote it. 

  $sql = "SELECT " . BookPeer::TABLE_NAME . ".*, " . AuthorPeer::TABLE_NAME . ".* FROM " . AuthorPeer::TABLE_NAME . " JOIN " . BookPeer::TABLE_NAME .
         " ON " . AuthorPeer::AUTHOR_ID . " = " . BookPeer::AUTHOR_ID;

  $con = Propel::getConnection(AuthorPeer::DATABASE_NAME);
  $stmt = $con->createStatement();
  $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);

  $results = array();
  $book_start_col   = 1;
  $author_start_col = (BookPeer::$numColumns - BookPeer::$numLazyLoadColumns) + 1;

  while ($rs->next()) {
    $book = new Book();
    $book->hydrate($rs, $book_start_col);

    $author = new Author();
    $author->hydrate($rs,$author_start_col);

    $book->setAuthor($author);
    $results[] = $book;
  }

  foreach($results as $book) {
    echo $book->getTitle() . ' was written by ' . $book->getAuthor()->getName() . "\n";
  }