Thursday, March 27, 2014

How to do Database design in mySQL PhPmyAdmin step by step BIT

Database design basics

A properly designed database provides you with access to up-to-date, accurate information. Because a correct design is essential to achieving your goals in working with a database, investing the time required to learn the principles of good design makes sense. In the end, you are much more likely to end up with a database that meets your needs and can easily accommodate change.
This article provides guidelines for planning a database. You will learn how to decide what information you need, how to divide that information into the appropriate tables and columns, and how those tables relate to each other. You should read this article before you create your first database.
In this article

Some database terms to know

Microsoft Office Access 2007 organizes your information into tables: lists of rows and columns reminiscent of an accountant’s pad or a Microsoft Office Excel 2007 worksheet. In a simple database, you might have only one table. For most databases you will need more than one. For example, you might have a table that stores information about products, another table that stores information about orders, and another table with information about customers.
Image depicting three tables in datasheets
Each row is also called a record, and each column, is also called a field. A record is a meaningful and consistent way to combine information about something. A field is a single item of information — an item type that appears in every record. In the Products table, for instance, each row or record would hold information about one product. Each column or field holds some type of information about that product, such as its name or price.
Top of Page Top of Page

What is good database design?

Certain principles guide the database design process. The first principle is that duplicate information (also called redundant data) is bad, because it wastes space and increases the likelihood of errors and inconsistencies. The second principle is that the correctness and completeness of information is important. If your database contains incorrect information, any reports that pull information from the database will also contain incorrect information. As a result, any decisions you make that are based on those reports will then be misinformed.
A good database design is, therefore, one that:
  • Divides your information into subject-based tables to reduce redundant data.
  • Provides Access with the information it requires to join the information in the tables together as needed.
  • Helps support and ensure the accuracy and integrity of your information.
  • Accommodates your data processing and reporting needs.
Top of Page Top of Page

The design process

The design process consists of the following steps:
  • Determine the purpose of your database
This helps prepare you for the remaining steps.
  • Find and organize the information required
Gather all of the types of information you might want to record in the database, such as product name and order number.
  • Divide the information into tables
Divide your information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.
  • Turn information items into columns
Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.
  • Specify primary keys
Choose each table’s primary key. The primary key is a column that is used to uniquely identify each row. An example might be Product ID or Order ID.
  • Set up the table relationships
Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
  • Refine your design
Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed.
  • Apply the normalization rules
Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.
Top of Page Top of Page

Determining the purpose of your database

It is a good idea to write down the purpose of the database on paper — its purpose, how you expect to use it, and who will use it. For a small database for a home based business, for example, you might write something simple like "The customer database keeps a list of customer information for the purpose of producing mailings and reports." If the database is more complex or is used by many people, as often occurs in a corporate setting, the purpose could easily be a paragraph or more and should include when and how each person will use the database. The idea is to have a well developed mission statement that can be referred to throughout the design process. Having such a statement helps you focus on your goals when you make decisions.
Top of Page Top of Page

Finding and organizing the required information

To find and organize the information required, start with your existing information. For example, you might record purchase orders in a ledger or keep customer information on paper forms in a file cabinet. Gather those documents and list each type of information shown (for example, each box that you fill in on a form). If you don't have any existing forms, imagine instead that you have to design a form to record the customer information. What information would you put on the form? What fill-in boxes would you create? Identify and list each of these items. For example, suppose you currently keep the customer list on index cards. Examining these cards might show that each card holds a customers name, address, city, state, postal code and telephone number. Each of these items represents a potential column in a table.
As you prepare this list, don’t worry about getting it perfect at first. Instead, list each item that comes to mind. If someone else will be using the database, ask for their ideas, too. You can fine-tune the list later.
Next, consider the types of reports or mailings you might want to produce from the database. For instance, you might want a product sales report to show sales by region, or an inventory summary report that shows product inventory levels. You might also want to generate form letters to send to customers that announces a sale event or offers a premium. Design the report in your mind, and imagine what it would look like. What information would you place on the report? List each item. Do the same for the form letter and for any other report you anticipate creating.
A person imagining a product inventory report
Giving thought to the reports and mailings you might want to create helps you identify items you will need in your database. For example, suppose you give customers the opportunity to opt in to (or out of) periodic e-mail updates, and you want to print a listing of those who have opted in. To record that information, you add a “Send e-mail” column to the customer table. For each customer, you can set the field to Yes or No.
The requirement to send e-mail messages to customers suggests another item to record. Once you know that a customer wants to receive e-mail messages, you will also need to know the e-mail address to which to send them. Therefore you need to record an e-mail address for each customer.
It makes good sense to construct a prototype of each report or output listing and consider what items you will need to produce the report. For instance, when you examine a form letter, a few things might come to mind. If you want to include a proper salutation — for example, the "Mr.", "Mrs." or "Ms." string that starts a greeting, you will have to create a salutation item. Also, you might typically start a letter with “Dear Mr. Smith”, rather than “Dear. Mr. Sylvester Smith”. This suggests you would typically want to store the last name separate from the first name.
A key point to remember is that you should break each piece of information into its smallest useful parts. In the case of a name, to make the last name readily available, you will break the name into two parts — First Name and Last Name. To sort a report by last name, for example, it helps to have the customer's last name stored separately. In general, if you want to sort, search, calculate, or report based on an item of information, you should put that item in its own field.
Think about the questions you might want the database to answer. For instance, how many sales of your featured product did you close last month? Where do your best customers live? Who is the supplier for your best-selling product? Anticipating these questions helps you zero in on additional items to record.
After gathering this information, you are ready for the next step.
Top of Page Top of Page

Dividing the information into tables

