February 7, 2010 in Blog, Coding & Scripting by Sanny

Fetching data from multiple tables in one MySQL query – Part 2

A few days back, I mentioned in a post how to fetch data from multiple tables in one MySQL query using multiple and nested SELECT statements. Today I’m going to present a more elegant way to do that using INNER JOIN.

Let’s reiterate the problem first -

We have 2 tables – tableA and tableB. The structure of these 2 tables are like -

tableA

`id` int(11) NOT NULL AUTO_INCREMENT,
`home_id` int(11) DEFAULT NULL,
`away_id` int(11) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`status` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)

tableB

`id` int(11) NOT NULL AUTO_INCREMENT,
`team_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)

Here home_id and away_id in tableA are foreign keys that references to id in tableB. We want to pick data from tableA according to some condition – for example, status is less than 4. But we want team names instead of ids. That means we need to pick team names from tableB.

Now if we needed just home team or away team, that would have been easy as we can use an INNER JOIN straightway -

SELECT tableA.id,
tableB.team_name AS home_team,
tableA.datetime
FROM tableA
INNER JOIN tableB
ON tableA.home_id = tableB.id
WHERE tableA.status < 4;

But we need both home and away team and they refer to 2 different rows on tableB. So, it is not possible to pick both the names using one INNER JOIN. Difficult situation, huh? Aliases come to the rescue! We can use an alias for tableB and inner join that to the first 2 tables. Here’s the full query -

SELECT tableA.id,
tableB.team_name AS home_team,
tableB_Alias.team_name AS away_team,
tableA.datetime
FROM tableA
INNER JOIN tableB
ON tableA.home_id = tableB.id
INNER JOIN tableB AS tableB_Alias
ON tableA.away_id = tableB_Alias.id
WHERE tableA.status < 4;

February 2, 2010 in Blog, Coding & Scripting by Sanny

Fetching data from multiple tables in one MySQL query

Often we need to use SELECT queries that will fetch data from multiple tables. One option is to use INNER JOIN, LEFT JOIN etc. But in this case described below I couldn’t find a way to use those. Lets say we have 2 tables – tableA and tableB. The structure of these 2 tables are like -

tableA

`id` int(11) NOT NULL AUTO_INCREMENT,
`home_id` int(11) DEFAULT NULL,
`away_id` int(11) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`status` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)

tableB

`id` int(11) NOT NULL AUTO_INCREMENT,
`team_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)

Here home_id and away_id in tableA are foreign keys that references to id in tableB. We want to pick data from tableA according to some condition – for example, status is less than 4. But we want team names instead of ids. That means we need to pick team names from tableB.

Here’s a simple way to do it -

SELECT tableA.id,
(SELECT tableB.team_name FROM tableB WHERE tableA.home_id = tableB.id) AS home_team,
(SELECT tableB.team_name FROM tableB WHERE tableA.away_id = tableB.id) AS away_team,
tableA.datetime
FROM tableA
WHERE tableA.status < 4;

It is important to use aliases here. You have to specify ‘AS home_team’ or ‘AS away_team’ etc in the inner SELECT commands.

There may be more efficient and elegant methods than this – using multiple selects in one query. Can anybody suggest some?

October 13, 2009 in Blog, Coding & Scripting by Raiyan

Java Applet and Javascript message passing

I did this quite a while a ago, thought I should share this.

To call a js function foo from java applet :

String [] stringArgs = new String[2];

stringArgs[0] = "first arg from Java";
stringArgs[1] = "second arg from Java";

mainWindow.call("foo", stringArgs);

To call a function foo() in a java applet named bar, do the following in javascript:

document.bar.foo(param1, param2, ....) ;

July 26, 2009 in Blog, Coding & Scripting by Raiyan

The Right-Click Menu in Flash

The right click menu is actually called the context menu. I did not know that. I had to go through several forums and blogs on the net before stumbling upon this piece of information.

Usually one does not need to bother about this menu. But if you are working on a game, you might want to disable this menu. Because the ‘play’ option in the context menu gives your user the ability to play the movie when you would rather have it to stopped. I leave it up to your imagination figuring out how one can cheat or mess things up from here.

To disable the context menu, put this line of code on the first frame, or in any other frame from where you want the context menu to be disabled.

