} // pull latest photo from db and it's created time $sql = "SELECT `date_added` FROM `uploads` ORDER BY `id` DESC LIMIT 1;"; $sqli_result = $sqli->query($sql); $latest_date_str = $sqli_result->fetchArray(SQLITE3_ASSOC); $latest_date = strtotime($latest_date_str["date_added"]); if (!$sqli_result) { die('ispy insert not completed: ' . $sqli->lastErrorMsg); } foreach ($results['data'] as $result) { // break if you find a photo created later than the latest if (isset($latest_date) && (int) $result["created_time"] <= $latest_date) { break; } // escape properties in _POST into variables $name = $sqli->escapeString($result["user"]["username"]); $date = $sqli->escapeString($result["created_time"]); if ($result["location"]["name"]) { $location = $sqli->escapeString($result["location"]["name"]); } else { // find address from google maps? $latitude = $sqli->escapeString($result["location"]["latitude"]); $longitude = $sqli->escapeString($result["location"]["longitude"]); $location = '(location not given)'; } $comments = $sqli->escapeString($result["caption"]["text"]); $file_https = preg_replace('/http:.*[^\\s]/', 'https://', $result["images"]["standard_resolution"]["url"]); $file_name = $sqli->escapeString($file_https); $date = date('m\\/d\\/y', $date); !isset($date) ? $date = date('m\\/d\\/y', NOW()) : ''; $sql = "INSERT INTO uploads (`name`, `date`, `location`, `photo`, `email`, `comments`, `date_added`)\n VALUES ('{$name}', '{$date}', '{$location}', '{$file_name}', 'NA', '{$comments}', NOW());";
<?php require_once __DIR__ . '/ispy_config.php'; $ispy_config = new IspyConfig(); $sqli = new Sqlite3($ispy_config->db); if (!$sqli) { die('Could not connect: ' . $sqli->lastErrorMsg); } // grab limit and offset $limit = $sqli->escapeString($_GET['limit']); $offset = $sqli->escapeString($_GET['offset']); $count = $sqli->escapeString($_GET['count']); // grab bottom three entries from table $sql = "SELECT * FROM `uploads` ORDER BY `id` DESC LIMIT {$limit} OFFSET {$offset};"; $result = $sqli->query($sql); if (!$result) { die('ispy insert not completed: ' . $sqli->error); } $i = 0; // loop through result entries do we need this??? // or can we just return the data jsonencoded while ($row = $result->fetchArray(SQLITE3_ASSOC)) { foreach ($row as $key => $value) { $selection[$key] = $value; } // push $selection assoc array to array of assoc arrays $result_array[$i] = $selection; $i++; } // json encode data for ajax parser $data = json_encode($result_array);
/** * When was queue last accessed? * @return int timestamp */ function getLastAccess() { $lastAccess = (int) $this->getQueuesModel()->getConnection()->querySingle("\n SELECT created\n\t\t\tFROM files\n\t\t\tWHERE queueID = '" . Sqlite3::escapeString($this->getQueueID()) . "'\n\t\t\tORDER BY created DESC\n "); return $lastAccess; }
public function deleteRow($table, $data) { if (empty($data)) { return true; } $this->checkTable($table); $first = current($data); if (is_array($first)) { $cols = array_keys($first); } else { $query = "PRAGMA table_info({$table})"; $res = self::$sqliteRead->query($query); $cols = array(); while ($row = $res->fetchArray()) { if ($row['name'] == 'id') { continue; } $cols[] = $row['name']; } } foreach ($data as $col => $row) { if (is_array($row)) { $d = array_values($row); foreach ($d as &$e) { $e = \Sqlite3::escapeString($e); } unset($e); } else { $d = array($row); } $query = 'DELETE FROM ' . $table . ' WHERE ' . $col . " IN ('" . implode("', '", $d) . "')"; self::$sqliteWrite->querySingle($query); } return true; }
include 'ispy_upload_photo_fb.php'; require_once __DIR__ . '/ispy_config.php'; $ispy_config = new IspyConfig(); /** * ispy_db_call.php, to record form entries to the db * */ if (empty($photo["id"])) { die('Upload failed: no photo detected.'); } $sqli = new Sqlite3($ispy_config->db); if (!$sqli) { die('Could not connect: ' . $sqli->lastErrorMsg); } // escape properties in _POST into variables $name = $sqli->escapeString($_POST["name"]); $date = $sqli->escapeString($_POST["date"]); $location = $sqli->escapeString($_POST["location"]); $email = $sqli->escapeString($_POST["email"]); $comments = $sqli->escapeString($_POST["comments"]); $file_name = 'https://graph.facebook.com/' . $sqli->escapeString($photo["id"]) . '/picture'; // convert date from string to date date_default_timezone_set('America/Los_Angeles'); $date_added = date('d\\/m\\/y H:i:s PST'); !isset($date) ? $date = NOW() : ''; $sql = "INSERT INTO uploads (`name`, `date`, `location`, `photo`, `email`, `comments`, `date_added`)\n VALUES ('{$name}', '{$date}', '{$location}', '{$file_name}', '{$email}', '{$comments}', NOW());"; $result = $sqli->query($sql); if (!$result) { die('ispy insert not completed: ' . $sqli->lastErrorMsg); } // free result set