To divide the information into tables, choose the major entities, or subjects. For example, after finding and organizing information for a product sales database, the preliminary list might look like this:
Handwritten information items grouped into subjects
The major entities shown here are the products, the suppliers, the customers, and the orders. Therefore, it makes sense to start out with these four tables: one for facts about products, one for facts about suppliers, one for facts about customers, and one for facts about orders. Although this doesn’t complete the list, it is a good starting point. You can continue to refine this list until you have a design that works well.
When you first review the preliminary list of items, you might be tempted to place them all in a single table, instead of the four shown in the preceding illustration. You will learn here why that is a bad idea. Consider for a moment, the table shown here:
Image showing table that contains both products and suppliers
In this case, each row contains information about both the product and its supplier. Because you can have many products from the same supplier, the supplier name and address information has to be repeated many times. This wastes disk space. Recording the supplier information only once in a separate Suppliers table, and then linking that table to the Products table, is a much better solution.
A second problem with this design comes about when you need to modify information about the supplier. For example, suppose you need to change a supplier's address. Because it appears in many places, you might accidentally change the address in one place but forget to change it in the others. Recording the supplier’s address in only one place solves the problem.
When you design your database, always try to record each fact just once. If you find yourself repeating the same information in more than one place, such as the address for a particular supplier, place that information in a separate table.
Finally, suppose there is only one product supplied by Coho Winery, and you want to delete the product, but retain the supplier name and address information. How would you delete the product record without also losing the supplier information? You can't. Because each record contains facts about a product, as well as facts about a supplier, you cannot delete one without deleting the other. To keep these facts separate, you must split the one table into two: one table for product information, and another table for supplier information. Deleting a product record should delete only the facts about the product, not the facts about the supplier.
Once you have chosen the subject that is represented by a table, columns in that table should store facts only about the subject. For instance, the product table should store facts only about products. Because the supplier address is a fact about the supplier, and not a fact about the product, it belongs in the supplier table.
Top of Page Top of Page

Turning information items into columns

To determine the columns in a table, decide what information you need to track about the subject recorded in the table. For example, for the Customers table, Name, Address, City-State-Zip, Send e-mail, Salutation and E-mail address comprise a good starting list of columns. Each record in the table contains the same set of columns, so you can store Name, Address, City-State-Zip, Send e-mail, Salutation and E-mail address information for each record. For example, the address column contains customers’ addresses. Each record contains data about one customer, and the address field contains the address for that customer.
Once you have determined the initial set of columns for each table, you can further refine the columns. For example, it makes sense to store the customer name as two separate columns: first name and last name, so that you can sort, search, and index on just those columns. Similarly, the address actually consists of five separate components, address, city, state, postal code, and country/region, and it also makes sense to store them in separate columns. If you want to perform a search, filter or sort operation by state, for example, you need the state information stored in a separate column.
You should also consider whether the database will hold information that is of domestic origin only, or international, as well. For instance, if you plan to store international addresses, it is better to have a Region column instead of State, because such a column can accommodate both domestic states and the regions of other countries/regions. Similarly, Postal Code makes more sense than Zip Code if you are going to store international addresses.
The following list shows a few tips for determining your columns.
  • Don’t include calculated data
In most cases, you should not store the result of calculations in tables. Instead, you can have Access perform the calculations when you want to see the result. For example, suppose there is a Products On Order report that displays the subtotal of units on order for each category of product in the database. However, there is no Units On Order subtotal column in any table. Instead, the Products table includes a Units On Order column that stores the units on order for each product. Using that data, Access calculates the subtotal each time you print the report. The subtotal itself should not be stored in a table.
  • Store information in its smallest logical parts
You may be tempted to have a single field for full names, or for product names along with product descriptions. If you combine more than one kind of information in a field, it is difficult to retrieve individual facts later. Try to break down information into logical parts; for example, create separate fields for first and last name, or for product name, category, and description.
List of information items during the design process
Once you have refined the data columns in each table, you are ready to choose each table's primary key.
Top of Page Top of Page

Specifying primary keys

Each table should include a column or set of columns that uniquely identifies each row stored in the table. This is often a unique identification number, such as an employee ID number or a serial number. In database terminology, this information is called the primary key of the table. Access uses primary key fields to quickly associate data from multiple tables and bring the data together for you.
If you already have a unique identifier for a table, such as a product number that uniquely identifies each product in your catalog, you can use that identifier as the table’s primary key — but only if the values in this column will always be different for each record. You cannot have duplicate values in a primary key. For example, don’t use people’s names as a primary key, because names are not unique. You could easily have two people with the same name in the same table.
A primary key must always have a value. If a column's value can become unassigned or unknown (a missing value) at some point, it can't be used as a component in a primary key.
You should always choose a primary key whose value will not change. In a database that uses more than one table, a table’s primary key can be used as a reference in other tables. If the primary key changes, the change must also be applied everywhere the key is referenced. Using a primary key that will not change reduces the chance that the primary key might become out of sync with other tables that reference it.
Often, an arbitrary unique number is used as the primary key. For example, you might assign each order a unique order number. The order number's only purpose is to identify an order. Once assigned, it never changes.
If you don’t have in mind a column or set of columns that might make a good primary key, consider using a column that has the AutoNumber data type. When you use the AutoNumber data type, Access automatically assigns a value for you. Such an identifier is factless; it contains no factual information describing the row that it represents. Factless identifiers are ideal for use as a primary key because they do not change. A primary key that contains facts about a row — a telephone number or a customer name, for example — is more likely to change, because the factual information itself might change.

Image showing Products table with primary key field.
Callout 1 A column set to the AutoNumber data type often makes a good primary key. No two product IDs are the same.

In some cases, you may want to use two or more fields that, together, provide the primary key of a table. For example, an Order Details table that stores line items for orders would use two columns in its primary key: Order ID and Product ID. When a primary key employs more than one column, it is also called a composite key.
For the product sales database, you can create an AutoNumber column for each of the tables to serve as primary key: ProductID for the Products table, OrderID for the Orders table, CustomerID for the Customers table, and SupplierID for the Suppliers table.
Image showing information items during design process
Top of Page Top of Page

Creating the table relationships

Now that you have divided your information into tables, you need a way to bring the information together again in meaningful ways. For example, the following form includes information from several tables.

The Orders form
Callout 1 Information in this form comes from the Customers table...
Callout 2 ...the Employees table...
Callout 3 ...the Orders table...
Callout 4 ...the Products table...
Callout 5 ...and the Order Details table.

Access is a relational database management system. In a relational database, you divide your information into separate, subject-based tables. You then use table relationships to bring the information together as needed.
Top of Page Top of Page

Creating a one-to-many relationship

Consider this example: the Suppliers and Products tables in the product orders database. A supplier can supply any number of products. It follows that for any supplier represented in the Suppliers table, there can be many products represented in the Products table. The relationship between the Suppliers table and the Products table is, therefore, a one-to-many relationship.
One to many conceptual
To represent a one-to-many relationship in your database design, take the primary key on the "one" side of the relationship and add it as an additional column or columns to the table on the "many" side of the relationship. In this case, for example, you add the Supplier ID column from the Suppliers table to the Products table. Access can then use the supplier ID number in the Products table to locate the correct supplier for each product.
The Supplier ID column in the Products table is called a foreign key. A foreign key is another table’s primary key. The Supplier ID column in the Products table is a foreign key because it is also the primary key in the Suppliers table.
List of information items during the design process
You provide the basis for joining related tables by establishing pairings of primary keys and foreign keys. If you are not sure which tables should share a common column, identifying a one-to-many relationship ensures that the two tables involved will, indeed, require a shared column.
Top of Page Top of Page

