CMS Home | Docs Index

Winds DB Table to CGI Map (may be incomplete)

Live DB: winds

Dev DB: windsdev

Data table CGI column: distinct values
foreign keys
cms_log2 CMS use only
contentLocs search.cgi
documents meetings-documents.cgi
media home.cgi, research.cgi, science-overview.cgi, storymaps.pl type: storymap, e-brochure
meetings meetings-list.cgi, meetings-documents.cgi foreign key: meetings.label = documents.category
meetingAttendees None
meetingSponsors None
news news.cgi, news-more.cgi mediaType: image, movie
onePagers research.cgi foreign keys: documentsId, publicationsId, mediaId
people meet-the-team.cgi
publications publications.cgi type: Journal Article, Report, Conference Paper, Chapter, Thesis, Book Chapter, Book
research meet-the-team.cgi foreign keys: teamId, peopleId
rotators home.cgi
teamMembers meet-the-team.cgi foreign keys: teamId, peopleId
teams meet-the-team.cgi
whatsNew home.cgi

Mapping CGI to SQL

The "static pages" still need to connect to the DB in order to fetch the "Last Updated" date for the footer on all pages.

CGISQL
storymaps.pl generates page for storymap passed as URL SELECT * FROM media WHERE type = 'storymap' AND gallery='Y' AND url = '/storymaps/$url'
/storymaps/beyond-the-blob.htm redirects to /storymaps.pl?url=beyond-the-blob.htm
/storymaps/vexed-by-hex.htm redirects to /storymaps.pl?url=vexed-by-hex.htm
/storymaps/ocean-motion-impacts-winds.htm redirects to /storymaps.pl?url=ocean-motion-impacts-winds.htm
/storymaps/ocean-wind-links-motion-in-our-seas-and-skies.htm redirects to /storymaps.pl?url=ocean-wind-links-motion-in-our-seas-and-skies.htm
/storymaps/salt-and-the-wind.htm redirects to /storymaps.pl?url=salt-and-the-wind.htm
home.cgi
  • SELECT COUNT(*) FROM rotators WHERE showName='home';
  • SELECT longTitle,caption,imageLarge,altTag,url FROM rotators WHERE showName='home' ORDER BY slideOrder;
  • SELECT title,url, sortOrder FROM whatsNew WHERE visible='Y' ORDER BY sortOrder DESC;
  • SELECT * FROM media WHERE type = 'storymap' AND gallery='Y' ORDER BY dateDisplay DESC limit 4;
meetings-list.cgi
  • SELECT * FROM meetings ORDER BY dateStart DESC;
meetings-documents.cgi
  • SELECT * FROM meetings WHERE label = '$id';
  • SELECT COUNT(*) FROM documents WHERE category = '$id';
  • SELECT * FROM documents WHERE category LIKE '$id' ORDER BY $orderField;
meet-the-team.cgi
  • SELECT p.fullName, t.startYear, t.endYear, p.title, p.email, p.bioUrl, p.institution, p.city, p.state, p.country, p.thumbName, p.firstName, p.lastName FROM people p, teams t, teamMembers tm WHERE t.id=5 AND tm.teamId=t.id AND tm.peopleId=p.id AND p.gallery='Y' ORDER BY tm.sortOrder,p.lastName;
  • SELECT p.fullName, p.institution, r.title FROM teamMembers tm LEFT JOIN teams t ON tm.teamId = t.id LEFT JOIN people p ON tm.peopleId = p.id LEFT JOIN research r ON r.teamId = tm.teamId AND r.peopleId = tm.peopleId WHERE tm.teamId=4 AND p.gallery='Y' ORDER BY tm.sortOrder, p.lastName;
  • SELECT p.fullName, p.institution, r.title FROM teamMembers tm LEFT JOIN teams t ON tm.teamId = t.id LEFT JOIN people p ON tm.peopleId = p.id LEFT JOIN research r ON r.teamId = tm.teamId AND r.peopleId = tm.peopleId WHERE tm.teamId=3 AND p.gallery='Y' ORDER BY tm.sortOrder, p.lastName;
  • SELECT p.fullName, p.institution, r.title FROM teamMembers tm LEFT JOIN teams t ON tm.teamId = t.id LEFT JOIN people p ON tm.peopleId = p.id LEFT JOIN research r ON r.teamId = tm.teamId AND r.peopleId = tm.peopleId WHERE tm.teamId=2 AND p.gallery='Y' ORDER BY tm.sortOrder, p.lastName;
  • SELECT p.fullName, p.institution, r.title FROM teamMembers tm LEFT JOIN teams t ON tm.teamId = t.id LEFT JOIN people p ON tm.peopleId = p.id LEFT JOIN research r ON r.teamId = tm.teamId AND r.peopleId = tm.peopleId WHERE tm.teamId=1 AND p.gallery='Y' ORDER BY tm.sortOrder, p.lastName;
missions-and-data.cgistatic
news.cgi
  • SELECT * FROM news ORDER BY dateAdded DESC LIMIT 3
  • SELECT * FROM news ORDER BY dateAdded DESC LIMIT 200 OFFSET 3
news-more.cgi SELECT * FROM news WHERE id='$id'
publications.cgi
  • SELECT * FROM publications ".$where." ORDER BY pubYear DESC, author ASC;
  • SELECT * FROM publications ".$where." ORDER BY title ASC;
  • SELECT * FROM publications ".$where." ORDER BY author ASC;
research.cgi SELECT o.*, d.title, d.dateAdded, d.documentName, d.imageName, d.thumbName, p.author, p.pubUrl, m.url FROM onePagers AS o LEFT JOIN documents AS d ON o.documentsId = d.id LEFT JOIN publications AS p ON o.publicationsId = p.id LEFT JOIN media AS m ON o.mediaId = m.id ORDER BY d.dateAdded DESC, p.author ASC
science-overview.cgi
  • SELECT * FROM media WHERE type = 'storymap' AND gallery='Y' ORDER BY dateDisplay DESC;
search.cgi
support.cgistatic
wind-compounds-ocean-extreme-events.cgistatic
wind-data-are-crucial-for-climate-research.cgistatic