8800 GS/GSO 9600GT 9800 GSO are pretty cheap, don't pay more than $100 for the GS/GSO models, and think seriously before spending more than $120 on the 9600 models.
The HD 4850 from AMD / ATi is the current value leader, I recommend it.
If you just want to play games then find a 7900 GS or GT for $45 with $10 shipping:
http://search.ebay.com/search/search.dll?s...ntrypage=search
I cannot recommend the 9400GT card, it has a 128 bit memory bus with DDR2 and only 16 stream processors.
The 7900 series has DDR3 with a 256 bit memory bus and 20-24 shaders, it is nearly the same, only faster.
You do need a PC with a PCI-E slot for a decent video card. DO NOT buy an AGP card for more than $40. Do NOT get a card with DDR2 as its memory.
You can get a PCI-E motherboard for $40, and a Core2 Celeron for $35, 2GB DDR2 is only $30 (if you don't currently have DDR2 that is), that is practically a whole PC upgrade right there.
Worst mistake is spending too much money on outdated components, Pentium 4/D processors, DDR (not DDR2) ram, Video cards on AGP or with DDR2 memory. No money should go into upgrading that stuff.
Usually though your current case and power supply can take an upgrade to all those components for $150-$200 (including the stock copper core heatsink from a Core2 Quad to overclock the Celeron to 2.4Ghz), and then you get much better performance than just chucking a $100 AGP video card at a wheezy Socket 478 system.
Do post your current system specs though