function search_data_parent($data, $type, $id) { if ($type == 1) { $query = "SELECT PERSONNAME_OWNER_ID FROM personname LEFT JOIN person ON personname.PERSONNAME_OWNER_ID = person.ID WHERE (PERSONNAME_NAME LIKE '%{$data}%' OR PERSONNAME_SURNAME LIKE '%{$data}%') AND person.ID != {$id}"; } else { if ($type == 2) { $query = "SELECT person.ID FROM person LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID WHERE chinaname.CHINANAME_NAME LIKE '%{$data}%' OR chinaname.CHINANAME_PINYIN LIKE '%{$data}%' OR chinaname.CHINANAME_TH LIKE '%{$data}%'"; } else { if ($type == 3) { if (search_province_id($data) == 0) { $query = "SELECT * FROM province WHERE PROVINCE_ID = 0"; } else { $provice = implode(",", search_province_id($data)); $query = "SELECT addresslist.ADDRESSLIST_OWNER_ID FROM addresslist LEFT JOIN address ON addresslist.ADDRESSLIST_ADDRESS_ID = address.ID RIGHT JOIN person ON addresslist.ADDRESSLIST_OWNER_ID = person.ID WHERE address.ADDRESS_PROVINCE_ID IN ({$provice}) AND person.ID != {$id}"; } } else { if ($type == 4) { if ($data[0] == 0 && strlen($data) < 4) { $field = "CONTACT_ARER_CODE"; } else { $field = "CONTACT_STRING"; } // echo $field; $query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN person ON contact.CONTACT_OWNER_ID = person.ID WHERE {$field} LIKE '%{$data}%' AND CONTACT_TYPE_ID IN (1,2) AND person.ID != {$id}"; } } } } // echo $query; return $query; // $query .= "LIMIT $start, $end"; // echo $query; // return mysql_query($query); }
function search_ancestorsaddr($data, $type) { if ($type == 1) { $query = "SELECT PERSONNAME_OWNER_ID FROM personname LEFT JOIN person ON personname.PERSONNAME_OWNER_ID = person.ID WHERE (PERSONNAME_NAME LIKE '%{$data}%' OR PERSONNAME_SURNAME LIKE '%{$data}%') ORDER BY PERSONNAME_NAME"; } else { if ($type == 2) { if (strlen($data) == 3) { $query = "SELECT person.ID FROM person LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID " . "LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID " . "LEFT JOIN generation ON person.GENERATION_ID = generation.ID " . "WHERE chinaname.CHINANAME_NAME LIKE '%{$data}%' OR chinaname.CHINANAME_PINYIN LIKE '%{$data}%' OR chinaname.CHINANAME_TH LIKE '%{$data}%' OR " . "generation.GENERATION_NAME LIKE '%{$data}%' OR generation.GENERATION_PINYIN LIKE '%{$data}%' OR generation.GENERATION_TH LIKE '%{$data}%' ORDER BY PERSONNAME_NAME "; // echo $query; } else { if (strlen($data) == 6) { $gen = substr($data, 0, 3); $name = substr($data, 3, 6); // echo $gen; // echo "<br/>"; // echo $name; $query = "SELECT person.ID FROM person " . "LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID " . "LEFT JOIN generation ON person.GENERATION_ID = generation.ID " . "WHERE (chinaname.CHINANAME_NAME LIKE '%{$name}%' OR chinaname.CHINANAME_PINYIN LIKE '%{$name}%' OR chinaname.CHINANAME_TH LIKE '%{$name}%') AND " . "(generation.GENERATION_NAME LIKE '%{$gen}%' OR generation.GENERATION_PINYIN LIKE '%{$gen}%' OR generation.GENERATION_TH LIKE '%{$gen}%') ORDER BY PERSONNAME_NAME "; } } // $query = "SELECT person.ID FROM person LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID WHERE (chinaname.CHINANAME_NAME LIKE '%$data%' OR chinaname.CHINANAME_PINYIN LIKE '%$data%' OR chinaname.CHINANAME_TH LIKE '%$data%') "; } else { if ($type == 3) { if (search_province_id($data) == 0) { $query = "SELECT * FROM province WHERE PROVINCE_ID = 0"; } else { $provice = implode(",", search_province_id($data)); $query = "SELECT addresslist.ADDRESSLIST_OWNER_ID FROM addresslist " . "LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = addresslist.ADDRESSLIST_OWNER_ID " . "LEFT JOIN address ON addresslist.ADDRESSLIST_ADDRESS_ID = address.ID " . "WHERE address.ADDRESS_PROVINCE_ID IN ({$provice}) ORDER BY PERSONNAME_NAME "; } } else { if ($type == 4) { if (strlen($data) == 10) { $arer_code = substr($data, 0, 3); $string = substr($data, 3, 7); // echo $arer_code; // echo $string; $query = "SELECT CONTACT_OWNER_ID FROM contact " . "LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_ARER_CODE LIKE '%{$arer_code}%' AND CONTACT_STRING LIKE '%{$string}%' AND CONTACT_TYPE_ID IN (1,2) " . "ORDER BY PERSONNAME_NAME "; } else { if (strlen($data) == 9) { $check_code = substr($data, 0, 2); if ($check_code == "02") { $arer_code = substr($data, 0, 2); $string = substr($data, 3, 7); } else { $arer_code = substr($data, 0, 3); $string = substr($data, 3, 6); } // echo $arer_code; // echo "<br/>"; // echo $string; $query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_ARER_CODE LIKE '%{$arer_code}%' AND CONTACT_STRING LIKE '%{$string}%' AND CONTACT_TYPE_ID IN (1,2) ORDER BY PERSONNAME_NAME "; } else { if ($data[0] == 0 && strlen($data) < 4) { $field = "CONTACT_ARER_CODE"; } else { $field = "CONTACT_STRING"; } // echo $field; $query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE {$field} LIKE '%{$data}%' AND CONTACT_TYPE_ID IN (1,2) ORDER BY PERSONNAME_NAME "; } } } else { if ($type == 5) { $query = "SELECT ID FROM person LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID " . "WHERE PERSONALID LIKE '%{$data}%' ORDER BY PERSONNAME_NAME "; } else { if ($type == 6) { $query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_STRING LIKE '%{$data}%' AND CONTACT_TYPE_ID = 4 ORDER BY PERSONNAME_NAME "; } else { if ($type == 7) { $query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_STRING LIKE '%{$data}%' AND CONTACT_TYPE_ID = 5 ORDER BY PERSONNAME_NAME "; } } } } } } } // echo $query; return $query; // $query .= "LIMIT $start, $end"; // echo $query; // return mysql_query($query); }