nsr/prepare_observations.php at master · EnquistLab/nsr · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<?php

/////////////////////////////////////
// Populates missing family and genus
// in observations table
/////////////////////////////////////

//include "dbw_open.php";

// "species" is family name
$sql="
UPDATE observation
SET family=species
WHERE $JOB_WHERE_NA
AND (family IS NULL OR TRIM(family=''))
AND species LIKE '%aceae'
;
";
sql_execute_multiple($dbh, $sql);

// Family but no species
// Copy family to species column
$sql="
UPDATE observation
SET species=family
WHERE $JOB_WHERE_NA
AND (family IS NOT NULL AND TRIM(family=''))
AND (species IS NULL OR TRIM(species=''))
;
";
sql_execute_multiple($dbh, $sql);


// "species" not a family name
$sql="
UPDATE observation
SET genus=strSplit(species,' ',1)
WHERE $JOB_WHERE_NA
AND (genus IS NULL OR TRIM(genus=''))
AND species NOT LIKE '%aceae'
;
";
sql_execute_multiple($dbh, $sql);

// Populate family if missing and genus has only
// one family
$sql="
UPDATE observation o JOIN gf_lookup b
ON o.genus=b.genus
SET o.family=b.family
WHERE $JOB_WHERE
AND (o.family IS NULL OR TRIM(o.family=''))
AND (o.genus IS NOT NULL AND TRIM(o.genus<>''))
AND b.fams=1
;
";
sql_execute_multiple($dbh, $sql);

//include "db_close.php";

?>