Coffee on the Keyboard

organization

Of course, as soon as I get around to doing the redesign, I decide I don’t quite like it.

I’ve decided to reorganize this site and another one, to separate the blog/webzine-type sections and the professional/portfolio-type sections.

The two will still be pretty closely related and linked, but I think it will make a little more sense, and allow some more interesting design choices on the blog side of things. I’m looking forward to doing something a little out of my comfort zone.

But, this isn’t going to happen too soon. I just started classes again, and I want to build a custom blog/CMS (and possibly release it).

At first I was going to use Wordpress, but I want more overall control over the structure and organization. So after deciding exactly how I want to organize both the site itself and the underlying code, and then actually doing it, there will be not one, but two interesting sites to visit.

Should be fun.

Posted in My Site

Reading Material: Anorexic Writing

Amber Simmons wrote a brilliant article on A List Apart on the importance of writing to web design.

Amber is absolutely right: writing hasn’t just taken a back seat to design; design left writing in the bathroom at the gas station.

So much emphasis has been put on user-generated content that we’ve forgotten that users aren’t going to generate our headers and footers, or our layout and alt tags. All of that "unimportant" stuff has been left to designers: form buttons, FAQs, instructions. Often even important "about" sections are left to designers. No one will read it, anyway.

This attitude is a vicious cycle. No one will read the content, so why bother writing anything worth reading? Why bother reading such bad content?

I will certainly be re-examining my footer content, with her thoughts in mind.

Just because something is on the web doesn’t mean it can’t be worth reading.

Posted in Reading Material

redesign!

So I finally got around to doing the redesign. I based this somewhat on WordPress’s site and default templates, because they were a great example of the style I wanted: a more web-professional look with a print-inspired feel.

I’ve heard rumors and whispers that, while serif fonts are easier to read on paper, sans-serif fonts are easier to read on screens. I don’t really know where this comes from or if it’s true. I find that I can read 6- or 8-point Times New Roman but I get fuzzy below 10-point Arial, so I decided to use serif fonts.

Lower-case letters are very stylish and "en vogue" at the moment, and I like the way my name looks, so I’ve decided to drop the case for now.

Posted in My Site

Adapting Wikiddiction for Profit

Since it first broke onto the internet, Wikipedia has generated some of the most polarized articles, the harshest critiques and the staunchest support, that I have ever read. Most of these articles tend to focus on reliability or the perception of reliability, to borrow from Larry Sanger, one of Wikipedia’s co-founders.

Sanger wrote, almost three years ago, a very eloquent article on what he sees as a pervasive and underlying flaw in the Wikipedian culture: anti-elitism. I happen to agree with him on this point. Certainly a doctorate in psychology qualifies an author better than 10,000 wiki-edits.

What I find interesting, though, is the reaction of Wikipedia’s critics. Rather than observe and adapt, the "more reliable" sites, like Encyclopaedia Britannica have stuck with their pre-Wiki concept of an online encyclopedia.

As an example (partly inspired by xkcd) I chose to compare the Wikipedia article on suspension bridges with that of the Encyclopaedia Britannica.

The first, and most obvious observation, is the length of the article. Britannica only gives you the first 250 words for free, cutting off mid-sentence. The Wikipedia article is several pages long, including examples and photos. I also noticed the difference between the URIs, Wikipedia’s simple /wiki/Suspension_bridge compared to Britannica’s cryptic /eb/article-9070493/suspension-bridge.

But the biggest difference is the links. The Wikipedia article has hundreds of outbound links, most right in the article—some external sources are at the bottom. The vast majority of these links are related to the article, and only a relative few go to extraneous or legal stuff. Britannica has very few links, none in the text of the article, and the only related ones are just more search results towards the bottom of the page (below the fold).

I’m sure the guys over at Google’s AdSense division dream of getting their contextual ads onto Wikipedia—a thing not likely to happen. So why couldn’t Britannica snatch up that revenue?

As Randall Munroe described so aptly, all those links keep users on Wikipedia for hours, sometimes reaching hundreds of page views. Most people don’t avail themselves of their ability to edit: it’s fascinating enough as-is. Web designers and developers spend much of their working lives trying to get users to stay on their sites. Wikipedia did it in the simplest way possible: links.

Britannica and other encyclopedias already have the articles. All they need to do is "wikify" them—a Wikipedian word meaning "to format … and add internal links to material" relevant to the article.

By putting their entire articles online, filled with internal and interesting links, traditional encyclopedias like Britannica might be able to generate the same kind of fascinated browsing that has become foil to would-be productive students and employees everywhere.

Couple that with the seemingly limitless font of cash that is internet advertising and it’s not hard to see how much money Britannica and their peers are throwing away.

Posted in Articles

MySQL Subqueries

I often find it difficult to find tips and advice for doing relatively simple things in things like MySQL, Ruby, Python, etc. So, starting with this post, I will help fill that niche. Today’s topic is Using Subqueries to Simplify your SQL Queries.