Creating a many-to-many relationship

Consider the relationship between the Products table and Orders table.
A single order can include more than one product. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. And for each record in the Products table, there can be many records in the Orders table. This type of relationship is called a many-to-many relationship because for any product, there can be many orders; and for any order, there can be many products. Note that to detect many-to-many relationships between your tables, it is important that you consider both sides of the relationship.
The subjects of the two tables — orders and products — have a many-to-many relationship. This presents a problem. To understand the problem, imagine what would happen if you tried to create the relationship between the two tables by adding the Product ID field to the Orders table. To have more than one product per order, you need more than one record in the Orders table per order. You would be repeating order information for each row that relates to a single order — resulting in an inefficient design that could lead to inaccurate data. You run into the same problem if you put the Order ID field in the Products table — you would have more than one record in the Products table for each product. How do you solve this problem?
The answer is to create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. You insert the primary key from each of the two tables into the third table. As a result, the third table records each occurrence or instance of the relationship.
A many-to-many relationship
Each record in the Order Details table represents one line item on an order. The Order Details table’s primary key consists of two fields — the foreign keys from the Orders and the Products tables. Using the Order ID field alone doesn’t work as the primary key for this table, because one order can have many line items. The Order ID is repeated for each line item on an order, so the field doesn’t contain unique values. Using the Product ID field alone doesn’t work either, because one product can appear on many different orders. But together, the two fields always produce a unique value for each record.
In the product sales database, the Orders table and the Products table are not related to each other directly. Instead, they are related indirectly through the Order Details table. The many-to-many relationship between orders and products is represented in the database by using two one-to-many relationships:
  • The Orders table and Order Details table have a one-to-many relationship. Each order can have more than one line item, but each line item is connected to only one order.
  • The Products table and Order Details table have a one-to-many relationship. Each product can have many line items associated with it, but each line item refers to only one product.
From the Order Details table, you can determine all of the products on a particular order. You can also determine all of the orders for a particular product.
After incorporating the Order Details table, the list of tables and fields might look something like this:
List of information items during the design process
Top of Page Top of Page

Creating a one-to-one relationship

Another type of relationship is the one-to-one relationship. For instance, suppose you need to record some special supplementary product information that you will need rarely or that only applies to a few products. Because you don't need the information often, and because storing the information in the Products table would result in empty space for every product to which it doesn’t apply, you place it in a separate table. Like the Products table, you use the ProductID as the primary key. The relationship between this supplemental table and the Product table is a one-to-one relationship. For each record in the Product table, there exists a single matching record in the supplemental table. When you do identify such a relationship, both tables must share a common field.
When you detect the need for a one-to-one relationship in your database, consider whether you can put the information from the two tables together in one table. If you don’t want to do that for some reason, perhaps because it would result in a lot of empty space, the following list shows how you would represent the relationship in your design:
  • If the two tables have the same subject, you can probably set up the relationship by using the same primary key in both tables.
  • If the two tables have different subjects with different primary keys, choose one of the tables (either one) and insert its primary key in the other table as a foreign key.
Determining the relationships between tables helps you ensure that you have the right tables and columns. When a one-to-one or one-to-many relationship exists, the tables involved need to share a common column or columns. When a many-to-many relationship exists, a third table is needed to represent the relationship.
Top of Page Top of Page

Refining the design

Once you have the tables, fields, and relationships you need, you should create and populate your tables with sample data and try working with the information: creating queries, adding new records, and so on. Doing this helps highlight potential problems — for example, you might need to add a column that you forgot to insert during your design phase, or you may have a table that you should split into two tables to remove duplication.
See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplication of data and, when you find any, alter your design to eliminate it.
As you try out your initial database, you will probably discover room for improvement. Here are a few things to check for:
  • Did you forget any columns? If so, does the information belong in the existing tables? If it is information about something else, you may need to create another table. Create a column for every information item you need to track. If the information can’t be calculated from other columns, it is likely that you will need a new column for it.
  • Are any columns unnecessary because they can be calculated from existing fields? If an information item can be calculated from other existing columns — a discounted price calculated from the retail price, for example — it is usually better to do just that, and avoid creating new column.
  • Are you repeatedly entering duplicate information in one of your tables? If so, you probably need to divide the table into two tables that have a one-to-many relationship.
  • Do you have tables with many fields, a limited number of records, and many empty fields in individual records? If so, think about redesigning the table so it has fewer fields and more records.
  • Has each information item been broken into its smallest useful parts? If you need to report, sort, search, or calculate on an item of information, put that item in its own column.
  • Does each column contain a fact about the table's subject? If a column does not contain information about the table's subject, it belongs in a different table.
  • Are all relationships between tables represented, either by common fields or by a third table? One-to-one and one-to- many relationships require common columns. Many-to-many relationships require a third table.

Refining the Products table

Suppose that each product in the product sales database falls under a general category, such as beverages, condiments, or seafood. The Products table could include a field that shows the category of each product.
Suppose that after examining and refining the design of the database, you decide to store a description of the category along with its name. If you add a Category Description field to the Products table, you have to repeat each category description for each product that falls under the category — this is not a good solution.
A better solution is to make Categories a new subject for the database to track, with its own table and its own primary key. You can then add the primary key from the Categories table to the Products table as a foreign key.
The Categories and Products tables have a one-to-many relationship: a category can include more than one product, but a product can belong to only one category.
When you review your table structures, be on the lookout for repeating groups. For example, consider a table containing the following columns:
  • Product ID
  • Name
  • Product ID1
  • Name1
  • Product ID2
  • Name2
  • Product ID3
  • Name3
Here, each product is a repeating group of columns that differs from the others only by adding a number to the end of the column name. When you see columns numbered this way, you should revisit your design.
Such a design has several flaws. For starters, it forces you to place an upper limit on the number of products. As soon as you exceed that limit, you must add a new group of columns to the table structure, which is a major administrative task.
Another problem is that those suppliers that have fewer than the maximum number of products will waste some space, since the additional columns will be blank. The most serious flaw with such a design is that it makes many tasks difficult to perform, such as sorting or indexing the table by product ID or name.
Whenever you see repeating groups review the design closely with an eye on splitting the table in two. In the above example it is better to use two tables, one for suppliers and one for products, linked by supplier ID.
Top of Page Top of Page

Applying the normalization rules

