Software and Technology
MySQL – getting two items per source in a single query
Feb 4th
Assume you have the following table called stories with the following data. And you have run the following query:
SELECT * FROM stories ORDER BY storydate DESC
| id | source | title | storydate |
| 10 | cnn | Story 10 | 2010-01-21 |
| 9 | bbcnews | Story 9 | 2010-01-20 |
| 8 | bbcnews | Story 8 | 2010-01-19 |
| 7 | skynews | Story 7 | 2010-01-18 |
| 6 | cnn | Story 6 | 2010-01-17 |
| 5 | bbcnews | Story 5 | 2010-01-16 |
| 4 | cnn | Story 4 | 2010-01-15 |
| 3 | skynews | Story 3 | 2010-01-14 |
| 2 | skynews | Story 2 | 2010-01-13 |
| 1 | cnn | Story 1 | 2010-01-12 |
Now for the challenge. What happens if you are required to get the top two stories from each source? One option is to first get the order of the sources, and then loop through each one of them:
SELECT DISTINCT source FROM stories ORDER BY storydate DESC
<loop “source” as “sourceid”>
SELECT * FROM stories WHERE source = "{sourceid}" ORDER BY storydate DESC LIMIT 2
</loop>
Another option is to do the in MySQL itself! Unsure if there is a better way of doing this, but here’s mine using UNION and Sub Selects:
(SELECT * FROM stories WHERE source = (SELECT DISTINCT source FROM stories ORDER BY storydate DESC LIMIT 0,1) ORDER BY storydate DESC LIMIT 2) UNION (SELECT * FROM stories WHERE source = (SELECT DISTINCT source FROM stories ORDER BY storydate DESC LIMIT 1,1) ORDER BY storydate DESC LIMIT 2) UNION (SELECT * FROM stories WHERE source = (SELECT DISTINCT source FROM stories ORDER BY storydate DESC LIMIT 2,1) ORDER BY storydate DESC LIMIT 2)
Explanation:
The bold section are subselects and form the WHERE clause of the query. Here we want the first, second and third as per source, but only one at a time. Once we have two results per item, we use UNION to join them together.
One thing to note though. Even though it returns the latest two stories per source, the final table is not sorted by storydate! This can be done using PHP sorting techniques. Is there a way of doing this one time in MySQL?
Getting started with Wireshark, the Fiddler for Ubuntu
Feb 1st
Fiddler is a useful tool for tracking http requests and responses. It’s similar to the Net tag in Firebug, except that it can be used to track all requests, not only browser-based ones.
For Ubuntu users, the recommended alternative is Wireshark. Wireshark is noted as being more powerful than Fiddler, but the focus will be on the features most commonly used by web developers for tracking.
Installation
Wireshark is found in the Ubuntu repositories, so simply search for wireshark in Synaptic, or enter sudo apt-get install wireshark
Usage
Under Applications > Internet, you will notice there are two options. Wireshark and Wireshark (as root). Run the second one (as root) as this gives you more data to track.
After wireshark has started, the process is straightforward:
- Choose which network interfaces to capture traffic from
- Start capturing
- Filter captured requests
1. Choosing which network interfaces to capture traffic from. From the menu, choose: Capture > Interfaces
This will bring up a list of network devices.
2. Since my work involves debugging work off my laptop (http://localhost etc), I only start device lo (127.0.0.1). This is useful as it immediately ignores all other traffic.
3. The one thing you will notice is that fiddler will log both the TCP and HTTP requests. For the purpose of debugging AJAX, etc., we are only interested in HTTP requests, TCP requests are not required. These can be hidden by adding a filter.
In the field next to Filter:, enter http and click on Apply.
The steps will now show all traffic coming on 127.0.0.1 via HTTP. The last step is picking up the AJAX parts for debugging. Notice that for each request, there is a response. Unlike Fiddler, Wireshark does not combine the request and the response. So click on the response line, and then expand the Line-based text data row. This will show the text of the AJAX response.
Conclusion
Wireshark takes more steps than Fiddler, and Fiddler is a more focussed program than Wireshark. Wireshark also captures traffic without having to install a plugin into firefox, etc., so it’s useful for debugging applications as well. The biggest improvement to make Wireshark truly replace Fiddler is to turn off truncation of the response text. Wish I knew how!
How to use ExtJS Abstract functions
Sep 22nd
For some methods in ExtJS, you will see:
Class Ext.grid.GridDragZone
…
onDrag( Event e ) : void
Abstract method called during the onMouseMove event while dragging an object.
Parameters:
e : Event
the mousemove event
Returns:Class Ext.grid.GridDragZone
void
In this case, GridDragZone is part of GridView which is part of GridPanel. Accessing it will be, GridPanel->GridView->GridDragZone.
The GridDragZone gets implemented for a GridPanel when you set enableDragDrop to true, but it only becomes available when the grid renders, so:
var grid = new Ext.grid.GridPanel({
// etc
});
grid.on('render', function(obj) {
obj.getView().dragZone.onDrag=function(evt){
alert('being dragged');
}
});
Simple Drag and Drop from Adobe AIR
Aug 21st
Quite a few articles and tutorials show you how to drag and drop a file onto an Adobe AIR application. This one will show you how to drag one FROM an Adobe AIR application TO YOUR DESKTOP (or any other location you provide. The code is really simple:
Drag Me to the Desktop
Download: Source Code
To run, extract files from archive, and then run: adl application.xml
Dynamically generating a Sandbox in Adobe AIR
Aug 19th
Something that seems to have worked
Thoughts?
Focus Booster App
Aug 4th
For the past couple of days, I’ve been experimenting with a tool called Focus Booster. It’s a simple app with an easy method to help you measure and increase your focus and concentration:
- Firstly, choose the amount of time you would like to complete a particular task. By default it is set to 25 minutes.
- Prepare to do the task, clear your PC desktop of any distractions, and start the timer.
- Continue working until you’ve completed the task or the timer runs out.
- Take a break
- Restart the process with your next task
Tiny app, simple process, but it works for me, and these are the benefits I believe are derived:
- Forces you to plan and think about a single task you would like to perform
- A quick glance at the timer helps you focus, there is something watching you!
- Helps you to persevere, (just another 8 more minutes to go, etc.)
- Provides some type of measurement of how long you can focus, and challenges you to better it. The next version will allow you to record your focus sessions.
Focus Booster is a free application and requires Adobe AIR to run. Download it at: http://www.focusboosterapp.com/ . Follow updates on twitter @focusboosterapp
How to: Uninstall Adobe AIR Apps on Ubuntu (via Synaptic)
Aug 3rd
There are two ways to uninstall Adobe AIR apps on Ubuntu.
1) Access to original .air file
If you have access to the original .air file, run it and it will give you a screen with options to either Uninstall, Run Now or Cancel. Simply click on Uninstall.
2) Use Synaptic Package Manager
A second more easier approach (particularly if you lost the .air package) is to use Synaptic. From the sidebar, simply choose: Status > Installed (local or obsolete)
Discovering this makes me less hesitant to try our different AIR apps, trying to find out which one works best.
Cant find/see unread email in Thunderbird
Aug 3rd
For days now, Thunderbird has been showing one unread email, though I couldn’t find it using all the sort techniques I’ve had. Googling shows it’s not an uncommon occurrence. The solution is also quite easy.
Right click on the folder and choose ‘Compact‘.
Takes a minute, but it will reveal the ‘hidden’ email(s).
Source: https://bugs.launchpad.net/ubuntu/+source/thunderbird/+bug/116642
ExtJS Books coming soon
Jun 24th
Here is a list of some of the ExtJS Books, one is already published, others should be out later this year:
Learning ExtJSThis book has already been published and is available for purchase. As the first and currently only book available, it is quite a useful reference. What you will learn from this book?
|
|
Practical ExtJS Projects with GearsThis promises to be an exciting book focussing on practical case studies, and also integrate offline usage with Google Gears. Expected in July 2009 What you’ll learn
|
|
 |
ExtJS in ActionExpected in January 2010, although some chapter are available with the Manning Early Access Program |
Appreciating Mozilla Prism
Jun 15th
When Mozilla Prism was first launched, I honestly never gave it much thought as to it’s usefulness, or why anyone would want to use it. Today, it is such an essential tool for me. Let me explain. More >
