Red Dead Redemption for sure. It has an amazing campaign, free-roam, and an endless supply of multiplayer modes. Also, Assassin's Creed: Brotherhood. It has all the same, though I haven't tried online yet. I wouldn't sell either one for my life. And, if you don't already have it, GTA 4 is amazing, of course. Another... Borderlands. It's more fun with friends, but you can have just as much fun by yourself. Plus, there's plenty of add-on content. Other than that, I'm not sure. Good luck!
Gran Turismo 5, Assassin's Creed Brotherhood online is amazing, Little Big Planet is surprisingly fun online, Portal 2 was funny, can't think of any others yet. As said, Red Dead Redemption was really good, same with Grand Theft Auto IV. I like Fallout 3 and New Vegas even though they are not online they have a ton of replayability since you can play the game entirely different everytime.
I'm playing Borderlands now but I find it boring... anyways, hope this helps
Some of the PlayStation 3's best games with online components include...
Warhawk - Online-only ground-to-air battles. Constant war. Lots of fun.
Portal 2 - Action-based puzzler. First-person portal-shooting mania. See if you're smart enough to figure your way to the exit alone or with a friend.
Uncharted 2: Among Thieves - Suspenseful action heightened by an amazing story. Blindfire gunplay merges with puzzle solving. Online, the game is your typically tacked-on Search and Destroy/Team Deathmatch types of modes.
Resident Evil 5 - Cooperatively offline and on, this action-survival entry once again tosses you into the mutant-infest world of Resident Evil. There's some competitive online in addition to the story stuff, where players must survive past a range of deadly creatures and from each other.