Just came across a MySQL query simple problem and I was thinking it would be too simple for me to solve.

But it wasn’t like that. The query made me realize, I’m still missing some basic rules of query even after years on working on MySQL. It was about the use of GROUP BY and ORDER BY in the query.

To make this understand, here is a sample table.

Problem:

As you can see there are duplicate records for product code with sale and tax record in different amount. All I want is to fetch the records where it should be grouped together on the basis of “product_code” field but it should fetch the maximum sale record too. So I expected this result:

And here is the query I applied:

SELECT * FROM sales GROUP BY product_code ORDER BY sale DESC

But guess what ? It brought me this result:

This is wrong right ? the maximum sale recorded for A-002 was 500 not 340, and for A-001 was 340 not 200 and for A-003 was also 340 but not 120. Why ? because it grouped the rows first and then applied the sorting with order by. And definitely this is not what we wanted.

So, I came up with a simple work around with query:

SELECT * FROM sales salestable WHERE sale = (SELECT MAX(sale) FROM sales WHERE product_code = salestable.product_code) GROUP BY product_code ORDER BY sale DESC;

See what it says, its selecting rows from the “sales” table with an alias “salestable”. It asked the db to fetch the records but only for the product id whose record is having the maximum sale and then group it and sort it.

So did we get what we expected ?

What about the performance ? I haven’t analyzed it yet. Let me know if you have some points.

Hope it may help some of you guys.

Update:

As in the Moyes Ansari commented, we can also come up with this solution, which will give the same data and probably better in the performance:

SELECT * FROM (SELECT * FROM sales  ORDER BY sale DESC) AS salestable GROUP BY salestable.product_code ORDER BY sale DESC

Thanks Moyed

Quid-e-Azam Muhammad Ali Jinnah, the founder of Pakistan (25 Dec, 1976 – 11 Sep, 1948), when died, buried with honored in Karachi. Their grave area later converted into a big tomb that later became the landmark for Pakistan especially Karachi. The tomb was built quite big in size that it can easily be seen from all the corners of the city.
The tomb is surrounded by a big beautiful garden. The tomb is quite on the height that you can see the entire city from the tomb floor. Inside the tomb, a chandelier was installed which was gifted from the govt. of China.
Few days before I had a chance to visit the tomb again after years since my childhood. I took my daughter there to show her as an assignment from her class teacher. It was a good task for the kids, as definitely it’s a good way to introduce them to the father of the nation Muhammad Ali Jinnah.
I went there and took some pictures and sharing them here.

Karachi, the largest city of Pakistan, is a bit less fortunate when it comes to monsoon raining.

Raining always fascinate Karachites because here the total season last for less than even a month. Though they enjoy cloudy weather in whole August after the “hell hot” summer of June-July but still everyone talks about if it will be raining today.

Raining, in Karachi, is mostly enjoyed by walking, driving and playing favorite sports. Not only this but cooking traditional nation’s favorite snack like “Pakoray” and “Poori Halwa and Kachori” is like a must do task.

Even after these enjoyments, people have nightmares also which are actully the “after effects” of raining. Because of bit inefficient sanitory system, the water on the roads causes traffic jam which last for hours and it is a normal thing here. People prefer to go back to home early before it starts raining because they don’t want to get stuck in the traffic.

Even after these things raining is always enjoyed and considered a blessing here.

Like othet people I also take photos from my mobile when it rains. Enjoy some of my photos I took in recent monsoon season.

Posted from WordPress for Android

Cache is important for any web or console app to save time in fetching data from the databases.

Yii offers a built in cache mechanism with a variety of choosing different cache engines, e.g. Sqlite, Memcache, API etc.

Sometimes, you also require a console application to integrate the data with the web app. This may be required when you want some cron jobs for report generations or some other continuous services that do not require direct user interaction. To do this, you also want your console and web app to share the common cache key/values. Whatever the cache engine you are using, you need to mention in config file of console and web app, to use common key/values.

You can do this by editing your config files for console and web app.

In, the example here, I’m using SQLite cache:

'cachedb'=>array(
	    'class' => 'system.db.CDbConnection',
            'connectionString' => 'sqlite:/wamp/www/myproject/protected/data/cache_data.db',
            'schemaCachingDuration' => 3600,
		),			

'cache'=>array(
           'class' => 'system.caching.CDbCache',           
           'connectionID' => 'cachedb',
           'keyPrefix' => 'cache-assets'
  ),
  

As you can see I’m using 2 components for cache, cachedb is actually being used for sqlite.

‘keyPrefix’ is actually forcing the cache to use some particular prefix for the cache keys.

This config should be common between your console and web app config, and by doing this both apps will share the common cache values.

More on Yii cahce, check the official documentation.

Few days before I wrote about the class , I recently submitted to PHPClasses.org.

The class was successfully approved on the PHPClasses and it has been nominated for the “Innovation Award”, a regular monthly competition arranged by PHPClasses.