You can apply the data normalization rules (sometimes just called normalization rules) as the next step in your design. You use these rules to see if your tables are structured correctly. The process of applying the rules to your database design is called normalizing the database, or just normalization.
Normalization is most useful after you have represented all of the information items and have arrived at a preliminary design. The idea is to help you ensure that you have divided your information items into the appropriate tables. What normalization cannot do is ensure that you have all the correct data items to begin with.
You apply the rules in succession, at each step ensuring that your design arrives at one of what is known as the "normal forms." Five normal forms are widely accepted — the first normal form through the fifth normal form. This article expands on the first three, because they are all that is required for the majority of database designs.

First normal form

First normal form states that at every row and column intersection in the table there, exists a single value, and never a list of values. For example, you cannot have a field named Price in which you place more than one Price. If you think of each intersection of rows and columns as a cell, each cell can hold only one value.

Second normal form

Second normal form requires that each non-key column be fully dependent on the entire primary key, not on just part of the key. This rule applies when you have a primary key that consists of more than one column. For example, suppose you have a table containing the following columns, where Order ID and Product ID form the primary key:
  • Order ID (primary key)
  • Product ID (primary key)
  • Product Name
This design violates second normal form, because Product Name is dependent on Product ID, but not on Order ID, so it is not dependent on the entire primary key. You must remove Product Name from the table. It belongs in a different table (Products).

Third normal form

Third normal form requires that not only every non-key column be dependent on the entire primary key, but that non-key columns be independent of each other.
Another way of saying this is that each non-key column must be dependent on the primary key and nothing but the primary key. For example, suppose you have a table containing the following columns:
  • ProductID (primary key)
  • Name
  • SRP
  • Discount
Assume that Discount depends on the suggested retail price (SRP). This table violates third normal form because a non-key column, Discount, depends on another non-key column, SRP. Column independence means that you should be able to change any non-key column without affecting any other column. If you change a value in the SRP field, the Discount would change accordingly, thus violating that rule. In this case Discount should be moved to another table that is keyed on SRP.


eMail -
Call  +94 777 33 7279

Saturday, March 15, 2014

PHP MVC step by step

A Brief History of Model-View-Controller

Let’s go all the way back to the 70s. Man has been to the moon, the civil rights movement has been a huge success, and the counterculture revolution is in full swing. More importantly for us, computer science has now established itself as a legitimate field, not just the eccentric hobby of people with huge glasses and bigger beards. Some of the brightest minds in engineering and mathematics are collaborating in research labs at Stanford, Berkeley and the dozens of private companies like Xerox and HP in what would later become Silicon Valley.
It’s against this backdrop that a bunch of very talented programmers have gathered at the Learning Research Group (LRG) of Xerox PARC in Palo Alto, California (the current home of Apple). These programmers are Alan Kay, Dan Ingalls, Adele Goldberg, Ted Kaehler, and Scott Wallace, among others. Together, they help create a programming language they call ‘Smalltalk’. It’s already been a few years since the development of the C language at Bell Labs; there are already some software design standards in place. However, the invention of the Smalltalk programming language would affect virtually all these standards, and set the tone for the future of programming.
It was from this language that the Model-View-Controller pattern first emerged. Ted Kaehler is usually given credit for its development in 1978 in a paper titled ‘A note on DynaBook requirements’, though his first name for it was ‘Thing-Model-View-Set’. The aim of this MVC pattern was to bring the user ‘closer’ to the digital model that existed inside the computer program. In Kaehler’s own words, the “user was czar”, and the MVC helped mediate the way the user could interact with a software program.
If that sounds too abstract to you, don’t worry – you’ll have a firm understanding of MVC by the time we’re done.
What you should know for now is that MVC described the way a user could interact with a computer program. This would later go on to inform Graphical User Interfaces (GUIs), and thus, by proxy, pretty much all of computer science. If it wasn’t for Kaehler and his MVC pattern, you might still be typing commands into a terminal right now.

So What Exactly is a Model-View-Controller?

A Model-View-Controller is a pattern of software architecture. It controls how different applications interact with a user.
Here’s a fun fact: you’re looking at a MVC right now.
This web page is a perfect example of the MVC structure. Here, the HTML structure of this web page is the model, the CSS stylesheets that control its appearance are the ‘View’, and the browser that you use to interact with the web page is the ‘controller’.
Let’s look at this in a little more detail:
Model: The model is a representation of knowledge. That is, the model contains the actual data that has to be shown to the user. All data is held in a structured format. For example, the HTML on this web page holds the actual text that you see inside structured HTML <tags>.
View: The view is a visual representation of the model. The view informs the way data structured in the model will be made visible to the user. For example, the HTML may hold the text, but the colors, font size and font style information is actually held by independent CSS stylesheets – i.e. the view.
Controller: A controller is the environment that bridges the divide between the user and the system. This is the device that lets the user interact with the system. In the above examples, the browser is the controller that helps you interact with the web page.
One way of representing this is as follows:
Here’s a crude analogy: the muscles, bones and organs in your body are the ‘model’ that holds everything vital. The skin, hair, etc. are the ‘view’ that controls the outward representation of the muscles and bones (the model). Your senses are the ‘controller’ that helps the ‘model’ interact with the world.
Want to make web applications? Learn dynamic web programming with PHP in this course.


As a programmer, you’ll most likely to encounter MVC in a Ruby on Rails or Django project. However, it is also used in some PHP web frameworks, though the implementation isn’t nearly always uniform or in strict accordance with the theoretical foundation of MVC as laid down by Ted Kaehler.
CakePHP, which is one of the most popular PHP frameworks, uses an interpretation of the Ruby on Rails MVC pattern. If you download a copy of the framework, you’ll see the following directory structure in /app/:
Here, the model, view and controller are included in separate folders. This is what /view/ contains:
And /Controller/:
Here’s the MVC in CodeIgniter, another very popular PHP framework:
You can learn more about using Codeigniter in this course.
However, CakePHP and CodeIgniter’s approach isn’t pure MVC, for here, the view and the model can interact with each other. In a pure MVC, all interactions must be handled by the controller alone. Most frameworks – CakePHP included – tend to treat the View as little more than a CSS template that controls the way the web page/app looks. The MVC approach is typically muddled in most PHP frameworks, both because of a poor translation of the Ruby on Rails and other similar frameworks. You don’t necessarily need to use MVC in your programs – WordPress isn’t MVC compliant – but it certainly will improve your application design.
MVC is an abstract, theoretical topic that can be difficult to grasp for beginners. You can learn more about it in this course on PHP programming from scratch.
Ref -->

