CMS Home | Docs Index

ECCO DB Table to CGI Map

Live DB: eccoprod

Dev DB: eccodev

Data table CGI column: distinct values
acronyms acronyms.cgi
contentLocs search.cgi contentType: table, file
datasets datasets.cgi
dataset_keyword datasets.cgi maps datasets.id to keywords.id
documents documents-more.cgi, meetings-documents.cgi
employment employment.cgi just one record in table
faqs faqs.cgi
faq_categories faqs.cgi
featured publications-featured.cgi
publications-featured-more.cgi
test/check_featured.cgi (Error 500)		 type: image, movie, storymap
category: NULL, Air-Sea/Climate, Biology/Carbon, Circulation, Other, Polar
geodeticVariables geodetic-variables.cgi
geo_categories geodetic-variables.cgi
keywords datasets.cgi
media home.cgi
media.cgi
media_more.cgi
publications-featured.cgi
publications-featured-more.cgi
storymaps.cgi
e-brochures.cgi		 type: image, e-brochure, movie, storymap
category: {no values in db}
gallery: Y, {no value}
meetingDocs meetings.cgi
meeting-documents.cgi		 type: presentation, abstract
category: osm2020, agu2019, ecco2018, agu2018, osm2018, ecco2017
meetings meetings.cgi
meeting-documents.cgi		 category: agu2018, agu2019, ecco2017, ecco2018, ecco2023, ecco2024, osm2018, osm2020, osm2024
people publications-featured.cgi linkLoc: institution, name
people_storymaps publications-featured.cgi `people_storymaps`.`peopleId` = `people`.`peopleId`
publications publications.cgi type: Journal Article, Generic, Magazine Article, Book Section, Report, Conference Proceedings, Thesis, Book
school summer-school.cgi type: document, movie
category: ss2019
shows not used directly anywhere but shows.showId <==> slidesRotator.showId
slidesRotators home.cgi
analysis-tool.cgi
data-analysis-tool.cgi 		distinct `showId` = {0, 1, 2}
-- showId = 0; not found in cgi, use this to disable display?
-- showId = 1; analysis-tool.cgi, data-analysis-tool.cgi
-- showId = 2; home.cgi
slidesSliders
(obsolete)		 publications-featured.cgi -- `orderSort` is set to 0 for all records
-- `orderDate` is used when ordering the output
-- `showId` = 3 for all records
updates updates.cgi
updates-more.cgi 		category: {no values}
type: new, event
users
whatsNew home.cgi type: globe, job, featured, event, news, events, default
visible: N, Y

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
about.cgistatic page
acronyms.cgi SELECT * FROM acronyms ORDER BY name;
adjoint-overview.cgistatic page
adjoint.cgistatic page
analysis-tool.cgi SELECT * FROM slidesRotators WHERE showId='1' ORDER BY slideOrder
analysis-tools.cgistatic page - has hardcoded details from media.mediaId=69
contact.cgistatic page
data-analysis-tool.cgi SELECT * FROM slidesRotators WHERE showId='1' ORDER BY slideOrder
data.cgistatic page
datasets.cgi SELECT keyword FROM keywords WHERE dict=?;
SELECT keyword FROM keywords WHERE dict=? ORDER BY sortOrder;
SELECT * FROM datasets ORDER BY sortOrder
SELECT keyword FROM keywords k, dataset_keyword dk WHERE dk.d_id=? AND dk.k_id=k.id AND k.dict IN ('grid','time','topic');
documents-more.cgi FILE exists only on DEV site
e-brochures.cgi?id=$id SELECT * FROM media WHERE mediaId='$id';
e-brochure-ecco.cgi static page
ecco2024recordings.cgi static page
employment.cgi SELECT * FROM employment WHERE dateEnd >= $currentDate ORDER BY 'dateAdded DESC'; {this SQL is broken}
faqs-more.cgiSELECT * FROM faqs WHERE faqsId='$id'
faqs.cgi
  • SELECT * FROM faq_categories ORDER BY sortOrder;
  • SELECT * FROM faqs WHERE category = '$abbTitle' ORDER BY listOrder;
geodetic-variables.cgi
  • SELECT * FROM geo_categories WHERE gallery = 'T' ORDER BY sortOrder;
  • SELECT * FROM geodeticVariables WHERE (category = '$category' AND gallery = 'T') ORDER BY sortOrder DESC;
home.cgi Rotator SELECT * FROM slidesRotators WHERE showId='2' ORDER BY slideOrder
What's New SELECT * FROM whatsNew WHERE visible = 'Y' ORDER BY sortOrder ASC LIMIT 5
Featured Publications SELECT title,shortDesc,imageThumb,imageAltTag,url,urlSource,type,archive,dateAdded,featuredid FROM `featured` WHERE `type` = 'storymap' AND `archive` != 'Y' ORDER BY dateAdded DESC LIMIT 6
Latest in Gallery SELECT * FROM media WHERE gallery = 'Y' ORDER BY date DESC LIMIT 1
media-more.cgi SELECT * FROM media WHERE mediaId='$id'
media.cgi Count All Rows SELECT * FROM media WHERE gallery = 'Y';
Default Order SELECT * FROM media WHERE gallery = 'Y' ORDER BY date DESC;
User-selected Order
(type, title, date)		 SELECT * FROM media WHERE gallery = 'Y' ORDER BY $orderBy DESC;
meetings.cgi
  • SELECT * FROM meetings ORDER BY dateEnd DESC;
  • SELECT * FROM meetingDocs WHERE category LIKE '%$category%';