stage.showMenu =  false ;

This will not actually disable the context menu, but hide most parts of it.

Disabling the context menu would help those would be cheaters from cheating their way through your game. But that’s not all we can do with this menu. We can also add some customized menu options as well. To do so, one has to follow these simple steps.

• Make a new context menu
• Create customized menu item
• Attach event listener with the custom menu item
• Insert the custom item into the new menu
• Set the new menu as the current context menu

For example, in our games we usually put a customized menu item linking back to our homepage. To do this, we use the following piece of code.

function initMenu() {
	var m:String='© muktosoft';

	var cm:ContextMenu=new ContextMenu();
	var item:ContextMenuItem=new ContextMenuItem(m);

	item.addEventListener(ContextMenuEvent.MENU_ITEM_SELECT,gotoMukto_);
	cm.hideBuiltInItems();
	cm.customItems.push(item);
	this.contextMenu = cm ;

}

function gotoMukto_(evt:ContextMenuEvent):void {
	navigateToURL(new URLRequest("http://www.muktosoft.com"), "_blank");
}

June 25, 2009 in Blog, Coding & Scripting by darthxaher

Python: C++ style cin, cout in Python

Ready to bring some flavor of C++ into python? If you like cout, cin in C++ and also a python programmer, you’d definitely like this snippet.

import sys
class ostream:
    def __init__(self, file):
        self.file = file

    def __lshift__(self, obj):
        self.file.write(str(obj));
        return self
cout = ostream(sys.stdout)
cerr = ostream(sys.stderr)
endl = 'n'

x, y = 'Printing', 'like C++'
cout << x << " " << y << endl

I found this and a lot of other cool stuffs from Peter Norvig’s blog. I highly recommended subscribing to this blog.

June 18, 2009 in Blog, Coding & Scripting by darthxaher

Python: Working in Unicode

While working on unicode based characters in python, you’ll often come across this type error message (this cost me a while to fix).


UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3: ordinal not in range(128)


This will happen if you do not set your character encoding in your python file to UTF-8. First you need to make sure the first few lines of your programm looks like this.


#!/usr/bin/python
# coding=utf-8
# -*- encoding: utf-8 -*-


This enables you to write unicode characters in your source code. But this does not enable you to print them in console and you’ll still keep getting the same error I previously mentioned.

To solve this you need to add the following code in your /usr/lib/python2.5/sitecustomize.py file (This might change depending your installed python version)


import sys;
sys.setdefaultencoding('utf-8')


The interesting part is, if you try to do that in your source file, it won’t work, I kept getting this error

AttributeError: 'module' object has no attribute 'setdefaultencoding'


I’m not sure why python does this, but a reasonable explanation would be not to change the character encoding in runtime, that could pose vulnerability to the system.

utf-8 isn’t default in python, maybe they’ll fix this in python 3.0

June 5, 2009 in Blog, Coding & Scripting by Raiyan

Hyperlink in actionscript 3

Regarding hyperlinks, there has been a significant change from Actionscript 2 to Actionscript 3.

In Actionscript 2, the following line of code would have sufficed to open a window in your default browser for this blog.

getURL("http://www.muktosoft.com/");

But in Actionscript 3, you need to instantiate an URLRequest then call navigateToURL to (:) navigate to your target url. See the code snippet bellow.

import flash.net.navigateToURL;

var req:URLRequest = new URLRequest("http://www.muktosoft.com");

navigateToURL(req);

Code explained :

First of all, import the required class from flash.net pacakge.

Now instantiate a URLRequest object with the target url provided in constructor, for our example the target is muktosoft.com.

Then call the public function navigateToURL (provided in flash.net package) to, offcourse, navigate to that url.

You can also control whether the new page should load in the same window or in a different one by providing a second parameter. For example, to open the target page in a new window, you have to put “_blank” as the second parameter.

navigateToURL(new URLRequest("http://www.muktosoft.com"), "_blank");

The possible values for the second parameter and their meanings are

The second parameter actually lets you tell on which browser window or HTML frame to display the document indicated by the request parameter. You can enter the name of a specific window or use one of the following values:

* “_self” specifies the current frame in the current window.
* “_blank” specifies a new window.
* “_parent” specifies the parent of the current frame.
* “_top” specifies the top-level frame in the current window.

