I was referenced in the WSJ article- Google’s iPhone tracking regarding my cross-domain cookies post.

For my technical audience, here is a detailed version of how my code was actually used.

So let us begin from scratch… what are cookies?

Cookies (data on your computer used to identify you) are stored whenever you visit a site. Usually, this data is used to keep you logged into a site or to figure out if you are a repeat visitor.

This is fine and in-order for the web to function in it’s current state, in many ways, essential.

In the above diagram, when you visit siteA.com, a cookie is set for siteA.com on your computer.

When you visit siteB.com, a cookie is set for siteB.com on your computer.

But siteB.com, CANNOT read the data from the cookies set in siteA.com and vice-versa. Thus siteB.com does not know if you ever visited siteA.com or what you did there.

Let’s introduce the ad-companies

Ad-companies started displaying ads on sites depending on the content of the page. So, if you are a webmaster, you add a line of JavaScript code and a neat looking advertisement is displayed on your site page.

But the information on the page is not enough to generate targeted ads. So the ad-companies wanted to know where you’ve been before.

Now here is where the privacy issue unfolds.

Tracking your moves

Inorder to track you, as you moved from one site to another (both displaying sites ads from the same ad-company), the companies need to store cookies.

The problem is that cookies set on one site CANNOT be read by another site. In other words, cookies set on siteA.com cannot be read by siteB.com.

So the work-around used is to store a third-party cookie (i.e. a cross-domain cookie). When a user visits siteA.com, a cookie is not only set for siteA.com but also for a common domain which is accessed by all sites that use the ad-company’s code.

Thus, siteA.com, siteB.com and all other sites displaying ads from adsite.com, store and access the same third-party cookie.

So when you visit siteA.com, a third-party cookie is created on adsite.com. When you visit siteB.com, the same cookie is read.

Thus adsite.com now knows that you have been to siteA.com before going to siteB.com. On the basis of this knowledge, a targeted ad can be shown. For an ad-company which provides ads to only two sites, this is not very useful. But imagine if you have 70% of the sites using your code.

Then, nearly most of the times, the same cookie can be read and your movements can be tracked as you browse through the internet. So now, the ad-company knows exact what kind of sites you are visiting and serve ads accordingly.

But why does the WSJ article only talk about Safari?

Safari, by default, does not allow third-party cookies to be created.

However, most major browsers- Firefox, Internet Explorer (using the correct header tags), Chrome etc. do ALLOW setting third-party cookies.

Where does your technique come in?

The technique I posted two years ago, allows setting third-party cookies for Safari as well. This helps in offering a consistent user experience irrespective of the browser used.

Note that other browsers already allow creation of third-party cookies. So Safari is the only exception.

How was this discovered?

A Stanford researcher, Jonathan Mayer found that many large companies like Google, MIG, PointRoll etc. used a variation of my technique to circumvent Safari’s policy.

Infact, Facebook’s official developer best practices article linked to my post (the page has since been removed).

You must remember that this issue affects Safari users ONLY.

Other browsers already allow third-party cookies and thus they CAN track you as they do NOT have any such policy.

How do I protect myself? Should I disable cookies?

NO. Do not disable cookies altogether. This will cause most of your sites (which have a login system) to stop working.

Third-party cookies also have a number of other uses like logging in users to third-party sites and are NOT always used for tracking. They cannot steal your data (a myth noted by some users in comments).

If you want, you can disable third-party cookies in browsers. Dennis O’Reilly has written a guide on how to disable third-party cookies for major browsers.

You may also want to use incognito/private browsing modes when using a public PC.

Questions/Suggestions?

If you have any questions about how this affects you or need further explanation, feel free to post a comment or contact me.

As your site grows, so do your chances of getting hacked. Most of these hacks are due to known exploits in existing open/closed source software. One of the biggest worries is a user getting full access to your site using a simple PHP upload.

So what’s the solution?

This is no silver bullet but if a user uploads any malicious files to your server, you should be able to quickly check what files have been modified/added.

PHP Exploit Scanner

The Exploit Scanner is a single PHP file which generates MD5 hash for all files of a particular software and then allows you to compare that with software you think has been modified.

Sample Usage

Lets say you want to check for any exploits in your WordPress installation

