Introduction to Table Class

The Zend_Db_Table class is an object-oriented interface to database tables. It provides methods for many common operations on tables. The base class is extensible, so you can add custom logic.

Defining a Table Class

For each table in your database that you want to access, define a class that extends Zend_Db_Table_Abstract.

My feeling on this, is that it’s a little bit “overkill”, at least for a website with a DB that handles hundreds of tables. I feel some tables will always be used in some kind of logical grouping into some class.  In those cases you will not want to create systematically all those class… Unless you have a lot more time available than I do

Defining the Table Name and Schema

Declare the database table for which this class is defined, using the protected variable $_name. This is a string, and must contain the name of the table spelled as it appears in the database.

Example : Declaring a table class with explicit table name

class Bugs extends Zend_Db_Table_Abstract

{

 protected $_name = 'bugs';

}

If you don’t specify the table name, it defaults to the name of the class. If you rely on this default, the class name must match the spelling of the table name as it appears in the database. My suggestion : don’t do that!

Defining the Table Primary Key

Every table must have a primary key. You can declare the column for the primary key using the protected variable $_primary. This is either a string that names the single column for the primary key, or else it is an array of column names if your primary key is a compound key.

Example of specifying the primary key

class Bugs extends Zend_Db_Table_Abstract

{

 protected $_name = 'bugs';

 protected $_primary = 'bug_id';

}

If you don’t specify the primary key, Zend_Db_Table_Abstract tries to discover the primary key based on
the information provided by the describeTable() method.

Setting a Default Database Adapter

The second way to provide a database adapter to a Table class is by declaring an object of type Zend_Db_Adapter_Abstract to be a default database adapter for all subsequent instances of Tables in your application. You can do this with the static method Zend_Db_Table_Abstract::setDefaultAdapter(). The argument is an object of type Zend_Db_Adapter_Abstract.

Example of constructing a Table using a the Default Adapter

$db = Zend_Db::factory('PDO_MYSQL', $options);

Zend_Db_Table_Abstract::setDefaultAdapter($db);

// Later...

$table = new Bugs();

It can be convenient to create the database adapter object in a central place of your application, such as the bootstrap, and then store it as the default adapter. This gives you a means to ensure that the adapter instance is the same throughout your application. However, setting a default adapter is limited to a single adapter instance.

Storing a Database Adapter in the Registry

The third way to provide a database adapter to a Table class is by passing a string in the options array, also identified by the ‘db’ key. The string is used as a key to the static Zend_Registry instance, where the entry at that key is an object of type Zend_Db_Adapter_Abstract.

Example of constructing a Table using a Registry key

$db = Zend_Db::factory('PDO_MYSQL', $options);

Zend_Registry::set('my_db', $db);

// Later...

$table = new Bugs(array('db' => 'my_db'));

Like setting the default adapter, this gives you the means to ensure that the same adapter instance is used throughout your application. Using the registry is more flexible, because you can store more than one adapter instance. A given adapter instance is specific to a certain RDBMS brand and database instance. If your application needs access to multiple databases or even multiple database brands, then you need to use multiple adapters.

Inserting Rows to a Table

You can use the Table object to insert rows into the database table on which the Table object is based. Use the insert() method of your Table object. The argument is an associative array, mapping column names to values.

Example of inserting to a Table

$table = new Bugs();

$data = array(

 'created_on'      => '2007-03-22',

 'bug_description' => 'Something wrong',

 'bug_status'      => 'NEW'

);

$table->insert($data);

Updating Rows in a Table

You can update rows in a database table using the update method of a Table class. This method takes two arguments: an associative array of columns to change and new values to assign to these columns; and an SQL expression that is used in a WHERE clause, as criteria for the rows to change in the UPDATE operation.

Example of updating rows in a Table

$table = new Bugs();

$data = array(

 'updated_on'      => '2007-03-23',

 'bug_status'      => 'FIXED'

);

$where = $table->getAdapter()->quoteInto('bug_id = ?', 1234);

$table->update($data, $where);

Since the table update() method proxies to the database adapter update() method, the second argument can be an array of SQL expressions. The expressions are combined as Boolean terms using an AND operator.

