function db_updated($value)
{
$version = db_get_installed_version();
if ($value) {
return ', updated=' . db_sql_encode($value);
} else {
if ($version === 'mysql-5.7') {
return '';
} else {
return ', updated=null';
}
}
}
public static function findByReferenceId($tripId = '', $referenceId = '')
{
if (!$tripId || !$referenceId) {
return null;
}
$tripIdValue = db_sql_encode($tripId);
$referenceIdValue = db_sql_encode($referenceId);
$query = "" . "SELECT t2.commentId " . "FROM blogComment " . "INNER JOIN (" . "SELECT " . "MAX(t1.updated) AS updated, " . "t1.tripId as tripId, " . "t1.commentId as commentId " . "FROM blogComment " . "AS t1 " . "GROUP BY t1.tripId, t1.commentId " . "HAVING t1.tripId = {$tripIdValue} " . ") AS t2 " . "WHERE blogComment.tripId = t2.tripId " . "AND blogComment.commentId = t2.commentId " . "AND blogComment.updated = t2.updated " . "AND blogComment.deleted != 'Y' " . "AND blogComment.referenceId = {$referenceIdValue} " . "ORDER BY blogComment.created ASC ";
// print $query . "\n";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return null;
}
if (mysql_num_rows($result) <= 0) {
// Comment does not exist
return null;
}
$list = array();
$count = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$list[$count++] = db_sql_decode($line['commentId']);
}
return $list;
}
public static function getList($tripId, $referenceId)
{
$tripId = db_sql_encode($tripId);
$referenceId = db_sql_encode($referenceId);
$query = "" . "SELECT " . "blogFeedback.tripId, " . "blogFeedback.referenceId, " . "blogFeedback.userId, " . "blogFeedback.type " . "FROM blogFeedback " . "INNER JOIN (" . "SELECT " . "MAX(t1.updated) AS updated, " . "t1.tripId AS tripId, " . "t1.referenceId AS referenceId, " . "t1.userId AS userId " . "FROM blogFeedback " . "AS t1 " . "GROUP BY " . "t1.tripId, " . "t1.referenceId, " . "t1.userId " . "HAVING " . "t1.tripId={$tripId} " . "AND t1.referenceId={$referenceId} " . ") AS t2 " . "WHERE blogFeedback.tripId = t2.tripId " . "AND blogFeedback.referenceId = t2.referenceId " . "AND blogFeedback.userId = t2.userId " . "AND blogFeedback.updated = t2.updated " . "AND blogFeedback.deleted != 'Y' " . "ORDER BY blogFeedback.userId";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return false;
}
$list = array();
if (mysql_num_rows($result) > 0) {
$count = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$tripId = db_sql_decode($line["tripId"]);
$referenceId = db_sql_decode($line['referenceId']);
$userId = db_sql_decode($line['userId']);
$userName = '';
$type = db_sql_decode($line['type']);
$user = new User($userId);
if ($user) {
$userName = $user->getName();
}
$list[$count++] = array('tripId' => $tripId, 'referenceId' => $referenceId, 'userId' => $userId, 'userName' => $userName, 'type' => $type);
}
}
return $list;
}
/**
* Create an instance by the hash value. This function is in support
* of the synchronization functionality. It takes a hash value and
* it will return an instance of the object for that hash value
* if the hash exists. If the hash does not exist, null will be
* returned, indicating that the object should be created using the
* normal constructor.
*/
public static function findByHash($hash = '')
{
if (!isset($hash) || $hash === '') {
return null;
}
$hashValue = db_sql_encode($hash);
$query = "SELECT * FROM blogMedia " . "WHERE hash={$hashValue} " . "ORDER BY updated DESC " . "LIMIT 1";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return null;
}
if (mysql_num_rows($result) <= 0) {
// Object does not exist
return null;
}
// Create an instance with a special ID '-' to bypass the
// checks on empty ID. The ID value will be overwritten by the
// value coming back from the database anyway.
$object = new Media('-', '-');
if ($object->loadFromResult($result)) {
return $object;
}
return null;
}
public function delete()
{
if (isset($this->authId) && $this->authId !== '') {
$authIdValue = db_sql_encode($this->authId);
$query = "DELETE FROM blogAuth " . "WHERE authId={$authIdValue}";
mysql_query($query);
}
}
public function getNextJournal()
{
$tripId = db_sql_encode($this->tripId);
$journalId = db_sql_encode($this->journalId);
$created = db_sql_encode($this->created);
$query = "" . "SELECT * " . "FROM blogJournal " . "INNER JOIN (" . "SELECT " . "MAX(t1.updated) AS updated, " . "t1.tripId as tripId, " . "t1.journalId as journalId " . "FROM blogJournal " . "AS t1 " . "GROUP BY t1.tripId, t1.journalId " . "HAVING t1.tripId = {$tripId} " . ") AS t2 " . "WHERE blogJournal.tripId = t2.tripId " . "AND blogJournal.journalId = t2.journalId " . "AND blogJournal.updated = t2.updated " . "AND blogJournal.deleted != 'Y' " . "AND ( " . "( " . "blogJournal.journalId = {$journalId} " . "AND blogJournal.created > {$created} " . ") " . "OR " . "( " . "blogJournal.journalId > {$journalId} " . ") " . ") " . "ORDER BY blogJournal.journalDate ASC, " . "blogJournal.created ASC " . "LIMIT 1";
// print $query . "\n";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return null;
}
if (mysql_num_rows($result) <= 0) {
// Object does not exist
return null;
}
// Create an instance with a special ID '-' to bypass the
// checks on empty ID. The ID value will be overwritten by the
// value coming back from the database anyway.
$object = new Journal('-', '-');
if ($object->loadFromResult($result)) {
return $object;
}
return null;
}
public function setAttribute($name, $value)
{
if ($this->tripId === "" || $name === "") {
// invalid request
return;
}
// load old values, if any
$created = null;
$updated = null;
$hash = "";
$query = "SELECT * FROM blogTripAttribute " . "WHERE tripId=" . db_sql_encode($this->tripId) . "AND name=" . db_sql_encode($name) . "ORDER BY updated DESC " . "LIMIT 1";
$result = mysql_query($query);
if (!$result) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return;
}
if (mysql_num_rows($result) > 0) {
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$created = db_sql_decode($line["created"]);
if ($created === "") {
$created = null;
}
}
$query = "INSERT INTO blogTripAttribute SET " . "tripId=" . db_sql_encode($this->tripId) . ", name=" . db_sql_encode($name) . ", created=" . db_sql_encode($created) . ", updated=null" . ", value=" . db_sql_encode($value);
if (mysql_query($query)) {
// Saved successfully, now load fresh, including updated values,
// and update the hash value
$query = "SELECT * FROM blogTripAttribute " . "WHERE tripId=" . db_sql_encode($this->tripId) . "AND name=" . db_sql_encode($name) . "ORDER BY updated DESC " . "LIMIT 1";
$result = mysql_query($query);
if ($result && mysql_num_rows($result) > 0) {
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$created = db_sql_decode($line["created"]);
$updated = db_sql_decode($line["updated"]);
$hash = "|" . $this->tripId . "|" . $created . "|" . $updated . "|" . $name . "|" . $value . "|";
$hash = md5($hash);
$query = "UPDATE blogTripAttribute SET " . "hash=" . db_sql_encode($hash) . " WHERE tripId=" . db_sql_encode($this->tripId) . " AND name=" . db_sql_encode($name) . " AND updated=" . db_sql_encode($updated);
mysql_query($query);
} else {
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
}
}
return;
}
public static function set($name = '', $value = '')
{
if (!isset($name) || $name === '') {
return false;
}
if (!isset($value)) {
return false;
}
$query = "INSERT INTO blogSetting SET " . "name=" . db_sql_encode($name) . ", value=" . db_sql_encode($value) . db_updated(null) . " ON DUPLICATE KEY UPDATE " . "value=" . db_sql_encode($value) . db_updated(null);
if (!mysql_query($query)) {
// Error executing the query
print $query . "<br/>";
print " --> error: " . mysql_error() . "<br/>\n";
return false;
}
return true;
}
public function testDbSqlEncode()
{
for ($i = 0; $i < count($this->values); $i++) {
$this->assertEquals($this->values[$i][1], db_sql_encode($this->values[$i][0]));
}
}