1. Download a fresh copy of WordPress from the official site (make sure you download correct version)
2. Upload WordPress to your server/localhost
3. Run exploitscanner.php?action=generate in the new WordPress folder (you do not need to install WordPress)
4. Check filehashes.php and verify output is correct (i.e. an array of files with their hashes)
5. Now upload exploitscanner.php and filehashes.php to your live WordPress folder
6. Run exploitscanner.php?action=scan to generate a list of modified/added files
7. Check output of scanresults.txt

Generating Hashes

Upload exploitscanner.php to the folder for which you want to generate file hashes and then point your browser to:

http://www.yoursite.com/untouchedsoftware/exploitscanner.php?action=generate

Only output is filehashes.php

Note: Be sure that the software is not already exploited. Ideally use a fresh copy from the software creators (make sure you check the version)

Scanning For Exploits

Upload exploitscanner.php to the folder for which you want to check file hashes and then point your browser to:

http://www.yoursite.com/hackedsoftware/exploitscanner.php?action=scan

Only output is scanresults.txt
Legend- F: New file | M: Modified file

First search for all .php files to see what is changed. If the file is tagged M, you can use a difference tool like WinMerge to find out what has changed.

Download

Download PHP Exploit Scanner free

License

Exploit Scanner is licensed under MIT license. Let me know if you make any interesting use of the script.

Comments/Suggestions?

If you would like to assist in creating a community where users can quickly download filehashes.php for their software then feel free to contact me using the form below.

Do let me know your suggestions on how we can improve this code or any other features you would like to add.

Future Updates

1. Improve recursive function to avoid time out on shared servers
2. Create a community where users can upload hashes for known software like phpBB, vBulletin etc.
3. Add GUI
4. Database integrity
5. Malicious code insertion in template files

Spread The Word

If you like what you are reading, then please help spread the word by re-tweeting, blogging or using the ShareThis button below. Thank you.

The Problem

Safari does not allow cross-domain cookies. In other words, if on X.com, you load an iFrame with contents of Y.com and set a cookie in the iFrame, Safari will not save the cookie. This problem also occurs in IE6/7 but can be resolved by sending a P3P header.

The Solution

I am not entirely sure why this works, but the cookie gets saved if a post is made to the iFrame. This solution relies of jQuery but can be adapted to any other.

Here is the code that goes on the remote domain:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js" type="text/javascript"></script>
<script>
var isSafari = (/Safari/.test(navigator.userAgent));
var firstTimeSession = 0;

function submitSessionForm() {
	if (firstTimeSession == 0) {
		firstTimeSession = 1;
		$("#sessionform").submit();
		setTimeout(processApplication(),2000);
  	}
}

if (isSafari) {
	$("body").append('<iframe id="sessionframe" name="sessionframe" onload="submitSessionForm()" src="http://www.yourdomain.com/blank.php" style="display:none;"></iframe><form id="sessionform" enctype="application/x-www-form-urlencoded" action="http://www.yourdomain.com/startsession.php" target="sessionframe" action="post"></form>');
} else {
	processApplication();
}

function processApplication() {
	alert('Session has been set. Now you can start your application!');
}
</script>

The contents for startsession.php would be as simple as:

<?php
header('P3P: CP="IDC DSP COR CURa ADMa OUR IND PHY ONL COM STA"');
session_start();

To make sure that your site is compatible with IE6/7, always output the following header:

<?php
header('P3P: CP="IDC DSP COR CURa ADMa OUR IND PHY ONL COM STA"');

Unfortunately, there is no way to find out when the browser has completed submitting the form. Thus, I have placed a delay of 2 seconds. This form needs to be submitted only once per user session. Then you can set your session data in PHP and it will be picked up by your remote domain. You can adapt the same example if you want to set cookies instead of starting a PHP session.

Comments/Suggestions?
Do let me know your suggestions on how we can improve this code.

Spread The Word
If you like what you are reading, then please help spread the word by re-tweeting, blogging or using the ShareThis button below. Thank you.

The lite version is an exact copy of the original version but all the files have been combined into only 4 files.

Acknowledgments

SQLBuddyLite is a lighter version of SQLBuddy by Calvin Lough

Why do you need a lite version?

A typical situation is where you have limited access to the user’s site say only FTP access and you need to verify the database structures. The easiest way to tackle the problem is to upload SQLBuddy and view the data structures.

