Connecting to a database

The P4A_DSN constant

Usually applications uses a database to retrieve and store data, when you need to bind your application to a database simply add this line to your index.php file, before requiring P4A itself:

define("P4A_DSN", 'mysql://root:@localhost/p4a_products_catalogue');

The format of the DSN is:

define("P4A_DSN", 'database_engine://username:password@host:port/database_name');

The first sample is about MySQL but you could use PostgreSQL, Oracle or SQLite too:

//define("P4A_DSN", 'pgsql://p4a:p4a@localhost/p4a_products_catalogue');
//define("P4A_DSN", 'oci://p4a:p4a@localhost/xe');
//define("P4A_DSN", 'sqlite:/p4a_products_catalogue');

When you set the P4A_DNS constant P4A will automatically connect to the database when the application is started.

P4A_DB

P4A_DB is the main class to manage database connections, it is quite simple but has a lot of features.

If you set P4A_DNS and start the application, you can access your database in every point of the application simply doing:

$db = P4A_DB::singleton();
$db->fetchAll("SELECT * FROM table");

This is just an example, please take a look at the code reference to understand all P4A_DB methods.

P4A_DB_Source

While P4A_DB class will give you a low-level access to the DB, you surely do not want to deal with selects, inserts etc on a daily basis. That's why P4A has P4A_DB_Source.

P4A_DB_Source will:

• help you setting up your data object with some easy methods
• take care of all the select/insert/update/delete operations (automatically generating cross-db SQL queries)
• autodetect primary keys
• autodetect and manage sequences for new records
• allows you to easily deal with join/where/orders/group by and everything else
• manage all above between different database schemas
• manage calculated fields
• transparently manage n-n relations on external tables
• do all above cross-db supporting MySQL, PostgreSQL, Oracle, SQLite

The most easy db_source:

$this->build("p4a_db_source", "source") // instance the object
	->setTable("mytable") // the main table of this data source is "mytable"
	->load(); // go autodetecting columns and every other thing needed

If your looking for a more complex example just take a look at the next chapter.

The load method and P4A_Data_Field

The load method tells the db_source that the setup is finished and let the tables/columns/sequences/primary keys autodetection starts. After the load finishes you'll have a new collection of objects ready to be used:

$this->source->fields

Every object in the fields collection is a P4A_Data_Field. Data fields are used to map the database field in the P4A world.

Data fields have features like default values:

$this->source->fields->sample_date->setDefaultValue(date("Y-m-d"));

That will be filled when creating a new record.

The data field is really important because allows you to do real data binding inside your applications, in facts you can have different fields sharing the same data_field.

Tying a db_source to a mask, the "setSource" method and the "fields" collection

Usually, writing a P4A application, you'll want to write a mask that will manage a data, mostly a database table or anyway some joined tables. P4A has some nice utilities to speed up this process, take a look at this code:

class Products extends P4A_Base_Mask
{
	public function __construct()
	{
		parent::__construct();

		$this->build("p4a_db_source", "source")
			->setTable("products")
			->addJoin("categories",
					  "products.category_id = categories.category_id",
					  array('description'=>'category'))
			->addJoin("brands", "products.brand_id = brands.brand_id",
					  array('description'=>'brand'))
			->addOrder("product_id","DESC")
			->load();

		$this->setSource($this->source);
		$this->firstRow();

You can see that after building a db_source, we called $this->setSource(), what is that doing?

• It binds the db_source to the mask so you can call $this->firstRow() or $this->lastRow() etc. directly on the mask, and these methods are called on the binded db_source
• It created the fields collection, binding db_source's data_fields to P4A_Fields, autodetecting the field type

So, after the setSource call, you can do something like:

$this->fields->model->setNewValue('Sample model'); // change fields properties
$this->frame->anchor($this->fields->model); // fields are ready to be displayed on your web page

obviously model has to be a column of the tables included in the db_source query.

Using toolbars to do actions on a db_source

Toolbars are a group of buttons, P4A comes with:

• a generic P4A_Toolbar widget: it's an empty toolbar and acts as the container for all the buttons and separators you may want to add
• some pre-built toolbars ready to use:
• P4A_Actions_Toolbar with confirm, cancel and exit buttons
• P4A_Full_Toolbar with confirm, cancel, first, prev, next, last, new, delete and exit buttons
• P4A_Navigation_Toolbar with first, prev, next, last and exit buttons
• P4A_Quit_Toolbar with print and quit buttons
• P4A_Simple_Toolbar with confirm, cancel, print and exit buttons

The pre-built toolbars have an easy setMask method that will bind all buttons of the toolbar with the mask you'll pass to the method:

$this->build("p4a_simple_toolbar", "toolbar") // simple toolbar is the most used
	->setMask($this);

From now on when users will click the buttons on the toolbar, those calls will be routed to the mask object (eg: clicking on the save button will call the saveRow method of the mask).

The saveRow, newRow, deleteRow, nextRow, prevRow, firstRow and lastRow methods

This methods are self-explanatory, you can find them both on every P4A_Data_Source (P4A_Array_Source, P4A_DB_Source) and on every mask.

If you're going to call one of these methods on a mask (instead of on a data source) the method will be called on the binded data source for the mask, a sample for an easy understanding:

class Categories extends P4A_Base_Mask
{
	public function __construct()
	{
		parent::__construct();
		$this->build("p4a_db_source", "source")
			->setTable("categories")
			->load();
		$this->setSource($this->source);
		$this->firstRow();

// etc...

The firstRow above will be translated to $this->source so you should be sure that you called the setSource before.