Eh... you could have posted in Video. HTPC stuff seems pretty evenly split between Video and GH.
Depends on what you want to do, I guess. Any computer equipped with a video card that has an S-Video or Composite out can be hooked up to a TV with appropriate inputs. Almost every non-integrated graphics card built in the last few years has this (as well as dual monitor support, so you can use the TV as a second monitor). You don't need a very fast video card, although some of the really cheap ones have lousy picture quality.
If you have an HDTV that takes component inputs, any of the more recent RADEON cards (from the 9600 up) can use an adapter to output component video from their DVI port. If you have a very new TV that can take DVI or HDMI, you can hook up directly to that from the DVI output (though I think you need some sort of adapter for HDMI) of just about any video card. This will let you output on your HDTV in any resolutions it supports (generally, 480p, 720p, 1080i).
Doing either of those would let you watch video files or play games on your TV (and, if you add a DVD drive, you can watch DVDs through the computer as well). If you want the computer to be able to record video, you'll need a video capture card. There are a number of different models, and going into that is sort of beyond the scope of one post. You can even get OTA HDTV capture cards these days, and, when equipped with appropriate PVR software, a system like this can do everything TiVo does and more. I'd try a search for "video capture card" and/or "PVR" if you're interested in this. You'll also need a LOT of hard drive space to capture video; you're talking 1GB+/hour for even fairly low quality settings. High-quality compressed video runs in the 4-6GB/hour range, and uncompressed video is several times that. I don't even want to think about how much space HDTV capture must take.
It generally does not look like crap (although the old 'Garbage In, Garbage Out' maxim certainly applies). If you have an HDTV, you can actually use a system like this as a programmable line doubler/video scaler to upsample standard-def signals and DVDs to HDTV resolutions. Programs like DScaler and FFDShow let you apply very sophisticated video filters and do all sorts of scaling and resampling.