meetings-documents-more.cgi
  • SELECT * FROM meetingDocs WHERE docId='$id'
  • SELECT * FROM meetings WHERE category = '$category'
meetings-documents.cgi
  • SELECT * FROM meetings WHERE category='$id';
  • SELECT * FROM meetings WHERE category='$sortId';
  • SELECT * FROM meetingDocs ORDER BY 'title';
  • SELECT * FROM meetingDocs WHERE category LIKE '%$id%' ORDER BY 'title';
  • SELECT * FROM meetingDocs WHERE category LIKE '%$sortId%' ORDER BY 'title';
  • SELECT * FROM meetingDocs WHERE category LIKE '%$sortId%' ORDER BY $orderBy;
ohc.cgistatic page
products-ECCO-V4r4.cgistatic page
products.cgistatic page
  • SELECT * FROM featured WHERE featuredId='$id';
Latest Featured Pubs SELECT p.fullName, p.institution, p.url, p.linkLoc, p.imageThumb,
ps.mediaId AS 'storymapId',
f.featuredId, f.title AS 'title', f.citation AS 'citation',
f.url AS storymapUrl, f.urlSource AS urlSource, f.category AS category
FROM `people` AS p , `people_storymaps` AS ps, `featured` AS f WHERE p.peopleId = ps.peopleId
AND ps.featuredId != 0 AND ps.featuredId = f.featuredId
ORDER BY f.dateAdded DESC LIMIT 2
Random Featured Pub, not already displayed SELECT p.fullName, p.institution, p.url, p.linkLoc, p.imageThumb,
ps.mediaId AS 'storymapId',
f.featuredId, f.title AS 'title', f.citation AS 'citation', f.url AS storymapUrl,
f.urlSource AS urlSource, f.category AS category
FROM `people` AS p , `people_storymaps` AS ps, `featured` AS f WHERE p.peopleId = ps.peopleId
AND ps.featuredId != 0 AND ps.featuredId = f.featuredId
AND f.featuredId NOT IN ($id_list)
ORDER BY rand() DESC LIMIT 2
All Featured Pubs
filter by topic (category) 		SELECT p.fullName, p.institution, p.url, p.linkLoc, p.imageThumb, ps.mediaId AS 'storymapId',
f.featuredId, f.title AS 'title', f.citation AS 'citation', f.url AS storymapUrl,
f.mediaFile AS mediaFile, f.urlSource AS urlSource, f.category AS category,
f.imageThumb AS imageThumb, f.imageAltTag AS imageAltTag, f.shortCitation AS 'shortCitation'
FROM `people` AS p , `people_storymaps` AS ps, `featured` AS f WHERE p.peopleId = ps.peopleId
AND ps.featuredId != 0 AND ps.featuredId = f.featuredId
ORDER BY f.dateAdded DESC
All archived and not storymaps SELECT * FROM featured WHERE archive = 'Y' AND `type` != 'storymap' ORDER BY dateAdded DESC
publications.cgi
  • SELECT * FROM publications;
  • SELECT * FROM $contentName ORDER BY date DESC, dateAdded DESC; // $contentName = 'publications'
science-support.cgistatic page
  • SELECT contentType, path, contentName, contenttTitle FROM contentLocs WHERE activebool ORDER BY contentType, path, contentName;
ssh-linear-trend.cgistatic page
state-estimation.cgistatic page
storymaps.cgi
  • SELECT * FROM media WHERE mediaId='$id'
summer-school.cgi
  • SELECT * FROM school WHERE date < '2019-05-27' ORDER BY date,time LIMIT 4;
  • SELECT * FROM school WHERE date < '2019-05-27' ORDER BY date,time LIMIT 200 OFFSET 4;
support.cgistatic page
teams.cgistatic page
timeline.cgistatic page
updates-more.cgi
  • SELECT * FROM updates WHERE updateId = '$id';
updates.cgi
  • SELECT * FROM updates ORDER BY date DESC LIMIT 3;
  • SELECT * FROM updates WHERE date >= '$cutoff' ORDER BY date DESC LIMIT 2000 OFFSET 3;
  • SELECT * FROM updates WHERE date < '$cutoff' ORDER BY date DESC;
user-guide-v4r3.cgistatic page
user-guide-v4r4.cgistatic page
what-moves-ecco.cgistatic page
why_trust_ecco.cgistatic page
world-of-ecco.cgistatic page