2 /* Copyright (c) 2009 Arnaud Renevier, Inc, published under the modified BSD
5 require_once ("./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 () {
32 $query = sprintf ("CREATE TABLE " .
34 name VARCHAR(255) NOT NULL, pwd CHAR(32),
35 PRIMARY KEY (name));", $this->dbprefix);
36 $this->_execute_query ($query);
39 public function items_table_exists () {
40 return $this->_tblexists ("items");
42 public function create_items_table () {
43 $query = sprintf ("CREATE TABLE " .
45 id MEDIUMINT NOT NULL AUTO_INCREMENT,
53 );", $this->dbprefix);
54 $this->_execute_query ($query);
57 public function user_exists ($user_name) {
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);
62 return ($res [0] == 1);
65 public function setpwd ($user_name, $pwd) {
66 if (strlen ($pwd) == 0) {
67 throw new Exception (anydbConnection::err_query);
69 $usrname_escaped = mysql_real_escape_string ($user_name);
70 if ($this->user_exists ($user_name)) {
71 $query = sprintf ("UPDATE %susers SET pwd='%s' WHERE name like '%s';",
72 $this->dbprefix, md5 ($pwd), $usrname_escaped);
74 $query = sprintf ("INSERT INTO %susers VALUES ('%s', '%s');",
75 $this->dbprefix, $usrname_escaped, md5 ($pwd));
77 $this->_execute_query ($query);
80 public function checkpwdmd5 ($user_name, $pwd_md5) {
81 $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s'
84 mysql_real_escape_string ($user_name),
85 mysql_real_escape_string ($pwd_md5));
86 $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
94 public function save_feature ($feature) {
97 } catch (Exception $e) {}
99 $query = sprintf ("UPDATE %sitems SET
103 location=GeomFromText('POINT(%s %s)')
106 mysql_real_escape_string ($feature->imgpath),
107 mysql_real_escape_string ($feature->title),
108 mysql_real_escape_string ($feature->description),
112 $this->_execute_query ($query);
115 $query = sprintf ("INSERT INTO %sitems
116 (imgpath, title, description, location, date, user)
117 VALUES ('%s', '%s', '%s',
118 GeomFromText('POINT(%s %s)'), NOW(), '%s')",
120 mysql_real_escape_string ($feature->imgpath),
121 mysql_real_escape_string ($feature->title),
122 mysql_real_escape_string ($feature->description),
125 mysql_real_escape_string ($feature->user)
128 $this->_execute_query ($query);
129 $id = mysql_insert_id ();
130 return new feature ($id, $feature->lon, $feature->lat,
131 $feature->imgpath, $feature->title,
132 $feature->description, $feature->date,
137 public function delete_feature ($feature) {
138 $query = sprintf ("DELETE from %sitems WHERE id = '%s'",
140 mysql_real_escape_string ($feature->id));
141 $this->_execute_query ($query);
145 public function getfeature ($id) {
146 $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
147 AS location, UNIX_TIMESTAMP(date) AS date, user
148 FROM %sitems WHERE id = '%s';",
149 $this->dbprefix, mysql_real_escape_string ($id));
150 $row = mysql_fetch_assoc ($this->_execute_query ($query));
151 if ($row === false) {
154 return $this->_feature_frow_row ($row);
157 public function listfeatures ($user) {
158 if ($user && ($user != "admin")) {
159 $from_user_query = sprintf (" WHERE user = '%s' ",
160 mysql_real_escape_string ($user));
162 $from_user_query = "";
165 $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
166 AS location, UNIX_TIMESTAMP(date) AS date, user
168 $this->dbprefix, $from_user_query);
170 $features = array ();
171 $res = $this->_execute_query ($query);
172 while ($row = mysql_fetch_assoc ($res)) {
173 $feature = $this->_feature_frow_row ($row);
174 if (isset ($feature)) {
175 $features[] = $feature;
181 public function mostrecentfeatures ($num_features) {
182 $query = sprintf ("SELECT id, imgpath, title, description,
183 AsText(location) AS location, UNIX_TIMESTAMP(date)
184 AS date, user FROM %sitems ORDER BY date DESC",
187 $query .= sprintf (" LIMIT %d", $num_features);
189 $features = array ();
190 $res = $this->_execute_query ($query);
191 while ($row = mysql_fetch_assoc ($res)) {
192 $feature = $this->_feature_frow_row ($row);
193 if (isset ($feature)) {
194 $features[] = $feature;
200 public function imgpath_exists ($imgpath) {
201 $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgpath LIKE '%s';",
202 $this->dbprefix, mysql_real_escape_string ($imgpath));
203 $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
204 return ($res [0] >= 1) ? true : false;
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 ["id"], $lon, $lat, $row ["imgpath"],
227 $row ["title"], $row ["description"],
228 $row ["date"], $row ["user"]);
229 } catch (Exception $e) {
235 private function _execute_query ($query) {
236 if (!function_exists ("mysql_query")) {
237 throw new Exception (anydbConnection::err_driver_unavailable);
240 throw new Exception (anydbConnection::err_query);
242 $res = mysql_query ($query, $this->link);
244 throw new Exception (anydbConnection::err_query);
250 $connection = new mysqlConnection();