Maintenance - Custom Event Media Links

From TNG_Wiki
Revision as of 17:07, 1 May 2014 by HiTowerUK (talk | contribs)

Jump to: navigation, search

Purpose

Custom event replaced with large number
There has been a few randomly reported instances of media links to custom events becoming broken.
Broken link to a custom event
This can be difficult to find and only reveals itself when media items that were previously linked to people and events, no longer appear on an individual's 'getperson' pages. Another symptom of this problem is that media items that were originally linked to a custom event, appear in the "All Media" report with a large 6, 7 or 8 digit number instead of the event that they were originally linked to. Another way of identifying these broken links is by taking a look at the media links section in "Admin | Media | (Action)Edit". A broken media link can be easily identified by a colon where the link used to be. See the graphic on the right for what that looks like. Under the "Event" heading the custom event would be detailed but in this instance all that remains is a colon (:).

If there are a large number of linked media items, it can be difficult (and tedious) to have to go through an entire list of media to be sure that all the links are still valid. For now there doesn't appear to be an easy solution but there is a way to identify broken links utilising the SQL query below and creating a report of it. Here's how.

Creating the Report

  • In Admin/Reports select the 'Add New' tab.
  • Enter a Report Name and Description:

Broken links new report.jpg

  • Select the 'No' radio button against 'Active' (this ensures the report is viewable only by the system administrator).
  • Copy the SQL code listed below and paste in the last text box (above the 'Save Report' button).

Broken links sql box.jpg

  • Save the Report.


SQL Code

SELECT
CONCAT('<a href=\"showmedia.php?mediaID=',
m1.mediaID, '&tree=', m1.gedcom, '\" target=\"_blank\">',
m1.description, '</a>') AS MediaDescription
,CASE WHEN m2.personID LIKE 'I%' THEN CONCAT('<a href =
\"getperson.php?personID=', m2.personID, '&tree=', m2.gedcom, '\"
target=\"_blank\">', m2.personID, '</a>')
      WHEN personID LIKE 'F%' THEN
CONCAT('<a href = \"familygroup.php?familyID=', m2.personID,
'&tree=', m2.gedcom, '\" target=\"_blank\">',
m2.personID, '</a>')
      WHEN m2.personID IS NOT NULL THEN
m2.personID
      ELSE ''
END AS PersonLinkedTo
,CASE WHEN e2.display IS NOT NULL THEN e2.display
      WHEN m2.eventID IS NOT NULL THEN
m2.eventID
      ELSE ''
END as EventLinkedTo
FROM
tng_media m1
LEFT JOIN
tng_medialinks m2
ON
m1.mediaID = m2.mediaID
LEFT JOIN
tng_events e1
ON
m2.eventID = e1.eventID
LEFT JOIN
tng_eventtypes e2
ON
e1.eventtypeID = e2.eventtypeID
WHERE e2.display IS NULL AND m2.eventID > 0
ORDER BY
EventLinkedTo DESC

Viewing and Using the Report

After saving the report changes, click the 'Test' button. The report will look similar to this. The media item and the person linked to are both links. This makes correcting the problem somewhat easier.

Broken links report run.jpg


Acknowledgements

Huge thanks is due to Warren S Gilbert for developing this code and making it available to all.