Unified database query for different TNG events

Initial Situation

Since days, I have been dissatisfied with the display of the different event types from TheNextGeneration on the homepage of our website.
So far, I was using a customized version of a script from Timo Kracke, where his version itself is based on code, which is hosted on Roger Moffats website. The key difference for Timo Krackes script is, that he combines various single scripts one huge PHP file. This script takes care for the display of the different information within separate sections.

I chose a design with three columns. With my intial modifications of the left column I had three <div> sections, which conducted separate database queries for the following lists:

  • list of birthdays
  • list of deathdays
  • list of anniversaires

In summary, this script did not provide a good overview for beginners. It was a quick&dirty result of copying from different sources, and some code was commented out or so. Even worse, I disliked the display of three separate sections. Especially with small databases, there is a risk that there might be empty sections displayed.

Wish for a combined query

In my imagination, there was a single list displaying of all events, sorted in chronological order. With the introduction of the genealogical signs, all lines would be identified and the separation into event type section is obsolete. At the same time, the output of the script will be much more compact.

There was only one hurdle: my lack of programming skills. Regarding PHP and SQL, I would have to explore a white map. By now, my little experience was limited to customization of single code lines or copying of entire <div> sections. I accepted the challenge, and after several hours of coding, I am satisfied with the result.

The result

The image below shows a direct comparison between the three separate queries and the new, unified query. The new output is significantly more compact. As you see, the genealogical signs are already implemented:

Comparison of old and new database query
Direct comparison of old database query (left) and the new, unified database query (right)

The script

The entire script will bloat this article. Thus, here is a brief description of the general approach. Also, the script can be downloaded below.

A large portion of the job is already done in the SQL statement. For example, SQL is already joining the displayed string of the hyperlink via CONCAT(). This helps to keep a simple output section in the PHP section. The SQL statement looks like this:

SELECT "*" as eventtype, gedcom, personID as linkID, CONCAT(firstname,' ',lastname) as hrefdisplay, birthdatetr as datedisplay 
   FROM tng_people 
   WHERE DATE_FORMAT(birthdatetr, '%m-%d') IN ('05-12', '05-13', '05-14', '05-15', '05-16', '05-17', '05-18') 
         AND living = "0" 

SELECT "&#x271d;" as eventtype, gedcom, personID as linkID, CONCAT(firstname,' ',lastname) as hrefdisplay, deathdatetr as datedisplay 
   FROM tng_people 
   WHERE DATE_FORMAT(deathdatetr, '%m-%d') IN ('05-12', '05-13', '05-14', '05-15', '05-16', '05-17', '05-18') 
           AND living = "0"

SELECT "~" as eventtype, gedcom, personID as linkID, CONCAT(firstname,' ',lastname) as hrefdisplay, baptdatetr as datedisplay 
   FROM tng_people 
   WHERE DATE_FORMAT(baptdatetr, '%m-%d') IN ('05-12', '05-13', '05-14', '05-15', '05-16', '05-17', '05-18')
         AND living = "0"

SELECT "oo" as eventtype, gedcom, familyID as linkID, CONCAT( (SELECT CONCAT(tng_people.firstname,' ',tng_people.lastname) as husbandname FROM tng_people WHERE tng_people.personID = tng_families.husband) ,' oo ', (SELECT CONCAT(tng_people.firstname,' ',tng_people.lastname) as wifename FROM tng_people WHERE tng_people.personID = tng_families.wife)) as hrefdisplay, marrdatetr as datedisplay 
   FROM tng_families 
   WHERE DATE_FORMAT(marrdatetr, '%m-%d') IN ('05-12', '05-13', '05-14', '05-15', '05-16', '05-17', '05-18') 
         AND living = "0"

ORDER BY DATE_FORMAT(datedisplay, '%m-%d'), DATE_FORMAT(datedisplay, '%y')

The script assesses the dates of the weekdays of the current week and compiles a comparison string for the conditional SELECT.
When using UNION ALL, please keep in mind that the number (and type) of columns for the different SELECT sections needs to match exactly.

Via this preparation, the output remains simple. The final IF clause is required because the hyperlinks for families and persons are different.

if( $rowCountnew > 0 ) { 
    while ($row = mysql_fetch_array($resultnew) ) {
        switch($row['eventtype']) {
        case "&#x26AD;":
                echo "<span class=\"tnglink\"><a href=\"/genealogie/familygroup.php?familyID=" . $row['linkID'] . "&amp;tree=" . $row['gedcom'] . "\">" . $row['hrefdisplay'] . "</a>   <span class=\"tngdatebracket\">( <span class=\"tngsymbol\">" . $row['eventtype'] . "</span> ". displaydate ( $row['datedisplay']) . ")</span></span>" . "<br />\n";
                echo "<span class=\"tnglink\"><a href=\"/genealogie/getperson.php?personID="   . $row['linkID'] . "&amp;tree=" . $row['gedcom'] . "\">" . $row['hrefdisplay'] . "</a>   <span class=\"tngdatebracket\">( <span class=\"tngsymbol\">" . $row['eventtype'] . "</span> ". displaydate ( $row['datedisplay']) . ")</span></span>" . "<br />\n";



Schreibe einen Kommentar