Set living flag, fills places of birth etc.

From TNG_Wiki
Jump to navigation Jump to search

The following query does a number of things. It switches off the living flag people born before 1910 and puts a "y" when the death date is empty. Fills the birthdates with a "Y" in case it's empty.

You will have to put into the cust_text.php $dates[Y] = "Date unknown"; so it shows that you don't know the date yet. Switches on the living flag for people born after 1910 and where the death date is empty.So if you want to show certain people who are still living you can fill in an "N" there and add a variable to the cust_text.php

$dates[N] = "Living";


Fills the birthplace with the place of the baptism in case it's empty. Fills the baptismal place with the birth place in case a baptism date is given and the baptism place is empty. Links cemeteries to headstones in case it was forgotten. Also takes care that the birthdatetr and baptismdatetr are filled in the right way. Again, use this at your own risk.



Copy and paste the text between the lines into the MySQL section of your MySQL admin, IMPORTANT: Make a backup before you try anything and use this at your own risk!


UPDATE `tng_people` SET `deathdate` = "y" where `living` ="0" and `birthdatetr` < "1910-00-00" and `deathdate` = "";

UPDATE `tng_people` SET living = "0" where `living` ="1" and `birthdatetr` < "1910-00-00" and `deathdate` <> "";

UPDATE `tng_people` SET `birthdate` = "y" where `birthdate` = "" OR `birthdate` ="0";

UPDATE `tng_people` SET `deathdate` = "" AND `living` ="1" WHERE `birthdatetr` > "1910-00-00" and (`deathdate` = "y" OR `deathdate` = "Y");

UPDATE `tng_people` SET `living` = "1" WHERE `birthdatetr` > "1910-00-00" AND `deathdate` = "";

UPDATE `tng_people` SET `living` = "0" WHERE `birthdatetr` > "1910-00-00" AND (`deathdate` = "y" or `deathdate` <> "") and `living` = "1";

UPDATE tng_people SET birthplace = altbirthplace WHERE birthplace = "" and altbirthplace <> "";
UPDATE tng_people SET altbirthplace = birthplace WHERE (altbirthplace = "" and birthplace <> "" AND altbirthdate <> "");

UPDATE tng_places SET notes = REPLACE(notes,'[edit]','');

UPDATE tng_media SET linktocem ="1" where cemeteryID <> "0" AND mediatypeID ="headstones";

UPDATE tng_media SET showmap ="1" where cemeteryID <> "0" AND mediatypeID ="headstones";

UPDATE tng_people SET birthdate = YEAR(altbirthdatetr) WHERE (altbirthdate <> "" AND (birthdate = "y" OR birthdate = "Y"OR birthdate = ""));

UPDATE tng_people SET birthdatetr= CONCAT(YEAR(altbirthdatetr),"-00-00") WHERE (altbirthdate <> "" AND birthdatetr="0000-00-00" AND ((birthdate = "y" OR birthdate = "Y" OR birthdate = "") or char_length(birthdate) =4)) ;

This works more dynamically. Calculating from the Current date. If you think that people older than 90 might be set to death as well, you could replace 100 by 90. You could also set 110 years as cut-off.


UPDATE `tng_people` SET `deathdate` = "y" WHERE `living` ="0" AND year(birthdatetr) < (year( curdate( ) ) -100) AND `deathdate` = "";


UPDATE `tng_people` SET living = "0" WHERE `living` ="1" AND year(birthdatetr) < (year( curdate( ) ) -100) AND `deathdate` <> "";


UPDATE `tng_people` SET `birthdate` = "y" WHERE `birthdate` = "" OR `birthdate` ="0";

UPDATE `tng_people` SET `deathdate` = "" WHERE `deathdate` = "0";

UPDATE `tng_people` SET `deathdate` = "" AND `living` ="1" WHERE year(birthdatetr) > (year( curdate( ) ) -100) AND (`deathdate` = "y" OR `deathdate` = "Y");


UPDATE `tng_people` SET `living` = "1" WHERE year(birthdatetr) > (year( curdate( ) ) -100) AND `deathdate` = "";


UPDATE `tng_people` SET `living` = "0" WHERE year(birthdatetr) > (year( curdate( ) ) -100) AND (`deathdate` = "y" OR `deathdate` <> "") AND `living` = "1";


UPDATE tng_people SET birthplace = altbirthplace WHERE birthplace = "" AND altbirthplace <> "";

UPDATE tng_people SET altbirthplace = birthplace WHERE (altbirthplace = "" AND birthplace <> "" AND altbirthdate <> "");


UPDATE tng_places SET notes = REPLACE(notes,'[edit]','');


UPDATE tng_media SET linktocem ="1" WHERE cemeteryID <> "0" AND mediatypeID ="headstones";


UPDATE tng_media SET showmap ="1" WHERE cemeteryID <> "0" AND mediatypeID ="headstones";


UPDATE tng_people SET birthdate = YEAR(altbirthdatetr) WHERE (altbirthdate <> "" AND (birthdate = "y" OR birthdate = "Y"OR birthdate = ""));


UPDATE tng_people SET birthdatetr= CONCAT(YEAR(altbirthdatetr),"-00-00") WHERE (altbirthdate <> "" AND birthdatetr="0000-00-00" AND ((birthdate = "y" OR birthdate = "Y" OR birthdate = "") OR char_length(birthdate) =4)) ;