For this article, I’m using PHP and MySQL for examples. There are slightly different implementations of SQL in the various database engines, but this is one thing they all have in common.

SQL is called “structured query language” because it allows subqueries to make complex queries easier and faster. The idea of a subquery is simple: have the database perform one query and insert it into another.

There are dozens of useful ways of using subqueries, but I will concentrate on two: subqueries in the select expression and subqueries in the where clause.

Security Concerns

In most web programming languages, the interface between the script and the database only allows one query per access for security reasons: an injection attack could input something like '; DELETE * FROM users; and do some serious damage to a website. Imagine your SQL query to login looked something like:

SELECT * FROM users WHERE user_name = '$username' AND password = '$password';

If you are not checking and cleaning the input appropriately, someone could type the snippet above into your login form and, if multiple queries were allowed, MySQL would execute the following:

SELECT * FROM users WHERE user_name = ''; DELETE * FROM users; AND password='';

Since the empty string wouldn’t match any rows (hopefully), the first query would be discarded. The second query, the DELETE statement, would run, terminating at the second semicolon. Since the third piece of code is nonsense, MySQL would throw it out with an error.

To solve this problem, languages like PHP cause MySQL to issue an error any time there is more text (except comments) after the line terminator, usually the semicolon. The downside is that situations arise where you need to run multiple queries. The result is either often either a godawfully complicated statement with multiple JOINs, or running several queries, each of which requires communication with your database server and can slow down your applications.

In the examples below, I’ll pretend we’re building a forum that has four tables:

• users with primary key user_id
• forums, a list of all the boards, with primary key forum_id
• threads which links each thread to a forum with forum_id and has primary key thread_id
• posts which links each post to a thread with thread_id and has primary key post_id

Subqueries in Select Expressions

One way to speed up your queries again is to use subqueries. Subqueries are full SQL queries nested within another query. For example:

SELECT (SELECT * FROM t1);

Obviously it’s a pretty simple example. Notice the parentheses. Subqueries must always be in parentheses, even if they are inside a function, like:

SELECT MAX((SELECT salary FROM employees));

Let’s get to work on our forum. Say that while reading all the threads of a forum you’d like to have both the number of threads and the number of posts in the forum. One way is to run two separate queries:

SELECT COUNT(*) AS threads FROM threads WHERE forum_id='1';
SELECT COUNT(*) AS posts FROM posts LEFT JOIN threads USING(thread_id) WHERE forum_id='1';

That might not be so bad if your SQL server is localhost, but more and more hosts are running dedicated SQL servers, meaning that every query has to run across the internet, be processed, and run back, slowing down your application. But we can run this in one query with two subqueries:

SELECT
(SELECT COUNT(*) FROM threads WHERE forum_id='1') AS threads,
(SELECT COUNT(*) FROM posts LEFT JOIN threads USING(thread_id) WHERE forum_id='1') AS posts;

We can add the above to our query to get the name of the forum and its description, so we can further decrease the number of trips to the database:

SELECT
(SELECT COUNT(*) FROM threads WHERE threads.forum_id=forums.forum_id) AS threads,
(SELECT COUNT(*) FROM posts LEFT JOIN threads USING(thread_id) WHERE threads.forum_id=forums.forum_id) AS posts,
forum_name,
forum_description
FROM forums WHERE forum_id='1';

Notice that we also changed the WHERE clauses to match whatever forum ID we put into the “outer query“.

Subqueries in Where Clauses

Another simple and useful way to use a subquery is in a WHERE clause. Here you must be careful to match the WHERE syntax and the type of data returned by the subquery. For example, in WHERE user_name = (...), the subquery ((...)) must return a single value, while in WHERE post_date IN (...), the subquery can return a list.

In our forum, we might want to search for all posts by a specific user, but we don’t want our visitors to need to know the user ID—or perhaps we want a more descriptive URL, like search.php?user=USER_NAME instead of search.php?user=#ID#. But in our forum, to be efficient, we link posts to their author by the user_id column.

One way to do this is to run a query to find the ID then run another query to find the posts. Another way in this particular case is to use a JOIN statement. But yet another way is to do this:

SELECT * FROM posts WHERE user_id = (SELECT user_id FROM users WHERE user_name = 'foo');

In the case above, a JOIN would also get us the information we want, but in some cases this isn’t true, for example:

SELECT column1 FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);

When you need to COUNT or otherwise aggregate one column, you’ll need to use a subquery instead of a JOIN, as well.

Summary

This article only scratched the surface of subqueries. Subqueries can be nested, they can appear in other places and do other things, and they can make your SQL more readable, among others. I don’t claim that the SQL statements above are the world’s most efficient or best way to do things—if you know a better way, let me know! I just want to give an introduction to subqueries, a very basic part of SQL that few people I’ve met seem to understand.

Tags: Database, MySQL, subquery
Posted in Code, Database, How To, MySQL