function getUserProfile($app, $email)
{
$query = "SELECT name, major, interests FROM accounts INNER JOIN users ON accounts.id=users.account_id WHERE email=?";
$SQLparams = array($email);
$link = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
$result = mysqli_prepared_query($app, $link, $query, "s", $SQLparams);
mysqli_close($link);
//var_dump($result);
return $result[0];
//protection again extra db matches
}
<?php
$app->get('/api/recommendation', function ($request, $response, $args) {
$this->logger->info("GET /api/recommendation");
$params = $request->getQueryParams();
$token = $params['token'];
$this->logger->info($token);
$major = get_major_from_key($this, $token);
$this->logger->info($major);
$query = "SELECT movie, AVG(stars) as average FROM ratings INNER JOIN users ON ratings.user=users.account_id WHERE users.major=? GROUP BY movie ORDER BY average DESC LIMIT 3";
$SQLparams = array($major);
$link = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
$result = mysqli_prepared_query($this, $link, $query, "s", $SQLparams);
mysqli_close($link);
$data = $result[0];
return $response->withJson($data);
});
<?php
$app->post('/api/account', function ($request, $response, $args) {
$params = $request->getParsedBody();
$email = $params["email"];
$status = $params["status"];
$this->logger->info("POST /api/account");
$this->logger->info("Setting " . $email . " to " . $status);
$query = "UPDATE accounts SET status=? WHERE email=?";
$SQLparams = array($status, $email);
$link = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
$result = mysqli_prepared_query($this, $link, $query, "ss", $SQLparams);
mysqli_close($link);
return $response->withHeader('Content-Type', 'application/json')->write(json_encode(array("status" => $status)));
});
$app->get('/api/account', function ($request, $response, $args) {
$this->logger->info("GET /api/account");
$query = "SELECT email, name, status FROM accounts ORDER BY email";
$link = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
$result = mysqli_prepared_query($this, $link, $query);
mysqli_close($link);
return $response->withHeader('Content-Type', 'application/json')->write(json_encode($result));
});
public function getArtifactCodes($uri, $head, $assertion, $provenance, $pubinfo, $page, $begin_timestamp, $end_timestamp, $order, $debug)
{
// BUILDS A QUERY LIKE: SELECT artifactCode FROM uris WHERE uri = ? AND sectionID IN (x) LIMIT 1000
$types = "";
$params = $uri;
$query = "SELECT uris.artifactCode FROM uris";
if ($begin_timestamp || $end_timestamp || $order == 1 || $order == 2) {
$query .= " LEFT JOIN nanopubs ON nanopubs.artifactCode = uris.artifactCode";
}
$query .= " WHERE uri IN (";
foreach ($uri as $searchuri) {
$query .= "?,";
$types .= "s";
}
$query = rtrim($query, ',');
$query .= ")";
if ($head == "off" && $assertion == "off" && $provenance == "off" && $pubinfo == "off") {
// ALL OFF
} else {
$query .= " AND sectionID IN (";
if ($head == "on") {
$query .= URIs::$SECTION_HEAD . ",";
}
if ($assertion == "on") {
$query .= URIs::$SECTION_ASSERTION . ",";
}
if ($provenance == "on") {
$query .= URIs::$SECTION_PROVENANCE . ",";
}
if ($pubinfo == "on") {
$query .= URIs::$SECTION_PUBINFO . ",";
}
$query = rtrim($query, ',');
$query .= ")";
}
if ($begin_timestamp) {
$query .= " AND timestamp > ?";
$types .= "d";
$params[] = $begin_timestamp;
}
if ($end_timestamp) {
$query .= " AND timestamp < ?";
$types .= "d";
$params[] = $end_timestamp;
}
$query .= " GROUP BY artifactCode";
$query .= " HAVING COUNT(*) >= " . count($uri);
if ($order == 1) {
$query .= " ORDER BY timestamp DESC";
} else {
if ($order == 2) {
$query .= " ORDER BY timestamp ASC";
}
}
if ($page != 0) {
$query .= " LIMIT " . URIs::$PAGE_SIZE;
$query .= " OFFSET " . ($page - 1) * URIs::$PAGE_SIZE;
}
$data = mysqli_prepared_query($this->_conn, $query, $types, $params);
$result = array();
if ($data) {
foreach ($data as $item) {
$result[] = $item['artifactCode'];
}
}
if ($debug == true) {
$result[] = $query;
$result = array_merge($result, $params);
$result[] = $data;
}
return json_encode($result);
}
public function getIndexes()
{
$query = "SELECT title, COUNT( title ) AS indexCount, SUM( children ) AS contentCount\n\t\t\t\t\tFROM indexes\n\t\t\t\t\tGROUP BY title";
$result = mysqli_prepared_query($this->_conn, $query);
return $result;
}
$query = addConjunction($query, $filters);
$query = $query . " email=?";
$SQLformat = $SQLformat . "s";
$SQLparams[] = $email;
}
if (isset($params['major'])) {
$major = $params['major'];
$filters += 1;
$query = addConjunction($query, $filters);
$query = $query . " major=?";
$SQLformat = $SQLformat . "s";
$SQLparams[] = $major;
}
if (isset($params['movie'])) {
$movie = $params['movie'];
$filters += 1;
$query = addConjunction($query, $filters);
$query = $query . " movie=?";
$SQLformat = $SQLformat . "s";
$SQLparams[] = $movie;
}
$this->logger->info("SQL Query: " . $query);
$link = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
if ($filters == 0) {
$result = mysqli_prepared_query($this, $link, $query);
} else {
$result = mysqli_prepared_query($this, $link, $query, $SQLformat, $SQLparams);
}
mysqli_close($link);
return $response->withHeader('Content-Type', 'application/json')->write(json_encode($result));
});
function register($app, $email, $password, $accountType)
{
$hash = password_hash($password, PASSWORD_DEFAULT);
$query = "INSERT INTO accounts (email, hash) VALUES (?,?)";
$SQLparams = array($email, $hash);
$link = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
mysqli_prepared_query($app, $link, $query, "ss", $SQLparams);
$accountID = mysqli_insert_id($link);
if ($accountType == "admin") {
$app->logger->info("Registering as admin");
$query = "INSERT INTO admins(account_id) VALUES (" . $accountID . ")";
mysqli_query($link, $query);
} else {
if ($accountType == "user") {
$app->logger->info("Registering as user");
$query = "INSERT INTO users(account_id) VALUES (" . $accountID . ")";
mysqli_query($link, $query);
} else {
return array("login" => False, "error" => "Registration Failed");
}
}
mysqli_close($link);
$data['token'] = generate_session_key($this, $email);
$data['login'] = True;
$data['accountType'] = $accountType;
return $data;
}
