If you want something as simple as [insert photo here], you could look through the Photo Fun Software and Creative Photo-Frame Software categories on this site, but you'll be limited by the designs provided in most of this software. Many of the Home Publishing and Creative Printing programs also have pre-designed templates that could be adapted into mock magazine covers and sports card layouts.
Another simple solution is an online site where you just upload your photo, fill in the blanks, and click create cover. The image shown here was created this way.
Create a Customized Magazine from your Digital Photographs
Create a Customized Trading Card from your Digital Photographs
More Flickr Toys: movie posters, framed photos, mosaics, ID badges, calendars, and more.
If you're up to designing your own layouts, you could use just about any photo-editing or home publishing software, and you'll have a lot more flexibility and creative options.
With photo-editing software you will get all the tools necessary to create every element of your mock magazine spread, but it probably won't have clip art or all the layout and text formatting options that more advanced page layout software will have.
Before You Buy a Photo Editor
Home Publishing Software
With home publishing software you will have access to a lot of pre-designed elements which you can combine with your own photos, but you usually don't get very good photo-editing tools for touch up and photo manipulation.
Before You Buy Home Publishing Software
Microsoft's Digital Image Suite has a lot of creative project templates in addition to photo editing tools, so it might be the best of both worlds.
Creating fun magazine covers and sports trading cards for your kids is not that far off from digital scrapbooking, so you might want to look through that section of the site for inspiration and ideas.
Join the discussion to ask a question or contribute to this answer.