The Google Maps API provides a sophisticated way for the developers to add custom interactive maps to the websites.
Here is an easy way to add a map with a set of dynamic location markers (locations from a MySQL database).
Each marker will have an associated info bubble with the name/address of the location.
The code seems easily customize to add more features like images, View More links etc into the info bubble.
Also see here how to catch the latitude and longitude coordinates from a certain post/zip code or address using the Google API and then dynamically show an embed Google Map.