The pixels which make an image are arranged like the squares on graph paper. There are a certain number of squares down and a certain number of squares across. We could count them one by one or just multiply the number of pixels down by the number of pixels across. An image with 4 pixels down and 6 pixels across contains a total of 24 pixels. Of course, digital cameras don't take pictures that small. There are cameras which produce images 4 thousand pixels down and 6 thousand pixels across, for a total of 24 million pixels...or 24 megapixels, though most people will never need or use images of that size.
In reality, 5 to 6 megapixels is easily enough to print a great 8 x 10 photo or even enlarge up to 12 x 18 or so, and depending on the expected distace from the viewer to the image, much larger sizes can be printed. Movie posters are a perfect example of this. These posters are designed for "curb appeal", or the first and lasting impression from a casual viewing. The posters are large, high contrast and highly defined, but if viewed closely it is clear the resolution of these images is quite low relative to their size.
Camera manufacturers like to continually increase the number of megapixels in their cameras because it is an easy marketing number and the public attaches to this number more than any other feature of a digital camera. However, any camera available today (aside from the pre-packaged "kids" cameras) has at least enough megapixels for those great 8 x 10 photos. Beyond that, almost no one prints larger sizes unless they are a professional photographer selling prints requiring those sizes.
There is one slight advantage to higher megapixels for the average user: they allow the user to "crop" or "cut out" more of an image and still produce an acceptable print. However, one can only crop so much before the image is not large enough to print adequately and the difference between, for instance, 12 megapixels and 6 megapixels is not double the image dimensions as it may imply.
To simplify things, let us say we are looking at a 12 pixel image versus a 6 pixel image. The math works the same, just on a smaller scale. An image with 2 pixels down and 3 pixels across would contain a total of 6 pixels. A 12 pixel image sounds as though it should be twice the size, but if we look at an image which is 4 pixels down and 6 pixels across (twice the dimensions of a 6 pixel image) we see it contains 24 pixels, or 4 times as much. So where is our 12 pixel image?
It turns out an image 3 pixels down and 4 pixels across would be 12 pixels; not much larger than a 6 pixel image. This is because the math involved is called a "square function" and the total number of pixels or "image area" grows much faster than the length of the sides. A 12 pixel image does contain twice as many pixels as a 6 pixel image, but the overall height and width of the image turns out to be only about 40% larger, or only about 1.4 times as much. The difference between something like an 8 megapixel camera and a 10 megapixel camera is even less; about 13%, or 1.13 times, which is negligible.
So a larger megapixel camera does provide more pixels to crop or cut out from, but not many more, and probably not much of a difference for most people, just as while more megapixels can print larger photos, most people will never print larger than an 8 x 10, which 5 to 6 megapixels can easily accommodate.
The downside to all these megapixels camera manufacturers keep pushing at us is they take more space on a computer or memory card. While a 12 megapixel image is only 40% larger on a side than a 6 megapixel image, it does have twice the total pixels and does take up twice as much space. A memory card which could hold 500 6 megapixel images will only hold 250 12 megapixel images. Similarly, for people who want to put these photos on their digital picture frame, which can only display a fraction of a megapixel, they get half as many pictures with no benefit.
Lastly, camera manufacturers and sales people may imply more megapixels mean sharper, better quality photos. This is only true to a certain extent. More megapixels may produce sharper results at sizes larger than most people will ever print. Again, 5 to 6 megapixels is enough for most people. The difference between 6 megapixels and 12 megapixels on an 8 x 10 photo lab print is indistinguishable. Further, the quality of an image is dependant on far more than megapixels.
The quality of the "optics" (lens system) is at least as important, if not more so, especially at the size of print necessary to make higher megapixels even remotely useful. At those sizes, image quality is usually limited by the quality of the lens or the photographer's technique, not the megapixels, and consumer "point-and-shoot" cameras do not have the quality optics to take advantage of all those megapixels.
This is one area where more advanced digital SLR (Single Lens Reflex) cameras and their much higher quality detachable lenses exceed the point-and-shoot cameras. In fact, even a 6 megapixel SLR such as the Nikon D40 used for most of the photos on this site produces higher quality photos than a 10 or 12 megapixel point-and-shoot because there are many factors contributing to image quality and the ability to get the right image in the first place other than megapixels such as: lens quality, sensor size, sensor quality, low noise performance, greater manual controls, flash performance, metering, greater in-camera image processing, etc.
If one wishes to print images at poster sizes (24 inches x 36 inches), by all means look for a higher megapixel camera. Even then, good professional poster size prints can be made from an 8 megapixel SLR camera. For most people though, a decent, compact point-and-shoot will be all they ever need, and they will not print images which require higher megapixels or higher-end cameras for that matter.