So If you are registered at PHPClasses.org, you can vote to the nominations here.

Happy Voting :)

https://play.google.com/store/apps/details?id=org.wordpress.android

I have just started using this WordPress Android app. Like the web interface, the app’s interface is so neat and usable that one would only want to create post from mobile device only. The menu is clearly defined and all main options can be seen at first look. Two options also given to create a quick blog with a picture or video. The editor is quite cool and its quite comfortable. The best option is the sharing option which is added in core Android system. You can share any link you want in your blog post. This is really a cool app. Here how it looks like :

Posted from WordPress for Android

http://www.bbc.co.uk/news/world-us-canada-18164200 The inventor of TV Remote Control “Eugene Polley” of “Zenith” died at the age of 97. Definitely Eugene’s invention made him one of the greatest inventor on 20th century. Today wheb we get beneited of hundreds of channels would never be fun without remote control. As it was said it was considered a luxury at that time but I thibk it still is. Thank you Eugene, for such a great invention. R.I.P. May God bless your soul

Update: This class won a 3rd rank overall for the month May 2012 at PHPClasses.org.

Recently, I was stuck in a situation where I wanted to have my own small conditional language and its parser.

I only wanted to execute the conditions using “IF THEN ELSE”. I came up with this idea and it worked. Although it could be bit memory costly so it may takes time for bigger conditions. It is ideal in the situations where performance is not the issue and you only need to get the jobs done.

So I wrote this class “BASIC Language Parser” and it has been published in (and yes this class been marked as “Noted” :) . Here is the direct link for the class.

Update (Nominated by PHPClasses.org):

How to use it:

Syntax:

IF <( ANY CONDITION YOU WANT )> THEN
DO
ELSE
{ DO | IF <( ANY CONDITION YOU WANT )> }
END

This is how the language will look like. It is up to you how you will write the conditions inside, you can do anything on your choice, you just need to tell parse weather the condition is “true” or “false” so that it may proceed for the “THEN” or “ELSE” what is appropriate.

Here is an example:

IF (MY_AGE > 13)
THEN
DO AGECHECK
ELSE
IF (MY_AGE > 20)
DO ANOTHERROUTINES
END
END

Then look at this function:

public function checkCondition($condition){

return false;
}

This is the place where you actually parse your condition. You could use DB to execute your condition or you could probably use “eval” to make it exactly what PHP is. Totally depends on your choice.

If you have any suggestion, feel free to comment. Or you might probably want to make it better.

Here is the link to the page for my already published classes at PHPClasses.org

How to change the DB connection in Yii models at runtime.

After you have generated the Yii model, you need to add a parent method from CActiveRecord “getDbConnection()”.

This method is actually resposible to return the database connection. In this method, you need to create a DB connection. If you use

return $connection->db;

This will actually the default DB connection mentioned in your config file (most probably main.php).

To use a different DB connection, you need to add this method:

Yii::createComponent( $array );

This method takes an array as argument. The structure of the array would be the same your default DB connection in your main conffig file, except you need to add one more key for db class “CDbConnection”.

So that’s how you will use it:

$dbconnection = Yii::createComponent(
			array('connectionString' => 'mysql:host=localhost;dbname=mydb',
			'emulatePrepare' => true,
			'username' => 'mydbuser',
			'password' => 'dbpass',
			'charset' => 'utf8',
			'class' => 'CDbConnection'
			)
		
		);

So now the method in your model would be:

public function getDbConnection(){
	
	$dbconnection = Yii::createComponent(
			array('connectionString' => 'mysql:host=localhost;dbname=mydb',
			'emulatePrepare' => true,
			'username' => 'mydbuser',
			'password' => 'dbpass',
			'charset' => 'utf8',
			'class' => 'CDbConnection'
			)
		
		);

	}

And the rest of the method would be as is.

So in any case where you are changing the databases at run time this code might help you.

Recently my 14 years old “hotmail” account was blocked as someone managed to access it to send lots of spam email. MSN blocked my account and asked me to send a code that has been sent to my email account which was at lycos mail. I was surprised to see that my lycos email was no more and I had to re signup. Luckily I got the same account with the same username.

When I signed up on Lycos, I got a blank email with no activation link. There was no way to activate and login to my account. Every I was logging it was asking me to signup again. I sent an email to the Lycoscustomer support and I was so surprised to see that I got reply within 10 minutes from the support, telling me that the account has been activated by support agent. And yes it was true, my account was activated and I was able to send and receive emails. Thanks to Lycos, I got my “hotmailaccount back.

Although there are some problems in Lycos, but I was so shocked to see the response time of Lycos customer service. I have been using Lycossince 1998 (when I started to browse the Internet). I still remember those early days of Internet, when having an email account was a big thing. I was also the user of Tripod which use to offer free hosting.

So that was a short story about Lycos…