According to Wikipedia, Model – View – Controller (MVC) is an architectural pattern used in software engineering. Successful use of the pattern isolates business logic from user interface considerations, resulting in an application where it is easier to modify either the visual appearance of the application or the underlying business rules without affecting the other. In MVC, the model represents the information (the data) of the application; the view corresponds to elements of the user interface such as text, checkbox items, and so forth; and the controller manages the communication of data and the business rules used to manipulate the data to and from the model. In simpler words:
  1. Model handles all our database logic. Using the model we connect to our database and provide an abstraction layer.
  2. Controller represents all our business logic i.e. all $_REQUEST.
  3. View represents our presentation logic i.e HTML/Javascript code.
Lets dive right in
The Directory Structure
Directory Structure
Although we will not be a couple of directories mentioned above for this tutorial, we should have them in place for future expansion. Let me explain the purpose of each directory:
  • application – application specific code
  • config – database/server configuration
  • db – database backups
  • library – framework code
  • public – application specific js/css/images
  • scripts – command-line utilities
  • tmp – temporary data
Once we have our directory structure ready, let us understand a few coding conventions.
Coding Conventions
  • MySQL tables will always be lowercase and plural e.g. items, cars
  • Models will always be singular and first letter capital e.g. Item, Car
  • Controllers will always have “Controller” appended to them. e.g. ItemsController, CarsController
  • Views will have plural name followed by action name as the file. e.g. items/view.php, cars/buy.php
We first add .htaccess file in the root directory which will redirect all calls to the public folder
1.<IfModule mod_rewrite.c>
2.    RewriteEngine on
3.    RewriteRule    ^$    public/    [L]
4.    RewriteRule    (.*) public/$1    [L]
5. </IfModule>
We then add .htaccess file to our public folder which redirect all calls to index.php. Line 3 and 4 make sure that the path requested is not a filename or directory. Line 7 redirects all such paths to index.php?url=PATHNAME
  • 01.<IfModule mod_rewrite.c>
  • 02.RewriteEngine On
  • 03. 
  • 04.RewriteCond %{REQUEST_FILENAME} !-f
  • 05.RewriteCond %{REQUEST_FILENAME} !-d
  • 06. 
  • 07.RewriteRule ^(.*)$ index.php?url=$1 [PT,L]
  • 08. 
  • 09.</IfModule>