The lite version makes it quicker to upload files. The lite version does not compromise on the functions; it only reduces the files drastically to only 4 files, thus speeding up remote site management.

Only English language is supported in the latest release.

Demonstration

For security reasons, there is no demonstration version available.

Screenshot

Requirements

1. PHP
2. mySQL
3. Apache mod-rewrite

Getting Started

Download and upload all the sqlbuddylite folder to your server

Download

SQLBuddyLite is hosted on Google Code
Direct download link for SQLBuddyLite

License

MIT-style license

Comments/Suggestions?

Do let me know your suggestions on how we can improve this code or any other features you would like to add.

Spread The Word

If you like what you are reading, then please help spread the word by re-tweeting, blogging or using the ShareThis button below. Thank you.

Features
1. Allow users to post questions and answers
2. Points system similar to StackOverflow
3. Ability to post an article as a knowledge-base (for corporates)
4. Ability to lock site to registered users only
5. Clean CSS layout
6. Works in all major browsers

Demonstration
Launch Qwench - StackOverflow Clone Demo

Screenshot

Requirements
1. PHP 4+
2. mySQL

Getting Started
Download and follow the instructions in README.txt file

Download
Qwench is hosted at GitHub

License
Qwench licence can be found in the LICENSE.txt file.

Comments/Suggestions?
Do let me know your suggestions on how we can improve this code or any other features you would like to add.

Spread The Word
If you like what you are reading, then please help spread the word by re-tweeting, blogging or using the ShareThis button below. Thank you.

Features
1. Create your own custom gestures
2. Create multiple areas where you can accept gestures
3. Visual feedback
4. Works in all major browsers

Acknowledgment
This script is a port of the mouse gesture recognition action script by Didier Brun.

Demonstration
Launch Fancy Gestures Demo

Screenshot

Requirements
1. jQuery
2. Walter Zorn’s VectorGraphics Library

Getting Started

Create a new html file with the following code. The function will return data i.e. the recognized character/symbol/name:

<div id="sample" style="border:1px solid black;position:relative;height:150px;width:150px;"></div>

<script type="text/javascript" src="wz_jsgraphics.js"></script>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="jquery.fancygestures.js"></script>

<script>

$(document).ready(function () {
	$('#sample').fancygestures(function (data) {
		alert(data);
	});
});

</script>

Customization

Please use the key below to generate a string of numbers for your gesture: e.g. L will be something like 2 then 0. The following letters are already mapped into the script:

Suppose you want to add a “CIRCLE” symbol with the sequence “432107654″, then simply edit jquery.fancygestures.js and add the following after line 7:

     gestures["CIRCLE"] = "432107654";

The above code will return “CIRCLE” as data in the function when you make a circle. However, make sure you do not have similar gestures, or you will get unpredictable results e.g. having same gesture for zero and letter O.

Download
Download Fancy Gestures

License
Fancy Gestures is licensed under MIT license. Let me know if you make any interesting use of the script.

Comments/Suggestions?
Do let me know your suggestions on how we can improve this code or any other features you would like to add.

Spread The Word
If you like what you are reading, then please help spread the word by re-tweeting, blogging or using the ShareThis button below. Thank you.

Why would you need such a program?
1. You run a small company/group which you would like twitter users to follow
2. You would like twitter users to follow a single brand (twitter account) and not separate multiple users
3. You would like a simple slick interface to enable your users to post to your collaborative twitter account
4. You would like to hide your real twitter account password from your users
5. You would like to automatically add “-username” to all tweets by a user
6. You would like to host this service on your own server

Demonstration
Please login with one of the following username/password combination:
johndoe/johndoe
janedoe/janedoe
babydoe/babydoe

Click here to launch Collabtweet demo

For demonstration purposes, I am using a dummy twitter account- http://www.twitter.com/collabtweet

Screenshots

Download
Collabtweet - Multiple Users, Single Twitter Account

Requirements
Web server capable of running PHP

Configuration
Simple edit config.php and modify the users which can access your account and your twitter username and password.

<?php

// Username => Password
// Username will be used as nickname
$users = array(
"USER1" => "PASS1",
"USER2" => "PASS2",
"USER3" => "PASS3"
);

$twitterUser = "YOURTWITTERUSERNAME";
$twitterPass = "YOURTWITTERPASSWORD";

