/* Copyright (c) 2009 Arnaud Renevier, Inc, published under the modified BSD
license. */
-require ("./inc/db/anydb.php");
+require_once ("./inc/db/anydb.php");
class mysqlConnection implements anydbConnection {
var $link = null;
public function users_table_exists () {
return $this->_tblexists ("users");
}
- public function create_users_table ($error_if_exists) {
+ public function create_users_table () {
$query = sprintf ("CREATE TABLE " .
- ($error_if_exists ? " " : "IF NOT EXISTS ") .
"%susers (
name VARCHAR(255) NOT NULL, pwd CHAR(32),
PRIMARY KEY (name));", $this->dbprefix);
public function items_table_exists () {
return $this->_tblexists ("items");
}
- public function create_items_table ($error_if_exists) {
+ public function create_items_table () {
$query = sprintf ("CREATE TABLE " .
- ($error_if_exists ? " " : "IF NOT EXISTS ") .
"%sitems (
- imgurl VARCHAR(255),
+ id MEDIUMINT NOT NULL AUTO_INCREMENT,
+ location POINT,
title VARCHAR(127),
description TEXT,
- location POINT,
+ imgpath VARCHAR(255),
date DATETIME,
- PRIMARY KEY (imgurl)
+ user VARCHAR(255),
+ PRIMARY KEY (id)
);", $this->dbprefix);
$this->_execute_query ($query);
}
- public function setpwd ($user_name, $pwd) {
+ public function setpwd ($user_name, $pwd, $create_if_not_exists) {
$usrname_escaped = mysql_real_escape_string ($user_name);
$query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s';",
$this->dbprefix, $usrname_escaped);
$res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
if ($res [0] == 1) {
- $query = sprintf ("UPDATE %susers SET pwd='%s' WHERE name like '%s';",
+ if ($create_if_not_exists) {
+ $query = sprintf ("UPDATE %susers SET pwd='%s' WHERE name like '%s';",
$this->dbprefix, md5 ($pwd), $usrname_escaped);
+ } else {
+ throw new Exception (anydbConnection::err_query);
+ }
} else {
$query = sprintf ("INSERT INTO %susers VALUES ('%s', '%s');",
$this->dbprefix, $usrname_escaped, md5 ($pwd));
}
public function save_feature ($feature) {
- $query = sprintf ("SELECT imgurl FROM %sitems WHERE imgurl = '%s'",
- $this->dbprefix,
- mysql_real_escape_string ($feature->imgurl));
- $this->_execute_query ($query);
- if (mysql_affected_rows ($this->link) == 0) {
- if ($feature->imgurl_exists ()) {
+ try {
+ $id = $feature->id;
+ } catch (Exception $e) {}
+ if (isset ($id)) {
+ $query = sprintf ("UPDATE %sitems SET
+ imgpath='%s',
+ title='%s',
+ description='%s',
+ location=GeomFromText('POINT(%s %s)')
+ WHERE id = '%s';",
+ $this->dbprefix,
+ mysql_real_escape_string ($feature->imgpath),
+ mysql_real_escape_string ($feature->title),
+ mysql_real_escape_string ($feature->description),
+ $feature->lon,
+ $feature->lat,
+ $id);
+ $this->_execute_query ($query);
+ return $feature;
+ } else {
$query = sprintf ("INSERT INTO %sitems
- (imgurl, title, description, location, date)
- VALUES ('%s', '%s', '%s',
- GeomFromText('POINT(%s %s)'), NOW())",
+ (imgpath, title, description, location, date, user)
+ VALUES ('%s', '%s', '%s',
+ GeomFromText('POINT(%s %s)'), NOW(), '%s')",
$this->dbprefix,
- mysql_real_escape_string ($feature->imgurl),
+ mysql_real_escape_string ($feature->imgpath),
mysql_real_escape_string ($feature->title),
mysql_real_escape_string ($feature->description),
$feature->lon,
- $feature->lat
+ $feature->lat,
+ mysql_real_escape_string ($feature->user)
);
+
$this->_execute_query ($query);
- return true;
- } else {
- return false;
- }
- } else {
- $query = sprintf ("UPDATE %sitems SET title='%s', description='%s',
- location=GeomFromText('POINT(%s %s)')
- where imgurl = '%s'",
- $this->dbprefix,
- mysql_real_escape_string ($feature->title),
- mysql_real_escape_string ($feature->description),
- $feature->lon,
- $feature->lat,
- mysql_real_escape_string ($feature->imgurl)
- );
- $this->_execute_query ($query);
- return true;
+ $id = mysql_insert_id ();
+ return new feature ($id, $feature->lon, $feature->lat,
+ $feature->imgpath, $feature->title,
+ $feature->description, $feature->date,
+ $feature->user);
}
}
public function delete_feature ($feature) {
- $query = sprintf ("DELETE from %sitems WHERE imgurl like '%s'",
+ $query = sprintf ("DELETE from %sitems WHERE id = '%s'",
$this->dbprefix,
- mysql_real_escape_string ($feature->imgurl));
+ mysql_real_escape_string ($feature->id));
$this->_execute_query ($query);
return true;
}
- public function getfeature ($imgurl) {
- $query = sprintf ("SELECT imgurl, title, description, AsText(location)
- AS location FROM %sitems WHERE imgurl like '%s';",
- $this->dbprefix, mysql_real_escape_string ($imgurl));
+ public function getfeature ($id) {
+ $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
+ AS location, UNIX_TIMESTAMP(date) AS date, user
+ FROM %sitems WHERE id = '%s';",
+ $this->dbprefix, mysql_real_escape_string ($id));
$row = mysql_fetch_assoc ($this->_execute_query ($query));
- if (!isset ($row)) {
+ if ($row === false) {
return null;
}
return $this->_feature_frow_row ($row);
}
- public function listfeatures () {
- $query = sprintf ("SELECT imgurl, title, description, AsText(location)
- AS location FROM %sitems;",
- $this->dbprefix);
+ public function listfeatures ($user) {
+ if ($user && ($user != "admin")) {
+ $from_user_query = sprintf (" WHERE user = '%s' ",
+ mysql_real_escape_string ($user));
+ } else {
+ $from_user_query = "";
+ }
+
+ $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
+ AS location, UNIX_TIMESTAMP(date) AS date, user
+ FROM %sitems %s;",
+ $this->dbprefix, $from_user_query);
$features = array ();
$res = $this->_execute_query ($query);
return $features;
}
- public function imgurl_exists ($imgurl) {
- $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgurl LIKE '%s';",
- $this->dbprefix, mysql_real_escape_string ($imgurl));
- $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
- return ($res [0] >= 1) ? true : false;
- }
-
- public function mbr () {
- $maxlon = -180;
- $minlon = 180;
- $maxlat = -88;
- $minlat = 88;
-
- if (!$this->link) {
- return array ($minlon, $minlat, $maxlon, $maxlat);
- }
- try {
- $features = $this->listfeatures ();
- } catch (Exception $e) {
- return array ($minlon, $minlat, $maxlon, $maxlat);
+ public function mostrecentfeatures ($num_features) {
+ $query = sprintf ("SELECT id, imgpath, title, description,
+ AsText(location) AS location, UNIX_TIMESTAMP(date)
+ AS date, user FROM %sitems ORDER BY date DESC",
+ $this->dbprefix);
+ if ($num_features) {
+ $query .= sprintf (" LIMIT %d", $num_features);
}
-
- if (count ($features) == 0) {
- return array ($minlon, $minlat, $maxlon, $maxlat);
- } else if (count ($features) == 1) {
- // in case there's only one feature, we show an area of at least
- // 4 x 4 degrees
- $feature = $features [0];
-
- $minlon = max ($feature->lon - 2, -180);
- $maxlon = min ($feature->lon + 2, 180);
- $minlat = max ($feature->lat - 2, -90);
- $maxlat = min ($feature->lat + 2, 90);
-
- return array ($minlon, $minlat, $maxlon, $maxlat);
- } else {
- foreach ($features as $feature) {
- $minlon = min ($feature->lon, $minlon);
- $minlat = min ($feature->lat, $minlat);
- $maxlon = max ($feature->lon, $maxlon);
- $maxlat = max ($feature->lat, $maxlat);
+ $features = array ();
+ $res = $this->_execute_query ($query);
+ while ($row = mysql_fetch_assoc ($res)) {
+ $feature = $this->_feature_frow_row ($row);
+ if (isset ($feature)) {
+ $features[] = $feature;
}
-
- return array ($minlon, $minlat, $maxlon, $maxlat);
}
+ return $features;
+ }
+
+ public function imgpath_exists ($imgpath) {
+ $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgpath LIKE '%s';",
+ $this->dbprefix, mysql_real_escape_string ($imgpath));
+ $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
+ return ($res [0] >= 1) ? true : false;
}
public function getdbname () {
$lon = $matches [1];
$lat = $matches [2];
try {
- $feature = new feature ($row ["imgurl"], $row ["title"], $row
- ["description"], $lon, $lat);
+ $feature = new feature ($row ["id"], $lon, $lat, $row ["imgpath"],
+ $row ["title"], $row ["description"],
+ $row ["date"], $row ["user"]);
} catch (Exception $e) {
return null;
}
projects / syp.git / blobdiff