While the megapixel war has more or less cooled off, there are still sales people out there who are pushing the "more megapixels is better" line to first time digital camera buyers or digital camera owners who are upgrading from older digital cameras. I see this just about every day on our forums where people, when comparing a 6 megapixel camera to a 8 megapixel camera, that the 8 megapixel camera is the one to go with.
A great analogy for the whole progression of the ever-increasing megapixels is the computer processor industry. For a while, it was generally accepted that a faster (faster clock speed) processor meant a faster computer. Then, as different architectures were introduced, a processor could achieve the same, if not better performance, at a slower clock speed. Also, processor speed has progressed more quickly than what computer applications need, so we now have processors that are much faster than what most people use them for. If you just browse the web, do some word processing, and email, many of today's current computers are much faster than is needed.
A similar progression happened with digital camera sensor resolution, where, for a while, more megapixels were better since the rest of the camera also improved - better lenses, better image processing, etc. Then, the resolution out-paced what people really need. Quite simply, having more megapixels means that you can create a larger print (or that you can do more cropping and still have enough data left for a full-size print).
The size at which you can print depends on a couple factors, including the capabilities of the printer. According to this page at Microsoft's site (and you can find plenty of other references out there with some Google searches), their minimum recommended printing resolution is 240 ppi (pixels per inch). At 240ppi, a 5 megapixel image can create a maximum print size of 8.1 x 10.8 inches. With a higher quality print resolution (300ppi), you would need an 8 megapixel image to be able to print an 8x10, but a 3 megapixel image is all that's needed for a 4x6.
However, the mathematical requirements for a large print may be completely irrelevant as the differences in print quality may not even be noticeable to the untrained eye. Recently, David Pogue, a tech columnist for the New York Times, took the same image and made three 16x24 prints at a photo lab. One image was the full 13 megapixels, one was 8 megapixels, and one was 5 megapixels. He put up the posters in Times Square and asked people walking by if they could figure out which print was which. During his test, about 95% of the people could not tell the difference. One person correctly figured it out, but she was a photography professor. You can read more about it on his blog.
Why More Is Not Necessarily Better
In addition to producing more image data than you need for your uses, higher-megapixel sensors are not always of better quality. Typically, within a camera product line, the physical dimensions of the sensor stay the same from model to model. To achieve a higher resolution, more "photosites" must be packed onto the same size sensor. Advances in manufacturing and sensor technology allow this to even be possible. However, when the photosites become more densely packed onto the sensor, they start to affect each other - electrical signals can affect neighboring photosites.
Another downside to high-megapixel cameras is simply the file size of each image. While actual storage space is cheap these days, it will take longer to transfer images and it makes it harder to transfer full-size images to friends, family, and photo-sharing sites.
It's not all Doom and Gloom
Luckily, camera manufacturers know the consequences of packing more pixels onto a small sensor package and actively work to minimize any ill effects. Imaging processors are constantly improving, manufacturing processes are always improving, and the rest of the parts of the camera are getting better.
Bottom line - don't think that more megapixels means better image quality. Don't let that sales person talk you into a better model than the one you were planning to buy just on the basis of resolution. If you never print anything larger than a 4x6, you won't need anything larger than 5 megapixels. If you do a lot of cropping, then do consider getting a higher megapixel camera as there's more data there for you to crop.