MySQL Group By Modifier – WITH ROLLUP

The Group By WITH ROLLUP modifier in MySQL can give awesome flexibility in data warehouse kind of projects, where users are displayed different hierarchical inter related data and analytical flashy charts.

Always start with simple example : My project saves daily user visit data for my site. And it displays a table, which also can be displayed in flash charts as the data is present, showing page wise daily visit with daily, monthly aggregate of total visit while MySQL does the aggregation part for us. Something like

We have tow tables, ‘urls’ :

CREATE TABLE url (
 id int(11) NOT NULL AUTO_INCREMENT,
 url varchar(255) NOT NULL,
 page varchar(50) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO url (id, url, page) VALUES (NULL, 'index.php', 'Home Page'), (NULL, 'contact.php', 'Contact Us');

and a ‘daily_visit’ table :

CREATE TABLE  daily_visit (
id INT NOT NULL AUTO_INCREMENT ,
url_id INT NOT NULL ,
year INT NOT NULL ,
month VARCHAR( 20 ) NOT NULL ,
date VARCHAR( 10 ) NOT NULL ,
visit INT NOT NULL ,
PRIMARY KEY (  id )
) ENGINE = InnoDB ;

INSERT INTO daily_visit (id, url_id, year, month, date, visit) VALUES
(1, 1, 2009, 'August', '2009-08-10', 3),
(2, 1, 2009, 'September', '2009-09-08', 6),
(3, 1, 2009, 'August', '2009-08-15', 9),
(4, 1, 2009, 'September', '2009-09-22', 7),
(5, 2, 2009, 'February', '2009-02-06', 3),
(6, 2, 2009, 'February', '2009-02-24', 4),
(7, 2, 2009, 'April', '2009-04-04', 4),
(8, 2, 2009, 'April', '2009-04-14', 2);

Only one query does all for us. Just add the WITH ROLLUP clause with the Group By clause. Here is the whole script

<?php
$link = mysql_connect('localhost', 'root', '');
mysql_select_db('groupby_modifier', $link);

$sql = "SELECT	page, year, month, date, SUM(visit) as tot_visit
		FROM  daily_visit
			LEFT JOIN url ON url.id = daily_visit.url_id 
		GROUP BY page, year, month, date
		WITH ROLLUP";
$res = mysql_query($sql);
?>
<table width="45%" bgcolor="#CCCCCC">
	<tr>
		<td>
			<table cellspacing="1" cellpadding="1" width="100%">
				<tr>
					<th bgcolor="#FFFFFF">Page</th>
					<th bgcolor="#FFFFFF">Year</th>
					<th bgcolor="#FFFFFF">Month</th>
					<th bgcolor="#FFFFFF">Date</th>
					<th bgcolor="#FFFFFF">Visit</th>
				</tr>
				<?php while($row = mysql_fetch_assoc($res)){?>
					<tr>
						<td bgcolor="#FFFFFF" <?php if(!$row['page']) echo 'colspan="4"';?>><?php echo $row['page'] ? $row['page'] : '<strong style="color:red;">Total</strong>';?></td>
						<?php if($row['page']){?>
							<td bgcolor="#FFFFFF" <?php if(!$row['year']) echo 'colspan="3"';?>>
								<?php echo $row['year'] ? $row['year'] : '<strong style="color:red;">Total</strong>';?>
							</td>
						<?php }?>	
						<?php if($row['year']){?>
							<td bgcolor="#FFFFFF" <?php if(!$row['month']) echo 'colspan="2"';?>>
								<?php echo $row['year'] && !$row['month'] ? '<strong style="color:red;">Total</strong>' : $row['month'];?>
							</td>
						<?php }?>
						<?php if($row['month']){?>
							<td bgcolor="#FFFFFF">
								<?php echo $row['month'] && !$row['date'] ? '<strong style="color:red;">Total</strong>' : $row['date'];?>
							</td>
						<?php }?>
						<td bgcolor="<?php echo !$row['date'] ? '#FFFF8F' : '#FFFFFF';?>" ><?php echo $row['tot_visit'];?></td>
					</tr>
				<?php }?>
			</table>
		</td>
	</tr>
</table>

Now there is lots other things to keep in mind while using the WITH ROLLUP clause. Please refer MySQL Doc for more.

Dynamic Database Selection in CakePHP – Approach to SAAS

Separate database for individual client is a common design approach for SAAS (Software as a service) model implementation. And while its about CakePHP, it makes life difficult as it reads the database connection from ‘database.php’ script. Its not possible, or rather not viable, to put a new database connection in the ‘database.php’ file each time a client registers.

Here is what I have done to accomplish or overcome the problem, inspired from this article. They used to get the client information from the url, but in my case I had to get the info from Cookie or Configure::read() method.

The first step : Specify a new variable in each of the models which are specific to client.

class YOUR_CLASS extends AppModel {
	var $specific = true;
}

Now we check for this variable while loading the model. If its true then we have to make the database connection dynamically which is achieved in ‘app_model.php’ under ‘app’ directory.

class AppModel extends Model {
	var $specific = false;
	
	function __construct($id = false, $table = null, $ds = null) {
		
		if($this->specific){
			
			$client_id = Configure::read('Client.id');
			if(!$client_id){
				$cookie = ClassRegistry::init('CookieComponent');
				$cookie->key = Configure::read('Security.salt');
				$client_id = $cookie->read('client_id');
				if(!$client_id){
					return;
				}
			}
			$product_id = Configure::read('Product.id');
			$db_config = ConnectionManager::getDataSource('default')->config;
			
			$link = mysql_connect($db_config['host'], $db_config['login'], $db_config['password']);
			mysql_select_db($db_config['database'], $link);
			$res = mysql_query('SELECT * FROM client_database WHERE client_id = '.$client_id.' AND product_id = '.$product_id);
			$row = mysql_fetch_assoc($res);
			
			if(isset($row['host'])){
				$dbName = $row['db_name'];
				$config = array();
				// Set correct database name
				$config['driver'] = 'mysql';
				$config['persistent'] = false;
				$config['host'] = $row['host'];
				$config['login'] = $row['username'];
				$config['password'] = $row['password'];
				$config['database'] = $dbName;
				$config['prefix'] = '';
				
				// Add new config to registry
				ConnectionManager::create($dbName, $config);
				// Point model to new config
				$this->useDbConfig = $dbName;
			}else{
				return;
			}
		}
		parent::__construct($id, $table, $ds);
	}
}

Its a full hack, which totally breaks the MVC (Model View Controller) structure of CakePHP, using Cookie in Model. And also using Mysql functions prevents use of any other data source. So I recommend to get the idea from given code and implement something more structured and flexible way to do this. And please share if you can implement a better way such that.