Hope you find this useful.

May 31, 2009 in Blog, Coding & Scripting by Raiyan

Mute button in actionscript 3

This is what I usually do, but it may or may not be the easiest or most sophisticated way of implementing a mute button in actionscript 3. I use Flash CS3 IDE and actionscript 3.0 for the coding, this solution might not work in earlier versions.

To make a mute button, first we need to define two SoundTransform instances, one for muting and the other for un-muting. When the button is clicked, depending on the current state, either of these SoundTransform instances are assigned to the soundTransform property of the global SoundMixer.

var muteTransform:SoundTransform = new SoundTransform(0.0,0.0);
var playTransform:SoundTransform = new SoundTransform(1.0,0.0);

function muteSound():void {
	SoundMixer.soundTransform = muteTransform ;
}

function unmuteSound():void {
	SoundMixer.soundTransform = playTransform ;

}

May 30, 2009 in Blog, Coding & Scripting by Raiyan

Suspended tween in Flash

If you have ever tried instantiating some tween during run time in flash, you might have noticed that sometimes your tween does not reach its endpoint. This can cause problem ranging from the category of a small nuisance, when you try to move something or make something fade out, to the halting of a complex process by breaking a chain of events when, you rely on the tween completion event to trigger another event in a long chain.

I first came across this suspended tween effect (a.k.a tween got stuck and tween not completing) in flash while working on a flash project that required me to move some entities around the screen upon receiving a mouse click event. To keep things easy to understand, let us say I had an array of objects named box and I had to move all items in box 100 units to right upon receiving a mouse click. I looped through the entire array and instantiated a tween for each of the elements in box.

for(i:int=0;i<box.length;i++) {

	var tween:Tween = new Tween(box[i], "x", Strong.easeOut, 0, 100, 1, true);

	tween.looping = false;
}

Soon I found that some of the boxes would not complete the movement from x=0 to x=100 and get stuck somewhere in between the starting and finishing position. Later, after googling for a while, I found the cause and a solution for this.

The suspended tween effect is caused by garbage collection of orphaned tween objects. If you don’t let your tween become orphans, they don’t get cleared by the garbage collector and your tweens get to reach their respective finishing points. A tween object becomes orphan as soon as it becomes out of scope. Notice that, in my previous code snippet, I have instantiated tween objects inside the loop. As soon as we are out of the loop, all the tween objects instantiated inside are out of scope and become orphans. When the garbage collector finds these orphan tween objects, they are cleared immediately and you see that your tweens have just suspended instead of reaching completion.

The solution is to make sure that your tween objects are not orphaned or out of scope before completion. You can ensure this by keeping a valid reference for the tween object for as long as it is not completed. In my case, I used an array of tween objects declared outside of the loop to hold the tween objects and all was fine.

tweens:Array = new Array();

for(i:int=0;i<box.length;i++) {

	var tween:Tween = new Tween(box[i], "x", Strong.easeOut, 0, 100, 1, true);

	tween.looping = false;

	tweens.push(tween);
}

March 20, 2009 in Blog, Coding & Scripting, Graphics & Design by Raiyan

Photoshop Scripting : Hello World

Being respectful to traditions, let us start with a script for drawing “Hello World” in Photoshop. One can use Javascript or VBScript in windows or Applescript in Mac for photoshop scripting. The following Javascript code will produce a “Hello World” photoshop document.

var originalUnit = preferences.rulerUnits
preferences.rulerUnits = Units.PIXELS

// Create a new document
var docRef = app.documents.add( 300, 300 )

// Create a new layer
var artLayerRef = docRef.artLayers.add()
artLayerRef.kind = LayerKind.TEXT

// Write our text
artLayerRef.textItem.contents = "Hello World"

// Release references
docRef = null
artLayerRef = null
textItemRef = null

// Restore unit setting
app.preferences.rulerUnits = originalUnit

Code explained :

First of all, we need to keep record of the current unit settings since we are going to change the unit settings according to our preferences. After saving the current units settings, we change the units to PIXELS.

Next, we create a 300X300 pixel document. Then we add a new layer to the new document. Since we want this to be a text layer, we set LayerKind to TEXT.

On the new layer, we write our text, “Hello World”.

At the end, we dereference the variables and restore the previous unit settings.