Future Updates
1. Ability to manage users using a web interface

Comments/Suggestions?
Do let me know your suggestions on how we can improve this code or any other features you would like to add.

Spread The Word
If you like what you are reading, then please help spread the word by re-tweeting, blogging or using the ShareThis button below. Thank you.

Features
1. Gmail style bottom right display of chat boxes
2. Keeps chat boxes open and stores state (data) even when pages are browsed/refreshed similar to Facebook
3. Displays “Sent at…” after 3 minutes of inactivity
4. Displays “X says…” & blinks chat boxes when window is not in focus
5. Minimize and close chat boxes
6. Auto-resize of text input box
7. Auto-scrolling of chat text
8. Auto-back-off polling policy (hits the server less-often when chat activity is low)
9. Extremely simple to integrate into existing site

Demo
Please load the following links in different browsers otherwise it wont work:
(Note that due to users trying out the chat links below at the same time, it might work slightly erratically because the username is actually not supposed to be used by multiple users at the same time)

Sample Chat User One
Sample Chat User Two
Sample Chat User Three

Getting Started
First download the module (link below)

The script does not manage rosters (i.e. which users are online etc.), it only enables users to chat with each other. I assume your current application already provides that functionality. You must also make sure that $_SESSION['username'] is being set when your website session begins. You will understand the logic better after you try the sample files that I have provided.

You must first create a mySQL table as below (or import db.txt)

CREATE TABLE `chat` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `from` VARCHAR(255) NOT NULL DEFAULT '',
  `to` VARCHAR(255) NOT NULL DEFAULT '',
  `message` TEXT NOT NULL,
  `sent` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `recd` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
 INDEX `to` (`to`),
 INDEX `from` (`from`)
)
ENGINE = InnoDB;

Add the following scripts to your page template

<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript" src="js/chat.js"></script>

Add the following CSS to your page template

<link type="text/css" rel="stylesheet" media="all" href="css/chat.css" />
<link type="text/css" rel="stylesheet" media="all" href="css/screen.css" />

<!--[if lte IE 7]>
<link type="text/css" rel="stylesheet" media="all" href="css/screen_ie.css" />
<![endif]-->

Now in your list of users online, add “javascript:chatWith(’USERNAME’);” function where USERNAME is the username for that particular user who he/she wants to chat with.

Once that is done, edit chat.php and set your database parameters and try your website.

For better understanding, load 3 different browsers (internet explorer, firefox, safari) and point them to samplea.php, sampleb.php and samplec.php.

Click on “chat with john doe” link and watch the chat functionality come alive!

Inorder to integrate your existing website, you must place all your content between the “main_container” div tag.

Browser Compatibility
1. Firefox 2+
2. Internet Explorer 6+
3. Safari 2+
4. Opera 9+

Licensing
This chat script can be used for free under GPL-style license for non-commercial purposes. For commercial purposes, please purchase a license.

Download

jQuery Chat Module. If you would like a more full featured inline chat software, have a look at CometChat

Purchase

Your purchase includes free updates for life! If you have any queries, please contact me using the form below.

Single Domain License $49.00
5 Domains License $199.00
10 Domains License $349.00
Unlimited Domains License $499.00

Future Updates
1. Gmail style pop-out functionality
2. Gmail style emoticons
3. Gmail style video chat
4. Comet integration
5. Jabber integration

Comments/Suggestions?
Do let me know your suggestions on how we can improve this code or any other features you would like to add.

Spread The Word
If you like what you are reading, then please help spread the word by re-tweeting, blogging and dzone upvoting or use the ShareThis button below. Thank you.

If you do not have subversion installed already, you can borrow parts from the subversion + trac installation tutorial. Once you have subversion installed, its important to integrate it in your web development workflow. Simply running SVN commit everytime you complete a feature is not enough. So let us see how we can fully integrate subversion into our web development. This workflow also includes database versioning (using a simple PHP script).

The Solution

Before we begin, scroll down and download the database backup and version control script that I have written in PHP.

The Bulletproof Method

This process is slightly lengthy and complicated, but once you get the hang of it, your web development process will be greatly streamlined. Also, you will be able to test your deployed versions before making them live. This method allows you to switch between versions really really quickly. It is advised for all heavy traffic websites.