Note
The values and identifiers in the SQL expression are not quoted for you. If you have values or identifiers that require quoting, you are responsible for doing this. Use the quote(), quoteInto(), and quoteIdentifier() methods of the database adapter.

Deleting Rows from a Table

You can delete rows from a database table using the delete() method. This method takes one argument, which is an SQL expression that is used in a WHERE clause, as criteria for the rows to delete.

Example of deleting rows from a Table

$table = new Bugs();

$where = $table->getAdapter()->quoteInto('bug_id = ?', 1235);

$table->delete($where);

The second argument can be an array of SQL expressions. The expressions are combined as Boolean terms
using an AND operator. Since the table delete() method proxies to the database adapter delete() method, the second argument can be an array of SQL expressions. The expressions are combined as Boolean terms using an AND operator.

Finding Rows by Primary Key

You can query the database table for rows matching specific values in the primary key, using the find() method. The first argument of this method is either a single value or an array of values to match against the primary key of the table.

Example of finding rows by primary key values

$table = new Bugs();
// Find a single row
// Returns a Rowset
$rows = $table->find(1234);
// Find multiple rows
// Also returns a Rowset
$rows = $table->find(array(1234, 5678));

Next : the Select API

Spreadsheets importation, online managing, multi-user sharing and easy publication around the web, the feature list is pretty impressive!

You can sign up for free for a limited time as a beta tester right here »

Have you tried it? I’d like to have your opinion about it and any idea how it could be used at ByTheOwner, thanks!

This morning, I had the chance to assist to Francisco Tolmasky’s (280 North) workshop called “How to build advanced web apps with Cappuccino and Objective-J”. It was an absolutely great demo and I’m actually very excited about using this framework for incoming projects at ByTheOwner. However, it was pretty technical, so I’ll have to play with the code and the demos in the following days to capture and be able to communicate what I’ve learned here. So, i’ll try to keep the blog updated with my upcoming experimentations.

« Cappuccino is an open source framework that makes it easy to build desktop-caliber applications that run in a web browser. »

This would be the “one sentence definition” for this framework according to the 280north website and I think it’s perfectly good. However, you need to understand what would be a “desktop-caliber application”. We’re talking about web apps like google maps, google docs, 280slides.com, etc… Web apps that you actually work and do something with. If you have a content oriented website (the Times for example) then Cappuccino isn’t a framework you should use to create your website, it’s simply not made/designed for that.

Here are finally a couple of highlights and notes about the framework I took during the workshop. I also include a video of a previous conference I found the 280north’s blog.

Very Quick Highlights about using Cappuccino and Objective-J

• You’ll never have to worry about layout cross-browser inconsistencies
• Built in support for the basic “OS” fonctions like undo, redo, save, open, new, autosave, etc… All things that people are not used to find in webapps nowadays and that makes it so much more usable and “wow-able”
• Uses a strict javascript superset that gives you compatibility with any regular javascript
• The visual controllers give you an incredible control over the way elements need to behave when the window is changing size
• A whole bunch of advanced form elements are available for you with almost no work at all (very cool color well for example…)
• The form elements can be skinned and will look very nice in all browser
• nib2cib provides an interface builder (wysiwyg style) application to help you design your cappuccino web app
• and many many many more…

Visit cappuccino.org for more information, demos and tutorials »

Time to get some sleep before FOWA’s day #2…

Frédérick Dubois
ByTheOwner.com / DuProprio.com

It reveals some very interesting (even fascinating) facts about technology’s evolution in the past years :

A meeting of the masterminds behind your favorite real estate website has given rise to a revolutionary idea!

This new feature is in the login page “Your Account”. A small checkbox labeled “Remember me on this computer” has appeared just below the password field. When you click this box and you log on, a Hi-Tech-Technology ensures that you to stay connected to the website for the next 30 days without having to re-enter your username and password.

However, if you click the “disconnect”, you will then “disconnect”…

Welcome to the suggestion box!

This new section, dedicated to our visitors, is online since yesterday. We’ve been using it for a couple of weeks with suggestions from our staff and got pretty interesting results, so we decided to open it up to the visitors! Here is how it works :

