Configure the Database for Bookshop
To develop the database for the bookshop application, start by creating an empty database under MySQL RDBMS named symfonydb. When you have done that, you then need to indicate to Symfony (and mandate to bookshop) to use this database. For this, you need to execute the
configure:database task, like this:
On Windows:
c:demo>php symfony configure:database
"mysql:host=localhost;dbname=symfonydb"
root pass
On Linux:
$ php symfony configure:database
"mysql:host=localhost;dbname=symfonydb"
root pass
Note: The
configure:databasetask takes three arguments: thePDO DSN, theusername, and thepassword(optional) to access the database.
Note: The Symfony framework supports all PDO-supported databases, like MySQL, PostgreSQL, SQLite, Oracle, MSSQL, and so on.
Note: Theconfigure:databasetask stores the database configuration into the/config/databases.ymlconfiguration file. Instead of using the task, you can edit this file by hand.
At this point, Symfony knows which database to use, and you are ready to develop the relational model. Your database will contain a single table, named books, that has the following diagram:
The road from this diagram to the database is facilitated by the Symfony-supported ORMs (Propel or Doctrine). The ORM needs a description of the tables and their relationships to create the related classes. The two ways to create this description schema are introspecting an existing database and creating it by hand.
Obviously, in this case, you can do it by hand. Edit the empty
config/schema.yml file, like this:
# config/schema.yml
propel:
books:
id: ~
title: { type: varchar(255), required: true }
author: { type: varchar(255), required: true }
publisher: { type: varchar(255), required: true }
price: { type: varchar(255), required: true }
yearofpub: { type: varchar(255), required: false }
Now, based on the
schema.yml, you can generate the proper SQL statements by running the propel:build-sql task, like this (the statements will be stored in the data/sql/ directory, optimized for the database engine you have configured, MySQL):
On Windows:
c:demo>php symfony propel:build-sql
On Linux:
$ php symfony propel:build-sql
If you take a quick look in the
data/sql/ folder, you will find the lib.model.schema.sql file with the following content:
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;
#-----------------------------------------------------------------------------
#-- books
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`author` VARCHAR(255) NOT NULL,
`publisher` VARCHAR(255) NOT NULL,
`price` VARCHAR(255) NOT NULL,
`yearofpub` VARCHAR(255),
PRIMARY KEY (`id`)
)Type=InnoDB;
# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
Next, run these SQL statements against the database by running the
propel:insert-sql task, like this:
On Windows:
c:demo>php symfony propel:insert-sql
On Linux:
$ php symfony propel:insert-sql
The ORM also generates PHP classes that map table records to objects. You can accomplish this by running the
propel:build-model task, like this (the generated PHP files are stored in lib/model/ folder):
On Windows:
c:demo>php symfony propel:build-model
On Linux:
$ php symfony propel:build-model
The tables have been created in the database, but there is no data in them. Each time Symfony creates tables in the database, all the data are lost. To populate the database with some initial data, create YAML files in the
data/fixtures/ directory and use the propel:data-load task to load them into the database.
First, create the following fixture file (save it as
fixtures.yml):
# data/fixtures/fixtures.yml
books:
1:
title: SOA Patterns with BizTalk Server 2009
author: Richard Seroter
publisher: Packt
price: E25.89
yearofpub: 2009
2:
title: Learning FreeNAS
author: Gary Sims
publisher: Packt
price: E17.49
yearofpub: 2008
3:
title: MODx Web Development
author: Antano Solar John
publisher: Packt
price: E17.49
yearofpub: 2009
4:
title: Apache Maven 2 Effective Implementation
author: Brett Porter, Maria Odea Ching
publisher: Packt
price: E17.49
yearofpub: 2009
5:
title: RESTful PHP Web Services
author: Samisa Abeysinghe
publisher: Packt
price: E14.99
yearofpub: 2008
Loading the initial data into the database is as simple as running the
propel:data-load task:
On Windows:
c:demo>php symfony propel:data-load
On Linux:
$ php symfony propel:data-load
Note: If you have decided to create the tables by writing SQL statements, you can generate the corresponding
schema.ymlconfiguration file by running thepropel:build-schematask:
Note: You can compress all the tasks executed in this section by running a single one-named
propel:build-all-loadtask, like this:
Generate the Web Pages
Now you have everything you need to create web pages that interact with the symfonydb database. The idea is to generate an application that displays a list of books, and allows the user to edit an existing book and delete a book. A Symfony project is composed of applications, and each application is further divided into modules. A module is a self-contained set of PHP code that represents a feature of the application, or a set of manipulations the user can do on a model object (a book module, for example). Symfony can automatically generate a module for a given model that provides basic manipulation features. To enable this feature, run the
propel:generate-module task, like this (you generate the book module for the books model):
On Windows:
c:demo>php symfony propel:generate-module --with-show --non-verbose-templates frontend book books
On Linux:
$ php php symfony propel:generate-module --with-show --non-verbose-templates frontend book books
Some files and directories have been created for you under the
apps/frontend/modules/book/ directory (explore those files):
| Directory | Description |
|---|---|
actions/ |
The module actions |
templates/ |
The module templates |
You can now test the book module in a browser at http://localhost/frontend_dev.php/book. The result should look like Figure 5 (navigate through the pages, and try to edit, delete and add a new book).
Click here for larger image
Figure 5. Testing the Book Module in the Browser
Customize Web Pages
To customize the generated web page, you first must know that in Symfony the content is rendered by a global template called a layout. The default layout of an application is called
layout.php and you can find it in the apps/frontend/templates/ directory. This directory contains all the global templates for an application.
Replace the default Symfony layout with the following code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<?php include_http_metas() ?>
<?php include_metas() ?>
<?php include_title() ?>
<link rel="shortcut icon" href="/favicon.ico" />
<?php include_stylesheets() ?>
<?php include_javascripts() ?>
</head>
<body>
<table border="0" width="100%">
<tr>
<td>
<img src="/images/books_banner.png" alt="header"/>
</td>
<form action="" method="get">
<td bgcolor="black">
<font color="white">Search:</font>
<input size="45" type="text" >
<input type="submit" value="Search" >
<font type="arial" size="25" color="yellow">
<a href="/frontend_dev.php/books/new">New book</a></font>
</td>
</form>
</tr>
<tr>
<td align="center" colspan="2">
<?php echo $sf_content ?>
</td>
</tr>
<tr>
<td colspan="2">
<img src="/images/footer.jpg" height="40" width="100%" alt="footer"/>
</td>
</tr>
</table>
</body>
</html>
Note: The stylesheets, images, JavaScript and uploads are stored by default under the
/webfolder (in/css,/images,/js, and/uploadsfolders, respectively). Therefore, you should place your images under the/imagesfolder.
Now, you can test the application again at http://localhost/frontend_dev.php/book. You should see something similar to Figure 6.
Click here for larger image
Figure 6. Testing the Book Module After Customizing the Web Page
Bookshop Complete
You have now completed the mechanism for seeing the books, the function for deleting/editing/adding a book, and the view for searching for a book. As you saw, Symfony did most of the work. Symfony is capable of so much more, but a full examination of the framework is well beyond the scope of this article. I hope that you have witnessed enough of Symfony’s power to explore what else it can do at symfony-project.org.
About the Author
Octavia Andreea Anghel is a senior PHP developer currently working as a primary trainer for programming teams that participate at national and international software-development contests. She consults on developing educational projects at a national level. She is a coauthor of the book “XML Technologies–XML in Java” (Albastra, ISBN 978-973-650-210-1), for which she wrote the XML portions. In addition to PHP and XML, she’s interested in software architecture, web services, UML, and high-performance unit tests. to e-mail her.
