/* 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 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(), 'admin')",
$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
);
+
$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);
}
}
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 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;",
+ $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
+ AS location, UNIX_TIMESTAMP(date) AS date FROM %sitems;",
$this->dbprefix);
$features = array ();
return $features;
}
- public function imgurl_exists ($imgurl) {
- $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgurl LIKE '%s';",
- $this->dbprefix, mysql_real_escape_string ($imgurl));
+ public function mostrecentfeatures ($num_features) {
+ $query = sprintf ("SELECT id, imgpath, title, description,
+ AsText(location) AS location, UNIX_TIMESTAMP(date)
+ AS date FROM %sitems ORDER BY date DESC",
+ $this->dbprefix);
+ if ($num_features) {
+ $query .= sprintf (" LIMIT %d", $num_features);
+ }
+ $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 $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;
}
$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"]);
} catch (Exception $e) {
return null;
}