I do this all of the time (well, when I had time), using an older-model WinTV PCI card. It has both Composite and S-Video (and tuner) inputs. The image quality is definately a bit cleaner when using S-Video, I would highly recommend it, if possible. It's also useful for recording gameplay movies. The only downside, is the issue of display latency vs. quality, in up-converting the interlaced signal from the console, into a non-interlaced display signal for your CRT. The best compromise for simply playing, is to disable any sort of software "enhancements" to the video, and let the TV viewer app switch the PC's CRT/video card into a 640x480 full-screen interlaced mode. Then it looks mostly just like if you were using your CRT as a big television for your console.
If all you want to do, is play PSX games on your PC, you might also look into the ePSXe emulator. Given sufficiently-powerful PC hardware, it can actually look *better* than a real PSX, due to texture filtering/smoothing. You can also use your PC's TV-out video feature (if it has one), to pipe the output back to a real TV as well.