Vote, comment and suggest new ideas!

When you open the suggestion box, you see a list of the last suggestions and comments visitors made. Anybody can read the suggestions but you need to be connected in order to interact with the box. (creating your user account is quick and free!)

If you click on a suggestion’s title, you’ll be able to read it’s description and comments. You’ll also see a + and a – button, allowing you to vote for the current idea in a single click. Back to the list, an icon will show you easily wich suggestions were voted or not.

Under the description and visitors comments, you’ll find a link allowing you to post a comment about the current suggestion.
Under the Current Suggestions and Completed tabs, you’ll find the ideas that came out of the box and got on our desks.
PS : This is where you need to look if you want to start a fsbo website and need fresh ideas!

What’s great about this new tool, is that we’ll be able to focus our development efforts on features that really matter to our customer. Listening to the visitors and letting them judge what’s more important for themselves will surely result in great ideas.
So! Go ideas?

I really don’t know if it’s a bug or a very brilliant ad, for I’m sure it’s clicked a lot!

Read the article »

RSS feeds are soooo cool ! But… why?

A RSS feed (Really Simple Syndication), is a XML file which a Web site diffuses in order to spread easily its contents. From the feed’s url, one can daily consult the new contents of the site without being forced to go on the website. Okay… so what? Why wouldn’t you want to go to the website?

Subscribing to RSS feeds becomes interesting from the moment you register several different rss feeds. With this intention, you will have to use a RSS reader in which you will add rss feeds that you wish to follow. You could thus add to your reader various blogists whom you would like to consult daily, 2 or 3 news sites, AND of course, your ByTheOwner Alerts RSS feeds.

In only one glance, you will be able to see all that is new in your favorite websites. And if one of your RSS feed loses its interest, hop, you simply erase it of the reader. Easier than unsubscribing to most of the emailing lists out there!

Many search engines (like Yahoo! and Google for example) also offer aggregation functions for your RSS feeds directly on their home page. I also strongly suggest that you give a try to NetVibes and Google Reader, two online tools that perfectly do the job and which, of course, are free!

Frédérick Dubois, Programmer
ByTheOwner.com / DuProprio.com

Advanced search

This page’s layout has been re-designed so that you can get a quick look of all the parameters chosen without having to scroll down. Instead of displaying a long list of checkboxes for the cities and types of properties, we’ve decided to roll back and use a control we had in the past : The available cities are displayed in a list-box. The visitor then clicks on the cities he’s interested in and sends them to a second list-box, using arrow buttons. Note that you can send multiple properties using the CTRL and SHIFT buttons or by clicking and dragging your mouse over the cities.

A keyword field was also added just below the options so you can be even more specific in your search. Simply type in the words you search for, separated by spaces and click on the search button.

Google Map search

The map search also received a lot of upgrades.

The provinces, regions, and cities markers were all replaced by a red target icon whose size represents the number of properties available in it. Keeping the same marker icon for those 3 steps makes it clearer and more intuitive to use.

The posted markers adjust themselves now automatically according to the zoom level and the map movements. In the past, it was necessary to click on the markers to enter a province, then in an area, a city, etc… Now, if a visitor uses directly the google maps controls to explore the map, the areas, cities or properties are displayed automatically.

A Jump to city field above the map makes it possible for visitors to quickly position the map on a city of their choice. Simply type in the city name and push Enter.

The filters you can apply to the map are now available under the button Modify my search criteria.

We have also decided to double the size of the map. This makes the map a lot more interesting to use.

Finally, a maximum of information on the properties is now available directly in the map. When you rollover a property, you get it’s photograph, price and city. When you click on the marker, an infoWindow containing part of the listing, the 320×240 pictures, the owner remarks and contact information opens. The visitor does not have to go back and forth any more from the map to a listing, the map, the listing, the map…

Also, in each search mode, a navigation bar allows the user to go to the other modes quickly in order to use that one that is appropriate to his needs.

Your comments?

Frédérick Dubois, ByTheOwner.com
Web development and programming