Assumption: You have a heavy traffic website. You release version upgrades to your site regularly. Once in a while, you also modify the database schema. You would like to test your builds on the live server before letting your users view it.

Phase 1: Launching of version 1.0

Development Server

1. Design the database schema and program the first version of your script.
2. Before committing the script to the repository perform the following steps:
a. Create a folder called db and assign appropriate permissions
b. Run dbbvc.php in your browser and create a baseline. The baseline can be created with data or without data as per your requirements. This will create db/1.sql file which is basically mysqldump of the current database.
3. Once that is done, you can safely commit your script (to the trunk folder). Be sure to commit the db folder, along with 1.sql.

Note: When committing to your repository, make sure you do not commit any configuration files. All configuration files should go in as config.default.php etc. (This will help you as your server config.php will not get updated when you run svn update on the server.) As a rule, any file that differs from your development to production server, must be a .default file.

Production Server

Now suppose you want to launch this version you have committed.

4. First copy your trunk folder to a suitable version in your tags directory (e.g. tags/1.0) using the svn copy.
5. Now go to your server and use svn export to export the contents of tag/1.0 to /yourserver/www/1.0
6. Now create a folder called static in your www. The static folder is used to store those folders which will have user uploaded data.

For example, you may have /yourserver/www/1.0/images/profilepics
a. First create a folder called profilepics in www/static
b. Delete the /yourserver/www/1.0/images/profilepics folder
c. Create a symbolic link in your images folder using the following command:
sudo ln -s /yourserver/static/profilepics /yourserver/www/1.0

7. Once that is done, create your database and modify your config.php as per your server settings. Now run dbbvc.php in your server and select Backup & Version Upgrade. This will create add all the schema/data to your database.
8. Now make sure your server supports mod_rewrite and add the following file to the /yourserver/www folder.

<IfModule mod_rewrite.c>
RewriteEngine On

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

RewriteRule    ^$  VERSION/    [L]
RewriteRule    (.*) VERSION/$1    [L]

</IfModule>
<IfModule !mod_rewrite.c>
	ErrorDocument 404 index.php
</IfModule>

In our example we replace VERSION with 1.0 in the above code.

9. Now create a .htaccess file in the /yourserver/www/1.0 folder with the following contents. (just to be safe, not necessary)

<IfModule mod_rewrite.c>
RewriteEngine Off
</IfModule>

10. Once that is done, you can point your browser to your server url and it should work! If you encounter any errors, then feel free to post a comment.

Phase 2: Launching of subsequent versions

Development Server

11. Now let us say you are ready to roll out a couple of changes on the server. Also, let us assume you have made modifications to the database schema. Now everytime you make changes to the schema, you must create a sequential file (2.sql,3.sql…) in the db folder. If you forget to create this file and have already modified the database schema, you can use MySQL Workbench to reverse engineer and find out the changes.
12. Now you can safely commit this version to trunk. Be sure to include your 2.sql… files also. You may use a single .sql file containing all the db changes for that revision.
13. Once that is done, create a tag of your new version and head over to the production server.

Production Server

14. Now svn export the tags folder to /yourserver/www/1.1 and create your symlinks
15. Duplicate your database (create a new database for this tag and import the current database)
16. Point your browser to dbbvc.php, use the Backup & Version Upgrade option. This will upgrade the database to the latest version.
17. Now you can test your script, once your a ready to do the switch, all you have to do is edit /yourserver/www/.htaccess and change the VERSION to your latest version.

Now, the above steps may appear to be an overkill for a small website which can afford a bit of downtime. Also, you may not have permission to create symlinks and thus can’t use the above method. For that let us have a look at a semi bulletproof svn workflow.

The Semi Bulletproof Method

Phase 1: Launching of version 1.0

Development Server

Follow the same steps as the bulletproof method (Steps 1,2,3).

Deployment Server

1. Make sure you have created your tag, and use svn checkout to checkout the contents of your tags/1.0 folder to /yourserver/www folder
2. Edit your config.php file, create your database and run dbbvc.php to import your database.
3. Create .htaccess file in your /yourserver/www folder with the following contents:

<IfModule mod_rewrite.c>
   RewriteEngine On
   RewriteRule ^(.*)(.svn)(.*)$ http://www.yourserver.com [L]
</IfModule>

