load->database(); } public function get_persons(){ /* first query part without birth & death place $sql = "SELECT full_name, title, firstname, patronym, prefix, surname, organization, gender, birth_date_modifier, birth_date_begin, birth_date_end, death_date_modifier, death_date_begin, death_date_end FROM persons WHERE identical_to = 0 "; */ $sql = "SELECT concat('http://www.vondel.humanities.uva.nl/ecartico/persons/',pe.id) as uri, full_name, title, firstname, patronym, prefix, surname, organization, gender, birth_date_modifier, birth_date_begin, birth_date_end, death_date_modifier, death_date_begin, death_date_end, pl1.name AS birthplace, pl2.name AS deathplace FROM persons AS pe LEFT JOIN place AS pl1 ON pe.birth_place = pl1.id LEFT JOIN place AS pl2 ON pe.death_place = pl2.id WHERE identical_to = 0 "; if(isset($_GET['name']) && $_GET['name']!=""){ $name = str_replace(","," ",$_GET['name']); $name = str_replace(" "," ",$name); $names = explode(" ",$name); foreach($names as $name){ if(!isset($_GET['fuzzyMatching']) || $_GET['fuzzyMatching']==true){ $name = regex_name($name); } $name = $this->db->escape_str($name); $sql .= "AND full_name REGEXP '" . $name . "' "; } } if(isset($_GET['firstname']) && $_GET['firstname']!=""){ $name = str_replace(","," ",$_GET['firstname']); $name = str_replace(" "," ",$name); $names = explode(" ",$name); foreach($names as $name){ if(!isset($_GET['fuzzyMatching']) || $_GET['fuzzyMatching']==true){ $name = regex_name($name); } $name = $this->db->escape_str($name); $sql .= "AND firstname REGEXP '" . $name . "' "; } } if(isset($_GET['patronym']) && $_GET['patronym']!=""){ $name = str_replace(","," ",$_GET['patronym']); $name = str_replace(" "," ",$name); $names = explode(" ",$name); foreach($names as $name){ if(!isset($_GET['fuzzyMatching']) || $_GET['fuzzyMatching']==true){ $name = regex_name($name); } $name = $this->db->escape_str($name); $sql .= "AND patronym REGEXP '" . $name . "' "; } } if(isset($_GET['surname']) && $_GET['surname']!=""){ $name = str_replace(","," ",$_GET['surname']); $name = str_replace(" "," ",$name); $names = explode(" ",$name); foreach($names as $name){ if(!isset($_GET['fuzzyMatching']) || $_GET['fuzzyMatching']==true){ $name = regex_name($name); } $name = $this->db->escape_str($name); $sql .= "AND surname REGEXP '" . $name . "' "; } } if(isset($_GET['birthdateFrom']) && $_GET['birthdateFrom']!=""){ $sql .= "AND (birth_date_begin >= '" . $this->db->escape_str($_GET['birthdateFrom']) . "' OR birth_date_end >= '" . $this->db->escape_str($_GET['birthdateFrom']) . "') "; } if(isset($_GET['birthdateBefore']) && $_GET['birthdateBefore']!=""){ $sql .= "AND ((birth_date_end <= '" . $this->db->escape_str($_GET['birthdateBefore']) . "' AND birth_date_end <> '0000-00-00') OR (birth_date_begin <= '" . $this->db->escape_str($_GET['birthdateBefore']) . "')) "; } if(isset($_GET['deathdateFrom']) && $_GET['deathdateFrom']!=""){ $sql .= "AND (death_date_begin >= '" . $this->db->escape_str($_GET['deathdateFrom']) . "' OR death_date_end >= '" . $this->db->escape_str($_GET['deathdateFrom']) . "') "; } if(isset($_GET['deathdateBefore']) && $_GET['deathdateBefore']!=""){ $sql .= "AND (death_date_begin <= '" . $this->db->escape_str($_GET['deathdateBefore']) . "' OR (death_date_end <='" . $this->db->escape_str($_GET['deathdateBefore']) . "' AND death_date_end <> '0000-00-00')) "; } if(isset($_GET['birthplace']) && $_GET['birthplace']!=""){ $sql .= "AND pl1.name like '" . $this->db->escape_like_str($_GET['birthplace']) . "%' "; } if(isset($_GET['deathplace']) && $_GET['deathplace']!=""){ $sql .= "AND pl2.name like '" . $this->db->escape_like_str($_GET['deathplace']) . "%' "; } if(isset($_GET['gender']) && $_GET['gender']!=""){ $sql .= "AND gender = '" . $this->db->escape_str($_GET['gender']) . "' "; } // order by's tested with limit 10,21000 //$sql .= "ORDER BY surname, firstname, patronym ASC "; // approx. 1.7 secs, even with indexes //$sql .= "ORDER BY surname ASC "; // approx. 0.25 secs //$sql .= "ORDER BY id ASC "; // approx. 0.05 secs if(isset($_GET['start'])){ $start = $_GET['start']; }else{ $start = 0; } if(isset($_GET['limit'])){ $limit = $_GET['limit']; }else{ $limit = 100; } $sql .= "LIMIT " . $this->db->escape_str($start) . "," . $this->db->escape_str($limit) . " "; $query = $this->db->query($sql); //echo $sql; return $query->result_array(); } public function get_persons_by_URI(){ $sql = "SELECT o.uri AS uri_found, concat('http://www.vondel.humanities.uva.nl/ecartico/persons/',p.id) as uri, full_name, title, firstname, patronym, prefix, surname, gender, birth_date_modifier, birth_date_begin, birth_date_end, death_date_modifier, death_date_begin, death_date_end FROM otherme AS o LEFT JOIN persons AS p ON o.person_id = p.id WHERE p.identical_to = 0 "; if(isset($_GET['uri']) && $_GET['uri']!=""){ $sql .= "AND uri REGEXP '" . regex_uri($this->db->escape_str($_GET['uri'])) . "' "; } if(isset($_GET['domain']) && $_GET['domain']!=""){ $sql .= "AND uri REGEXP '^" . $this->db->escape_str($_GET['domain']) . "' "; } //$sql .= "ORDER BY p.id ASC "; if(isset($_GET['start'])){ $start = $_GET['start']; }else{ $start = 0; } if(isset($_GET['limit'])){ $limit = $_GET['limit']; }else{ $limit = 100; } $sql .= "LIMIT " . $this->db->escape_str($start) . "," . $this->db->escape_str($limit) . " "; $query = $this->db->query($sql); return $query->result_array(); } } /* end of file */