2 /* Copyright (c) 2009 Arnaud Renevier, Inc, published under the modified BSD
5 require ("./inc/db/anydb.php");
7 class mysqlConnection implements anydbConnection {
11 public function connect ($host, $user, $pwd, $dbname, $dbprefix) {
12 if (!function_exists ("mysql_connect")) {
13 throw new Exception (anydbConnection::err_driver_unavailable);
15 if ($this->link) { // connection has already been opened
18 $this->link = @mysql_connect ($host,$user,$pwd,true);
20 throw new Exception (anydbConnection::err_connection);
22 if (!mysql_select_db ($dbname, $this->link)) {
23 throw new Exception (anydbConnection::err_unknown_database);
25 $this->dbprefix = $dbprefix;
28 public function users_table_exists () {
29 return $this->_tblexists ("users");
31 public function create_users_table ($error_if_exists) {
32 $query = sprintf ("CREATE TABLE " .
33 ($error_if_exists ? " " : "IF NOT EXISTS ") .
35 name VARCHAR(255) NOT NULL, pwd CHAR(32),
36 PRIMARY KEY (name));", $this->dbprefix);
37 $this->_execute_query ($query);
40 public function items_table_exists () {
41 return $this->_tblexists ("items");
43 public function create_items_table ($error_if_exists) {
44 $query = sprintf ("CREATE TABLE " .
45 ($error_if_exists ? " " : "IF NOT EXISTS ") .
53 );", $this->dbprefix);
54 $this->_execute_query ($query);
57 public function setpwd ($user_name, $pwd) {
58 $usrname_escaped = mysql_real_escape_string ($user_name);
59 $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s';",
60 $this->dbprefix, $usrname_escaped);
61 $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
63 $query = sprintf ("UPDATE %susers SET pwd='%s' WHERE name like '%s';",
64 $this->dbprefix, md5 ($pwd), $usrname_escaped);
66 $query = sprintf ("INSERT INTO %susers VALUES ('%s', '%s');",
67 $this->dbprefix, $usrname_escaped, md5 ($pwd));
69 $this->_execute_query ($query);
72 public function checkpwdmd5 ($user_name, $pwd_md5) {
73 $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s'
76 mysql_real_escape_string ($user_name),
77 mysql_real_escape_string ($pwd_md5));
78 $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
86 public function save_feature ($feature) {
87 $query = sprintf ("SELECT imgurl FROM %sitems WHERE imgurl = '%s'",
89 mysql_real_escape_string ($feature->imgurl));
90 $this->_execute_query ($query);
91 if (mysql_affected_rows ($this->link) == 0) {
92 if ($feature->imgurl_exists ()) {
93 $query = sprintf ("INSERT INTO %sitems
94 (imgurl, title, description, location, date)
95 VALUES ('%s', '%s', '%s',
96 GeomFromText('POINT(%s %s)'), NOW())",
98 mysql_real_escape_string ($feature->imgurl),
99 mysql_real_escape_string ($feature->title),
100 mysql_real_escape_string ($feature->description),
104 $this->_execute_query ($query);
110 $query = sprintf ("UPDATE %sitems SET title='%s', description='%s',
111 location=GeomFromText('POINT(%s %s)')
112 where imgurl = '%s'",
114 mysql_real_escape_string ($feature->title),
115 mysql_real_escape_string ($feature->description),
118 mysql_real_escape_string ($feature->imgurl)
120 $this->_execute_query ($query);
125 public function delete_feature ($feature) {
126 $query = sprintf ("DELETE from %sitems WHERE imgurl like '%s'",
128 mysql_real_escape_string ($feature->imgurl));
129 $this->_execute_query ($query);
133 public function getfeature ($imgurl) {
134 $query = sprintf ("SELECT imgurl, title, description, AsText(location)
135 AS location FROM %sitems WHERE imgurl like '%s';",
136 $this->dbprefix, mysql_real_escape_string ($imgurl));
137 $row = mysql_fetch_assoc ($this->_execute_query ($query));
141 return $this->_feature_frow_row ($row);
144 public function listfeatures () {
145 $query = sprintf ("SELECT imgurl, title, description, AsText(location)
146 AS location FROM %sitems;",
149 $features = array ();
150 $res = $this->_execute_query ($query);
151 while ($row = mysql_fetch_assoc ($res)) {
152 $feature = $this->_feature_frow_row ($row);
153 if (isset ($feature)) {
154 $features[] = $feature;
160 public function imgurl_exists ($imgurl) {
161 $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgurl LIKE '%s';",
162 $this->dbprefix, mysql_real_escape_string ($imgurl));
163 $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
164 return ($res [0] >= 1) ? true : false;
167 public function mbr () {
174 return array ($minlon, $minlat, $maxlon, $maxlat);
177 $features = $this->listfeatures ();
178 } catch (Exception $e) {
179 return array ($minlon, $minlat, $maxlon, $maxlat);
182 if (count ($features) == 0) {
183 return array ($minlon, $minlat, $maxlon, $maxlat);
184 } else if (count ($features) == 1) {
185 // in case there's only one feature, we show an area of at least
187 $feature = $features [0];
189 $minlon = max ($feature->lon - 2, -180);
190 $maxlon = min ($feature->lon + 2, 180);
191 $minlat = max ($feature->lat - 2, -90);
192 $maxlat = min ($feature->lat + 2, 90);
194 return array ($minlon, $minlat, $maxlon, $maxlat);
196 foreach ($features as $feature) {
197 $minlon = min ($feature->lon, $minlon);
198 $minlat = min ($feature->lat, $minlat);
199 $maxlon = max ($feature->lon, $maxlon);
200 $maxlat = max ($feature->lat, $maxlat);
203 return array ($minlon, $minlat, $maxlon, $maxlat);
207 public function getdbname () {
211 private function _tblexists ($tblname) {
212 $query = sprintf ("SHOW TABLES LIKE '%s%s';",
213 $this->dbprefix, $tblname);
214 return mysql_num_rows ($this->_execute_query ($query)) == 1;
217 private function _feature_frow_row ($row) {
218 // XXX: should I remove invalid features from database ?
219 if (!preg_match ('/^POINT\(([0-9\-\.]+)\s+([0-9\-\.]+)\)$/',
220 $row ["location"], $matches)) {
226 $feature = new feature ($row ["imgurl"], $row ["title"], $row
227 ["description"], $lon, $lat);
228 } catch (Exception $e) {
234 private function _execute_query ($query) {
235 if (!function_exists ("mysql_query")) {
236 throw new Exception (anydbConnection::err_driver_unavailable);
239 throw new Exception (anydbConnection::err_query);
241 $res = mysql_query ($query, $this->link);
243 throw new Exception (anydbConnection::err_query);
249 $connection = new mysqlConnection();