4. Now you can point your browser to yourserver.com and it should work!

Phase 2: Launching of subsequent versions

Development Server

Follow the same steps as the bulletproof method (Steps 11,12,13).

Production Server

5. Now use the svn switch command to update the contents of your /yourserver/www folder.
6. Point your browser to dbbvc.php and update your database (if you have made any changes to schema).

Although the above method is simpler, and will work for most of your projects, there will be a definite downtime during your revision update and db schema update. During this upgrade, you may want to redirect all users to a temporary page using .htaccess.

Download the database versioning script (dbbvc)

Download the Database Version Control script. You will need to rename config.default.php to config.php and update the database details or you may edit dbbvc.php and require your own config.php/db.php.

Comments/Suggestions?

Do let me know your suggestions on how we can improve this workflow or anything else you want to add.

Spread The Word

If you like what you are reading, then please help spread the word by re-tweeting, blogging and dzone upvoting or use the ShareThis button below. Thank you.

What’s New

config/inflection.php
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

config/routing.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. For more information on how to use regular expressions have a look at one of these tutorials.

library/cache.class.php
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.

library/html.class.php
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’);

library/inflection.class.php
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 originally created by Sho Kuwamoto 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.

library/template.class.php
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.

function new() {
   $this->Category->id = $_POST['id'];
   $this->Category->name = $_POST['name'];
   $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)

function delete($categoryId) {
   $this->Category->id = $categoryId;
   $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. If you are unaware of database table relationships (1:1, 1:Many and Many:Many) have a quick look here. 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.

class Product extends VanillaModel {
	var $hasOne = array('Category' => 'Category');
	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.

<?php

class Category extends VanillaModel {
		var $hasMany = array('Product' => 'Product');
		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.

function view() {
   $this->Category->id = 1;
   $this->Category->showHasOne();
   $this->Category->showHasMany();
   $this->Category->showHMABTM();
   $data = $this->Category->search();
   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.

function page ($pageNumber = 1) {
  $this->Product->setPage($pageNumber);
  $this->Product->setLimit('10');
  $products = $this->Product->search();
  $totalPages = $this->Product->totalPages();
  $this->set('totalPages',$totalPages);
  $this->set('products',$products);
  $this->set('currentPageNumber',$pageNumber);
}

Now our corresponding view i.e. views/products/page.php will be something like below.

<?php foreach ($products as $product):?>
<div>
<?php echo $product['Product']['name']?>
</div>
<?php endforeach?>

<?php for ($i = 1; $i <= $totalPages; $i++):?>
<div>
<?php if ($i == $currentPageNumber):?>
<?php echo $currentPageNumber?>
<?php else: ?>
<?php echo $html->link($i,'products/page/'.$i)?>
<?php endif?>
</div>
<?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.

function view($categoryId = null, $categoryName = null) {
  $categories = performAction('products','findProducts',array($categoryId,$categoryName));
}

And the corresponding code in productscontroller.php should be as below.

function findProducts ($categoryId = null, $categoryName = null) {
  $this->Product->where('category_id',$categoryId);
  $this->Product->orderBy('name');
  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.

Where do we go from here?

I hope this tutorial is not too complicated. I have tried to break it down as much as possible. A couple of functions that we need to implement include -> redirectAction which will be similar to performAction, but instead redirect. performAction can only return results. redirectAction will perform only the redirected action and display that corresponding view only. This will be helpful during user authentication. If the user is not logged in, then we call a function like -> redirectAction(’user’,'login’,array($returnUrl));

Various classes are in their infancy like the cache class, HTML class etc. These classes can be expanded to add more functionality.

More scripts can be added like dbimport, clearcache etc. can be added. Currently there is only one script dbexport.php which dumps the entire database. Classes for managing sessions, cookies etc. also need to be developed.

Download link here

Download Framework (Part 2) (ZIP File)

Make sure you edit config/config.php and add your database details. Point your browser to localhost/FRAMEWORKDIR to see output. Also before executing, import the database that I have created in the db folder.

Suggestions?

Do let me know your suggestions on how we can improve this code/any particular features you would like to see implemented/any other unrelated topic you would like a tutorial on.

Spread The Word

If you like what you are reading, then please help spread the word by re-tweeting, blogging and dzone upvoting or use the ShareThis button below. Thank you.