The vast majority of us are not going to be famous - probably for anything, and certainly not for our photography. Arguably, since you are reading this, I at least have some notoriety and have had images published and published two books and a third on the way, so am perhaps not the right person to write about fame, or more accurately the lack of it. That said, truth is, through the by far biggest part of my photographic years I was entirely unknown, so do speak from experience, and not that long ago either.
Truth is, most people who achieve a goal very rapidly take it for granted and set a new, even tougher goal to achieve, so basking in one's fame, at whatever level, is fleeting at best.
Add to this the problem that john q public isn't able to appreciate all that our work offers. I know I certainly didn't when younger have the eye to appreciate much of the work I now admire, partly through education, largely through experience. For many of us, it is sharing our work with other photographers (and with non photographers who can appreciate all that our work means) that gives us the biggest kick.
For a lot of us, we never shared our work before the days of the internet - we didn't belong to a club, we didn't submit to magazines (or didn't get accepted), and our best work sat for years hidden away in old printing paper boxes. Satisfaction came from solving problems and getting things right, and also simply enjoying a beautiful print of our own, and feeling that it held up against the work of others we had seen, in books and at exhibitions.
Given the choice of making a photograph that is meaningful for myself or someone else, I'd pick me, every time. I have images which have demonstrably failed to impress in the public domain and despite not only lack of enthusiasm but downright and legitimate criticism; continue to 'work' for me.
This 'working for me' can happen at any level of skill and if someone has no knowledge of how wonderful a photograph can be, they may in fact be happy with what many would consider quite mediocre images. But does that actually matter? As long as they aren't trying to foist their poor images on the rest of us and feel satisfied in isolation, who are we to criticize. If later, they find out what really good images look like, and change their minds about their 'early' work, well, moving on and learning and getting better is all worthwhile. If a few of their early images continue to be important to them, that's lovely.
If a photographer thinks one of their images is terrific and the rest of us could or would disagree it doesn't matter. Most golfers tell me that they compete against themselves far more than the compete against the other players - it's about improving their game. I think there are a lot of reasons why photography should NOT be a competitive sport.