db->escape($slug) . "' group by postcode order by postcode asc"; // // echo $sql; // $query = $this->db->query($sql); // return $query->rows; // } public function getInfoPostcode($postcode, $limit=0) { if ($limit){ $ssql = "limit ".$limit; } else { $ssql =''; } $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "post_range_ca where postcode='" . strtoupper($postcode) . "' ".$ssql); if ($limit>1 ){ return $query->rows; } else { return $query->row; } } public function getCityByPostcode($postcode) { $query = $this->db->query("SELECT c.city,c.province, c.slug, p.census FROM " . DB_PREFIX . "post_range_ca p left join city_post_range_ca c on p.city_post_range_id = c.id WHERE p.postcode='" . strtoupper($postcode) . "'"); return $query->rows; } public function getPostCodesByCity($city_id) { $sql = "SELECT postcode FROM post_range_ca WHERE city_post_range_id ='" . $city_id . "' group by postcode order by postcode asc"; $query = $this->db->query($sql); return $query->rows; } public function getPostCodesByCityName($name){ $query = $this->db->query("SELECT postcode FROM post_range_ca WHERE city ='" . $name . "' group by postcode order by postcode asc"); return $query->rows; } public function getPostCodesByCityNameToby($name, $province){ $query = $this->db->query("SELECT postcode FROM post_range_ca WHERE city ='" . $this->db->escape($name) . "' and province='".$province."' group by postcode order by postcode asc"); return $query->rows; } public function getTotalPostCodes() { $sql = "SELECT count(*) as total FROM post_range_ca"; $query = $this->db->query($sql); return $query->row['total']; } public function getPostCodesBatch($minid, $maxid) { $sql = "SELECT province, postcode FROM post_range_ca where id > ".(int)$minid ." and id <= ".(int)$maxid." order by id asc"; $query = $this->db->query($sql); return $query->rows; } // public function getPostCodesByRange($postcode) { // $sql = "SELECT postcode FROM post_range_ca WHERE postcode like '" . trim($this->db->escape($postcode)) . "%' group by postcode order by postcode asc"; // // echo $sql; // $query = $this->db->query($sql); // $pc = array(); // foreach ($query->rows as $row) { // $pc[] = $row['postcode']; // } // return $pc; // } public function getPostCodes($postcode) { // If Postcode is a range (3 first cchars) pulls all distinct postcodes from address table, else pulls all postcodes range from postcode table. if (strlen($postcode) == 6){ $sql = "SELECT * FROM " . DB_PREFIX . "city_postcode_ca WHERE postcode = '" . $this->db->escape($postcode) . "' group by name order by postcode asc"; $query = $this->db->query($sql); if (!count($query)){ $postcode = substr_replace($postcode, "0", -1); $sql = "SELECT * FROM " . DB_PREFIX . "city_postcode_ca WHERE postcode LIKE '" . $this->db->escape($postcode) . "' group by name order by postcode asc"; $query = $this->db->query($sql); if (!count($query)){ $postcode = substr_replace($postcode, "%", -1); $sql = "SELECT * FROM " . DB_PREFIX . "city_postcode_ca WHERE postcode LIKE '" . $this->db->escape($postcode) . "' group by name order by postcode asc"; $query = $this->db->query($sql); if (!count($query)){ $sql = "SELECT * FROM " . DB_PREFIX . "address_dbx WHERE postcode like '" . $this->db->escape($postcode) . "' group by city order by postcode asc"; $query = $this->db->query($sql); } } } } else if (strlen($postcode) == 3){ $postcode = $postcode."%"; $sql = "SELECT * FROM " . DB_PREFIX . "post_range_ca WHERE postcode like '" . $this->db->escape($postcode) . "' group by postcode order by postcode asc"; $query = $this->db->query($sql); } return $query->rows; } }