Wednesday, 01 Oct 2008 11:00 EST
MySQL subqueries, supported in v4.1+, allow for SELECT statements to be placed within other statements, giving you a structured query that can be isolated from the rest of the query. This allows you to avoid using complex joins and have more readable, easier to understand code. These advantages come at the expense of code performance, though, as those complex joins are often more efficient than a subquery. For a small-scale database, like one for a personal website or blog, the ease and readability of your code will provide more of a benefit than the small hit in performance.
Let's take a look at a basic example of a subquery. This query selects all players in a baseball league that play for New York teams. The query looks at two tables;
players, which contains data on each player in the league (including their name and the team they play for), and
teams, which contains data on each team in that league (including the city it plays in).
SELECT players.name
FROM players
WHERE players.team
IN (SELECT teams.name FROM teams WHERE teams.city='New York');
For those of you who are all about efficiency, here is how you could rewrite this as a JOIN.
SELECT players.name
FROM players, teams
WHERE players.team=teams.name
AND teams.city='New York';
OK, so in this example, the JOIN might just be little bit easier than the subquery. Trust me, though, as the queries get bigger and you start adding more tables, the subqueries will be much easier to handle.
Let's look at a somewhat more complex example. This query returns the names of every player who has hit a homerun by selecting names from the
players table where there is a record of the player hitting a homerun in the hits table.
SELECT
players.name,
(SELECT DISTINCT hits.player_id
FROM hits
WHERE hit_type='homerun'
) AS hr_hitters
FROM players
WHERE players.id=hits.player_id;
And the same query rewritten as an inner join.
SELECT DISTINCT players.name
FROM players, hits
WHERE players.id=hits.player_id
AND hits.type='homerun';
Now let's take a look at one final example, containing 3 nested queries. This example selects all players who have hit more than 20 homeruns by taking a COUNT of each players homeruns hit.
SELECT
players.name,
(SELECT
(SELECT
player_id,
COUNT(*) AS count
FROM hits
WHERE hit_type='homerun'
) AS hr_count,
FROM hits
WHERE count>=20
) AS hr_hitters
FROM players
WHERE players.id=hits.player_id;
Here's where we really see the benefit of subqueries. This query would be very difficult to write as an inner join because of the third level count. If you're running a small site and can afford a small hit in performance, writing your queries with subqueries instead of joins can save a tremendous amount of development time as well as pain and frustration while you debug your code.
As always, if you have a better technique or a tweak that you think improves on this code, please share it in the comments. Also, if you have a tip, a trick or an idea for my next post, please don't hesitate to
contact me.
Sunday, 28 Sep 2008 7:18 EST
Many developers aren't aware that there is a CASE statement in
MySQL 5.0 which enables you to have if-then-else functionality directly in your SQL. The CASE statement is for stored procedures, which are SQL statements that can be stored on the server so that clients don't have to keep reissuing the same statements ove rand over. The functionality of the MySQL CASE statement is basically the same as any other case function in programming. If a search condition evaluates as true, then the following block of code is executed. You can have any number of if-then statements and follow them up with a single else statement to be executed if none of the search conditions are true. Be sure to always add an ELSE clause into your code block. MySQL will return the "Case not found for CASE statement" error if no search conditions evaluate to true and there is no ELSE clause. For instances where there is no ELSE code to execute, use an empty BEGIN . . . END code block. The CASE function is one of the most powerful functions in SQL coding. The CASE function can evaluate complex conditional statements, use any comparison operator and check multiple conditions all directly in your SQL code. CASE allows you to combine multiple SQL queries into one powerful query, based on the conditions you lay out.
Let's take a look at the following example. Say we have a database of baseball statistics and we want to pull all batters statistics and group them by how good of a hitter they are.
DELIMITER //[1]
CREATE PROCEDURE battertype()
BEGIN
DECLARE batting_avg INT DEFAULT .000;
CASE batting_avg
WHEN batting_avg > .300 THEN 'Great Hitter'
WHEN batting_avg >= .275 and batting_avg < .300 THEN 'Good Hitter'
WHEN batting_avg >= .250 and batting_avg < .275 THEN 'Average Hitter'
WHEN batting_avg < .250 THEN 'Minor Leaguer'
ELSE
BEGIN
END;
END CASE;
END;
//
And that's it for a basic MySQL CASE statement. Stay tuned for an in-depth look at MySQL stored procedures as well as several other MySQL posts that I'm working on. As always, if you have a better technique or a tweak that you think improves on this code, please share it in the comments. Also, if you have a tip, a trick or an idea for my next post, please don't hesitate to
contact me.
Monday, 22 Sep 2008 9:53 EST
eWeek recently put together a panel of representatives from Microsoft, Mozilla and Google to talk about the future of web browsers. Cross browser compapatability is the major topic here as developers continue to struggle with the lack of standards. The panel of experts faced off on HTML 5, Canvas, Silverlight, IE 8, Google Chrome and Firefox at the Web 2.0 Expo, then took on questions from an audience of disgruntled developers hoping to hear how these browsers will unify the web. Let's just say that I don't expect to be able to forget about browser compatability for quite some time.
» Read the full article on eWeek.com
Monday, 11 Aug 2008 11:29 EST
Check out these
PSD files and tutorials posted at
Noupe.com yesterday. These could be a huge help for you next time your trying to create that perfect graphic for your site.
Wednesday, 06 Aug 2008 7:46 EST
Aurora is a concept browser created by
Adaptive Path as part of the Mozilla concept browser series.
The video explores and entirely new way in which people could interact with the web through technology that is projected to appear in the mainstream over the next decade.
Tuesday, 05 Aug 2008 12:58 EST
The team at
speckyboy.com took the time to pull together a list of
96 high-quality, free icon sets; the least you can do is take a look at it. Besides, there's some really good icon packs in here. This post is definitely worth a bookmark.
Monday, 04 Aug 2008 18:55 EST
So you're working with a 2D array in JavaScript and you need to find a way to sort the damn thing. As it turns out, it's not nearly as bad as you might think. While JavaScript does not have a built in 2D array sort function to go along with array
.sort(), you are able to utilize good old
sort() by passing it a custom comparison function. Let start with the following 2D array:
var customers = new Array();
customers[0] = {lastName:"Williams", firstName:"Tom", company:"ABC Inc"};
customers[1] = {lastName:"Smith", firstName:"John", company:"123 Ltd"};
customers[2] = {lastName:"Johnson", firstName:"Bob", company:"Johnson Co"};
By default,
customers.sort() will sort on the first field defined in the array,
lastName. Let's say we want to sort on
company, though. All we need to do is create a custom comparison function which will grab the value of
company, compare the two and return 1 if
a.company is higher in the order and -1 if
b.company is higher. Very simple, yet very powerful.
function sortByCompany(a, b) {
var x = a.company;
var y = b.company;
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
}
Now you simply call JavaScript's standard array sort function with the name of your comparison function as the only parameter.
customers.sort(sortByCompany);
To sort descending, simply swap
1 and
-1 in the comparison function.
function sortByCompanyDESC(a, b) {
var x = a.company;
var y = b.company;
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
}
customers.sort(sortByCompanyDESC);
As always, if you have a technique that you think is better or a tweak that improves on this code, please share it in the comments. If you have a tip, a trick or an idea for my next post, please don't hesitate to
contact me.
Tuesday, 29 Jul 2008 20:48 EST
Noupe.com posted a very good resource for CSS designers of all skill levels yesterday;
40+ tutorials, tips, demos and best practices. Check out the list and let me know what you think of it and what some of your favorite CSS resources are.
Thursday, 24 Jul 2008 20:55 EST
Ian Hickman posted
5 rules for variable naming, today. I strongly agree with the first 4 rules and hope you all follow them; the 5th is debatable. Personally, I use i, ii, j, k, etc. all the time and have never found it to be a problem. They are commonly used names for loop counters and any programmer should know what they mean.
Check out Ian's
post and let me know what you think. What some other rules that all variable naming conventions that everyone should be following?
Wednesday, 23 Jul 2008 21:48 EST
And so the never ending debate continues. Nathan Smith at
Digital Web Magazine had the opportunity to debate the use of
Photoshop versus Fireworks with 8 top web designers; 4 in favor of each. Though they each had convincing arguments; the most convincing to me was offered by Jared Christensen, a designer at Viewzi. Jared argues that Photoshop "is the graphics software that has always catered to the pixel. All of our displays (currently) are pixel-based, and [he is] very comfortable working with pixels." I agree completely, Jared. Fireworks is a great tool, and maybe it's just a matter of getting comfortable, but I've always felt that I am a bit more "in control" and my image size are more definite when I'm working in Photoshop.
So who's side are you on?