This redirection has many advantages-
a) we can use it for bootstrapping i.e. all calls go via our index.php except for images/js/cs.
b) we can use pretty/seo-friendly URLS
c) we have a single entry point
Now we add index.php to our public folder
4.define(‘ROOT’, dirname(dirname(__FILE__)));
6.$url = $_GET['url'];
8.require_once (ROOT . DS . ‘library’ . DS . ‘bootstrap.php’);
Notice that I have purposely not included the closing ?>. This is to avoid injection of any extra whitespaces in our output. For more, I suggest you view Zend’s coding style.
Our index.php basically set the $url variable and calls bootstrap.php which resides in our library directory.
Now lets view our bootstrap.php
3.require_once (ROOT . DS . ‘config’ . DS . ‘config.php’);
4.require_once (ROOT . DS . ‘library’ . DS . ‘shared.php’);
Yes these requires could be included directly in index.php. But have not been on purpose to allow future expansion of code.
Now let us have a look at shared.php, finally something that does some real work
03./** Check if environment is development and display errors **/
05.function setReporting() {
07.    error_reporting(E_ALL);
08.    ini_set(‘display_errors’,'On’);
09.} else {
10.    error_reporting(E_ALL);
11.    ini_set(‘display_errors’,'Off’);
12.    ini_set(‘log_errors’, ‘On’);
13.    ini_set(‘error_log’, ROOT.DS.’tmp’.DS.’logs’.DS.’error.log’);
17./** Check for Magic Quotes and remove them **/
19.function stripSlashesDeep($value) {
20.    $value = is_array($value) ? array_map(‘stripSlashesDeep’, $value) : stripslashes($value);
21.    return $value;
24.function removeMagicQuotes() {
25.if ( get_magic_quotes_gpc() ) {
26.    $_GET    = stripSlashesDeep($_GET   );
27.    $_POST   = stripSlashesDeep($_POST  );
28.    $_COOKIE = stripSlashesDeep($_COOKIE);
32./** Check register globals and remove them **/
34.function unregisterGlobals() {
35.    if (ini_get(‘register_globals’)) {
36.  $array = array(‘_SESSION’, ‘_POST’, ‘_GET’, ‘_COOKIE’, ‘_REQUEST’, ‘_SERVER’, ‘_ENV’, ‘_FILES’);
37.  foreach ($array as $value) {
38.      foreach ($GLOBALS[$value] as $key => $var) {
39.    if ($var === $GLOBALS[$key]) {
40.  unset($GLOBALS[$key]);
41.    }
42.      }
43.  }
44.    }
47./** Main Call Function **/
49.function callHook() {
50.    global $url;
52.    $urlArray = array();
53.    $urlArray = explode(“/”,$url);
55.    $controller = $urlArray[0];
56.    array_shift($urlArray);
57.    $action = $urlArray[0];
58.    array_shift($urlArray);
59.    $queryString = $urlArray;
61.    $controllerName = $controller;
62.    $controller = ucwords($controller);
63.    $model = rtrim($controller, ‘s’);
64.    $controller .= ‘Controller’;
65.    $dispatch = new $controller($model,$controllerName,$action);
67.    if ((int)method_exists($controller, $action)) {
68.  call_user_func_array(array($dispatch,$action),$queryString);
69.    } else {
70.  /* Error Generation Code Here */
71.    }
74./** Autoload any classes that are required **/
76.function __autoload($className) {
77.    if (file_exists(ROOT . DS . ‘library’ . DS . strtolower($className) . ‘.class.php’)) {
78.  require_once(ROOT . DS . ‘library’ . DS . strtolower($className) . ‘.class.php’);
79.    } else if (file_exists(ROOT . DS . ‘application’ . DS . ‘controllers’ . DS . strtolower($className) . ‘.php’)) {
80.  require_once(ROOT . DS . ‘application’ . DS . ‘controllers’ . DS . strtolower($className) . ‘.php’);
81.    } else if (file_exists(ROOT . DS . ‘application’ . DS . ‘models’ . DS . strtolower($className) . ‘.php’)) {
82.  require_once(ROOT . DS . ‘application’ . DS . ‘models’ . DS . strtolower($className) . ‘.php’);
83.    } else {
84.  /* Error Generation Code Here */
85.    }
Let me explain the above code briefly. The setReporting() function helps us display errors only when theDEVELOPMENT_ENVIRONMENT is true. The next move is to remove global variables and magic quotes. Another function that we make use of is __autoload which helps us load our classes automagically. Finally, we execute the callHook() function which does the main processing.
First let me explain how each of our URLs will look –
So callHook() basically takes the URL which we have received from index.php and separates it out as $controller, $action and the remaining as $queryString. $model is the singular version of $controller.
e.g. if our URL is, then
Controller is items
Model is item (corresponding mysql table)
View is delete
Action is delete
Query String is an array (1,first-item)
After the separation is done, it creates a new object of the class $controller.”Controller” and calls the method $action of the class.
Now let us create a few classes first namely our base Controller class which will be used as the base class for all our controllers, our Model class which will be used as base class for all our models.
First the controller.class.php:
02.class Controller {
04.    protected $_model;
05.    protected $_controller;
06.    protected $_action;
07.    protected $_template;
09.    function __construct($model, $controller, $action) {
11.  $this->_controller = $controller;
12.  $this->_action = $action;
13.  $this->_model = $model;
15.  $this->$model =&amp; new $model;
16.  $this->_template =&amp; new Template($controller,$action);
18.    }
20.    function set($name,$value) {
21.  $this->_template->set($name,$value);
22.    }
24.    function __destruct() {
25.      $this->_template->render();
26.    }
The above class is used for all communication between the controller, the model and the view (template class). It creates an object for the model class and an object for template class. The object for model class has the same name as the model itself, so that we can call it something like $this->Item->selectAll(); from our controller.
While destroying the class we call the render() function which displays the view (template) file.
Now let us look at our model.class.php
02.class Model extends SQLQuery {
03.    protected $_model;
05.    function __construct() {
07.  $this->connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);
08.  $this->_model = get_class($this);
09.  $this->_table = strtolower($this->_model).”s”;
10.    }
12.    function __destruct() {
13.    }
The Model class extends the SQLQuery class which basically is an abstraction layer for the mySQL connectivity. Depending on your requirements you can specify any other DB connection class that you may require.
Now let us have a look at the SQLQuery.class.php
03.class SQLQuery {
04.    protected $_dbHandle;
05.    protected $_result;
07.    /** Connects to database **/
09.    function connect($address, $account, $pwd, $name) {
10.  $this->_dbHandle = @mysql_connect($address, $account, $pwd);
11.  if ($this->_dbHandle != 0) {
12.      if (mysql_select_db($name, $this->_dbHandle)) {
13.    return 1;
14.      }
15.      else {
16.    return 0;
17.      }
18.  }
19.  else {
20.      return 0;
21.  }
22.    }
24.    /** Disconnects from database **/
26.    function disconnect() {
27.  if (@mysql_close($this->_dbHandle) != 0) {
28.      return 1;
29.  }  else {
30.      return 0;
31.  }
32.    }
34.    function selectAll() {
35.  $query = ‘select * from `’.$this->_table.’`';
36.  return $this->query($query);
37.    }
39.    function select($id) {
40.  $query = ‘select * from `’.$this->_table.’` where `id` = ”.mysql_real_escape_string($id).”’;
41.  return $this->query($query, 1);
42.    }
44.    /** Custom SQL Query **/
46.    function query($query, $singleResult = 0) {
48.  $this->_result = mysql_query($query, $this->_dbHandle);
50.  if (preg_match(“/select/i”,$query)) {
51.  $result = array();
52.  $table = array();
53.  $field = array();
54.  $tempResults = array();
55.  $numOfFields = mysql_num_fields($this->_result);
56.  for ($i = 0; $i < $numOfFields; ++$i) {
57.      array_push($table,mysql_field_table($this->_result, $i));
58.      array_push($field,mysql_field_name($this->_result, $i));
59.  }
61.      while ($row = mysql_fetch_row($this->_result)) {
62.    for ($i = 0;$i < $numOfFields; ++$i) {
63.  $table[$i] = trim(ucfirst($table[$i]),”s”);
64.  $tempResults[$table[$i]][$field[$i]] = $row[$i];
65.    }
66.    if ($singleResult == 1) {
67.  mysql_free_result($this->_result);
68.  return $tempResults;
69.    }
70.    array_push($result,$tempResults);
71.      }
72.      mysql_free_result($this->_result);
73.      return($result);
74.  }
76.    }
78.    /** Get number of rows **/
79.    function getNumRows() {
80.  return mysql_num_rows($this->_result);
81.    }
83.    /** Free resources allocated by a query **/
85.    function freeResult() {
86.  mysql_free_result($this->_result);
87.    }
89.    /** Get error string **/
91.    function getError() {
92.  return mysql_error($this->_dbHandle);
93.    }
The SQLQuery.class.php is the heart of our framework. Why? Simply because it can really help us reduce our work while programming by creating an SQL abstraction layer. We will dive into an advanced version of SQLQuery.class.php in the next tutorial. For now lets just keep it simple.
The connect() and disconnect() functions are fairly standard so I will not get into too much detail. Let me specifically talk about the query class. Line 48 first executes the query. Let me consider an example. Suppose our SQL Query is something like:
SELECT table1.field1 , table1.field2, table2.field3, table2.field4 FROM table1,table2 WHERE ….
Now what our script does is first find out all the output fields and their corresponding tables and place them in arrays – $field and $table at the same index value. For our above example, $table and $field will look like
$field = array(field1,field2,field3,field4);
$table = array(table1,table1,table2,table2);
The script then fetches all the rows, and converts the table to a Model name (i.e. removes the plural and capitalizes the first letter) and places it in our multi-dimensional array and returns the result. The result is of the form $var['modelName']['fieldName']. This style of output makes it easy for us to include db elements in our views.
Now let us have a look at template.class.php
02.class Template {
04.    protected $variables = array();
05.    protected $_controller;
06.    protected $_action;
08.    function __construct($controller,$action) {
09.  $this->_controller = $controller;
10.  $this->_action = $action;
11.    }
13.    /** Set Variables **/
15.    function set($name,$value) {
16.  $this->variables[$name] = $value;
17.    }
19.    /** Display Template **/
21.    function render() {
22.  extract($this->variables);
24.      if (file_exists(ROOT . DS . ‘application’ . DS . ‘views’ . DS . $this->_controller . DS . ‘header.php’)) {
25.    include (ROOT . DS . ‘application’ . DS . ‘views’ . DS . $this->_controller . DS . ‘header.php’);
26.      } else {
27.    include (ROOT . DS . ‘application’ . DS . ‘views’ . DS . ‘header.php’);
28.      }
30.  include (ROOT . DS . ‘application’ . DS . ‘views’ . DS . $this->_controller . DS . $this->_action . ‘.php’);      
32.      if (file_exists(ROOT . DS . ‘application’ . DS . ‘views’ . DS . $this->_controller . DS . ‘footer.php’)) {
33.    include (ROOT . DS . ‘application’ . DS . ‘views’ . DS . $this->_controller . DS . ‘footer.php’);
34.      } else {
35.    include (ROOT . DS . ‘application’ . DS . ‘views’ . DS . ‘footer.php’);
36.      }
37.    }
The above code is pretty straight forward. Just one point- if it does not find header and footer in theview/controllerName folder then it goes for the global header and footer in the view folder.
Now all we have to add is a config.php in the config folder and we can begin creating our first model, view and controller!
03./** Configuration Variables **/
07.define(‘DB_NAME’, ‘yourdatabasename’);
08.define(‘DB_USER’, ‘yourusername’);
09.define(‘DB_PASSWORD’, ‘yourpassword’);
10.define(‘DB_HOST’, ‘localhost’);
Phew! Now let us create our first mini-todo application. We first create a database “todo” and execute the following SQL queries
1.CREATE TABLE `items` (
2.  `id` int(11) NOT NULL auto_increment,
3.  `item_name` varchar(255) NOT NULL,
4.  PRIMARY KEY  (`id`)
7.INSERT INTO `items` VALUES(1, ‘Get Milk’);
8.INSERT INTO `items` VALUES(2, ‘Buy Application’);
Once that is done, we add item.php to our model folder with the following contents
3.class Item extends Model {
Write it is empty, but will have more information when we expand our framework in Part 2.
Now create a file called itemscontroller.php in the controller folder
03.class ItemsController extends Controller {
05.    function view($id = null,$name = null) {
07.  $this->set(‘title’,$name.’ – My Todo List App’);
08.  $this->set(‘todo’,$this->Item->select($id));
10.    }
12.    function viewall() {
14.  $this->set(‘title’,'All Items – My Todo List App’);
15.  $this->set(‘todo’,$this->Item->selectAll());
16.    }
18.    function add() {
19.  $todo = $_POST['todo'];
20.  $this->set(‘title’,'Success – My Todo List App’);
21.  $this->set(‘todo’,$this->Item->query(‘insert into items (item_name) values (”.mysql_real_escape_string($todo).”)’));
22.    }
24.    function delete($id = null) {
25.  $this->set(‘title’,'Success – My Todo List App’);
26.  $this->set(‘todo’,$this->Item->query(‘delete from items where id = ”.mysql_real_escape_string($id).”’));
27.    }
Finally create a folder called items in the views folder and create the following files in it-
1.<h2><?php echo $todo['Item']['item_name']?></h2>
3.    <a class=”big” href=”../../../items/delete/<?php echo $todo['Item']['id']?>”>
4.    <span class=”item”>
5.    Delete this item
6.    </span>
7.    </a>
01.<form action=”../items/add” method=”post”>
02.<input type=”text” value=”I have to…” onclick=”this.value=”” name=”todo”> <input type=”submit” value=”add”>
05.<?php $number = 0?>
07.<?php foreach ($todo as $todoitem):?>
08.    <a class=”big” href=”../items/view/<?php echo $todoitem['Item']['id']?>/<?php echo strtolower(str_replace(” “,”-”,$todoitem['Item']['item_name']))?>”>
09.    <span class=”item”>
10.    <?php echo ++$number?>
11.    <?php echo $todoitem['Item']['item_name']?>
12.    </span>
13.    </a><br/>
14.<?php endforeach?>
1.<a class=”big” href=”../../items/viewall”>Todo successfully deleted. Click here to go back.</a>
1.<a class=”big” href=”../items/viewall”>Todo successfully added. Click here to go back.</a>
03.<title><?php echo $title?></title>
05..item {
10.input {
11.    color:#222222;
16.    color:black;
19. a {
20.    color:#222222;
25.    color:black;
26.    text-decoration:none;
30.a:hover {
31.    background-color:#BCFC3D;
33.h1 {
39.border-bottom:1px dotted #cccccc;
42.h2 {
53.<h1>My Todo-List App</h1>
Now assuming you have uploaded the directory structure to the todo folder, point your browser to:
Todo List App
Where do we go from here?
Firstly we have achieved a lot by completing this tutorial – we have been able to separate our presentation logic from our business logic and database logic. We have also been able to provide for pretty-urls and extensibility..
The above is part 1, the source can be download here. The following is the new version which we have implemented a simple e-commerce website consisting of categories, subcategories, products and tags (for products). The example will help you understand the various relationships between the tables and most of the new functionality provided by this part of the framework.
What’s New
The inflection configuration file enables us to use irregular words i.e. words which do not have a standard plural name. This file is used in conjunction with library/inflection.class.php
The routing configuration file enables us to specify default controller and action. We can also specify custom redirects using regular expressions. Currently I have specified only one redirect i.e. http://localhost/framework/admin/categories/view will become http://localhost/framework/admin/categories_view where admin is the controller and categories_view is the action. This will enable us to create an administration centre with pretty URLs. You can specify others as per your requirements.
The cache class is in its infancy. Currently there are two simple functions- set and get. The data is stored in a flat text-file in the cache directory. Currently only the describe function of the SQLQuery class uses this cache function.
The HTML class is used to aid the template class. It allows you to use a few standard functions for creating links, adding javascript and css. I have also added a function to convert links to tinyurls. This class can be used only in the views e.g. $html->includeJs(’generic.js’);
In the previous part, plural of words were created by adding only “s” to the word. However, for a more full-fledged version, we now use the inflection class with slight modifications. If you have a look at the class, it makes use of simple regular expressions. It would be nice to add a cache function to this class in future.
I have added a new variable called doNotRenderHeader which will enable you to not output headers for a particular action. This can be used in AJAX calls when you do not want to return the headers. It has to be called by the controller e.g. $this->doNotRenderHeader = 1;
library/vanillacontroller.class.php & vanillamodel.class.php
Pretty much unchanged, but I have added a prefix vanilla to them to emphasize on its simplicity library/sqlquery.class.php
The SQLQuery class is the heart of this framework. This class will enable you to use your tables as objects.
Let us understand the easy functions first – save() and delete()
The save() function must be used from the controller. The save() function can have two options- if an id is set, then it will update the entry; if it is not set, then it will create a new entry. For example let us consider the categories class i.e. application/controllers/categoriescontroller.php. We can have a function like the one below.
1.function new() {
2.   $this->Category->id = $_POST['id'];
3.   $this->Category->name = $_POST['name'];
4.   $this->Category->save();
If $this->Category->id = null; then it will create a new record in the categories table.
The delete() function enables you to delete a record from the table. A sample code could be as below. Although you should use POST instead of GET queries for deleting records. As a rule idempotent operations should use GET. Idempotent operations are those which do not change the state of the database (i.e. do not update/delete/insert rows or modify the database in anyway)
1.function delete($categoryId) {
2.   $this->Category->id = $categoryId;
3.   $this->Category->delete();
Now let us look at the search() function. I know it is a bit intimidating at first. But it is pretty straight forward after we break it down. During database design, we use the following convention:
1:1 Relationship
For a one is to one relationship, suppose we have two tables students and mentors and each student hasOne mentor, then in the students table we will have a field called ‘mentor_id’ which will store the id of the mentor from the mentors table.
1:Many Relationship
For a one is to many relationship, suppose we have two tables parents and children and each parent hasMany children, then in the children table we will have a field called ‘parent_id’ which will store the id of the parent from the parents table.
Many:Many Relationship
For a many is to many relationship, suppose we have two tables students and teachers and each student hasManyAndBelongsToMany teachers, then we create a new table called students_teachers with three fields: id, student_id and teacher_id. The naming convention for this table is alphabetical. i.e. if our tables are cars and animals, then the table should be named animals_cars and not cars_animals.
Now once we have created our database as per these conventions, we must tell our framework about their existence. Let us have a look at models/product.php.
1.class Product extends VanillaModel {
2.    var $hasOne = array(‘Category’ => ‘Category’);
3.    var $hasManyAndBelongsToMany = array(‘Tag’ => ‘Tag’);
The first Category is the alias and the second Category is the actual model. In most cases both will be the same. Let us consider the models/category.php where they are not.
3.class Category extends VanillaModel {
4.  var $hasMany = array(‘Product’ => ‘Product’);
5.  var $hasOne = array(‘Parent’ => ‘Category’);
Here each category has a parent category, thus our alias is Parent while model is Category. Thus we will have a field called parent_id in the categories table. To clearly understand these relationships, I suggest you create a couple of tables and test them out for yourself. In order to see the output, use code similar to the following in your controller.
1.function view() {
2.   $this->Category->id = 1;
3.   $this->Category->showHasOne();
4.   $this->Category->showHasMany();
5.   $this->Category->showHMABTM();
6.   $data = $this->Category->search();
7.   print_r($data);
Now let us try and understand the search() function. If there are no relationships, then the function simply does a select * from tableName (tableName is same as controllerName). We can influence this statement, by using the following commands:
where(’fieldName’,’value’) => Appends WHERE ‘fieldName’ = ‘value’
like(’fieldName’,’value’) => Appends WHERE ‘fieldName’ LIKE ‘%value%’
setPage(’pageNumber’) => Enables pagination and display only results for the set page number
setLimit(’fieldName’,’value’) => Allows you to modify the number of results per page if pageNumber is set. Its default value is the one set in config.php.
orderBy(’fieldName’,’Order’) => Appends ORDER BY ‘fieldName’ ASC/DESC
id = X => Will display only a single result of the row matching the id
Now let us consider when showHasOne() function has be called, then for each hasOne relationship, a LEFT JOIN is done (see line 91-99).
Now if showHasMany() function has been called, then for each result returned by the above query and for each hasMany relationship, it will find all those records in the second table which match the current result’s id (see line 150). Then it will push all those results in the same array. For example, if teachers hasMany students, then $this->Teacher->showHasMany() will search for teacher_id in the students table.
Finally if showHMABTM() function has been called, then for each result returned by the first query and for each hasManyAndBelongsToMany relationship, it will find all those records which match the current result’s id (see line 200-201). For example, if teachers hasManyAndBelongsToMany students, then $this->Teacher->showHMABTM() will search for teacher_id in students_teachers table.
On line 236, if id is set, then it will return a single result (and not an array), else it will return an array. The function then calls the clear() function which resets all the variables (line 368-380).
Now let us consider an example to enable pagination on products. The code in the controllers/productscontroller.php should look something similar to the following.
01.function page ($pageNumber = 1) {
02.  $this->Product->setPage($pageNumber);
03.  $this->Product->setLimit(’10′);
04.  $products = $this->Product->search();
05.  $totalPages = $this->Product->totalPages();
06.  $this->set(‘totalPages’,$totalPages);
07.  $this->set(‘products’,$products);
08.  $this->set(‘currentPageNumber’,$pageNumber);
Now our corresponding view i.e. views/products/page.php will be something like below.
01.<?php foreach ($products as $product):?>
03.<?php echo $product['Product']['name']?>
05.<?php endforeach?>
07.<?php for ($i = 1; $i <= $totalPages; $i++):?>
09.<?php if ($i == $currentPageNumber):?>
10.<?php echo $currentPageNumber?>
11.<?php else: ?>
12.<?php echo $html->link($i,’products/page/’.$i)?>
13.<?php endif?>
15.<?php endfor?>
Thus with a few lines of code we have enabled pagination on our products page with pretty URLs!
(/products/page/1, products/page/2 …)
If you look at the totalPages() function on line 384-397, you will see that it takes the existing query and strips of the LIMIT condition and returns the count. This count/limit gives us the totalPages.
Now suppose that we are in the categories controller and we want to implement a query on the products table. One way to implement this is using a custom query i.e. $this->Category->custom(‘select * from products where …’);
Alternatively, we can use the performAction function (line 49-57 in shared.php) to call an action of another controller. An example call in categoriescontroller.php would be something like below.
1.function view($categoryId = null, $categoryName = null) {
2.  $categories = performAction(‘products’,'findProducts’,array($categoryId,$categoryName));
And the corresponding code in productscontroller.php should be as below.
1.function findProducts ($categoryId = null, $categoryName = null) {
2.  $this->Product->where(‘category_id’,$categoryId);
3.  $this->Product->orderBy(‘name’);
4.  return $this->Product->search();
The above more or less sums up all the functionality of the SQLQuery class. You can easily extend this as per your requirements e.g. to cache results, add conditions to hasMany, hasOne, hasMABTM queries also etc.
I have also laid the foundation for implementing user registration functionality by specifying a beforeAction and afterAction function which will be executed for each controller action. This will enable us to call a function which checks whether the user cookie is set and is a valid user.
One more feature that I have implemented is to have an administration centre. For this purpose we create a dummy model called models/admin.php and set a variable called $abstract = true. This will tell our VanillaModel to not look for a corresponding table in the database. As stated before I have created a routing for admin/X/Y as admin/X_Y. Thus we can have URLs like admin/categories/delete/15 which will actually call the categories_delete(15) function in the controllers/admincontroller.php file.

Phon +94 777 337279