function getQuestions($sid, $filter, $sort)
{
$db_conn = connectToDB();
$rows = array();
$query = null;
// There are seven filtering options and three sorting options. Each combination
// of these options needs to be handled differently.
if ($filter == "None") {
$query1 = null;
$query2 = null;
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
$query1 = sprintf("SELECT * FROM Question WHERE sid = %d ORDER BY time DESC", $sid);
$query2 = sprintf("SELECT * FROM Feedback WHERE sid = %d ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
$query1 = sprintf("SELECT * FROM Question WHERE sid = %d ORDER BY numvotes DESC", $sid);
$query2 = sprintf("SELECT * FROM Feedback WHERE sid = %d ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
$query1 = sprintf("SELECT * FROM Question WHERE sid = %d", $sid);
$query2 = sprintf("SELECT * FROM Feedback WHERE sid = %d", $sid);
}
// Query the Question table and fetch results
$results = mysql_query($query1, $db_conn);
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'answered' => $r["answered"], 'type' => 'Q', 'id' => $r["qid"], 'numvotes' => $r["numvotes"], 'time' => $r["time"]);
}
// Query the Feedback table and fetch results
$results = mysql_query($query2, $db_conn);
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'isread' => $r["isread"], 'type' => 'F', 'id' => $r["fid"], 'numvotes' => $r["numvotes"], 'time' => $r["time"]);
}
$rows = sortResults($rows, $sort);
} elseif ($filter == "All Questions") {
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
$query = sprintf("SELECT * FROM Question WHERE sid = %d ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
$query = sprintf("SELECT * FROM Question WHERE sid = %d ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
$query = sprintf("SELECT * FROM Question WHERE sid = %d", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'answered' => $r["answered"], 'type' => 'Q', 'id' => $r["qid"]);
}
} elseif ($filter == "All Feedback") {
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'isread' => $r["isread"], 'type' => 'F', 'id' => $r["fid"]);
}
} elseif ($filter == "Answered") {
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 1 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 1 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 1", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
$rows = array();
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'answered' => $r["answered"], 'type' => 'Q', 'id' => $r["qid"]);
}
} elseif ($filter == "Unanswered") {
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 0 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 0 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 0", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
$rows = array();
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'answered' => $r["answered"], 'type' => 'Q', 'id' => $r["qid"]);
}
} elseif ($filter == "Unread") {
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 0 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 0 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 0", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'isread' => $r["isread"], 'type' => 'F', 'id' => $r["fid"]);
}
} elseif ($filter == "Read") {
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 1 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 1 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 1", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'isread' => $r["isread"], 'type' => 'F', 'id' => $r["fid"]);
}
}
mysql_close($db_conn);
return $rows;
}
function getQuestions($sid, $filter, $sort, $uid)
{
$db_conn = connectToDB();
$uid;
if (isset($_POST['sid'])) {
$uid = $_POST['uid'];
}
$feed = array();
$query = null;
// There are seven filtering options and three sorting options. Each combination
// of these options needs to be handled differently.
if ($filter == "None" || $filter == "All Questions") {
$query = null;
if ($sort == "Newest") {
$query = sprintf("SELECT * FROM Question WHERE sid = %d ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
$query = sprintf("SELECT * FROM Question WHERE sid = %d ORDER BY numvotes DESC", $sid);
} else {
$query = sprintf("SELECT * FROM Question WHERE sid = %d", $sid);
}
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$qid = (int) $r["qid"];
$voted = hasVoted('Q', $qid, $uid, $db_conn);
$feed[] = array('voted' => $voted, 'text' => $r["text"], 'answered' => $r["answered"], 'type' => 'Q', 'id' => $r["qid"], 'numvotes' => $r["numvotes"], 'time' => $r["time"]);
}
}
if ($filter == "None" || $filter == "All Feedback") {
$query = null;
// echo "Filter By: All Feedback</br>";
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
// echo "Sort By: Newest</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
// echo "Sort By: Priority</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
// echo "Sort By: None</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$fid = (int) $r["fid"];
$voted = hasVoted('F', $fid, $uid, $db_conn);
$feed[] = array('voted' => $voted, 'text' => $r["text"], 'isread' => $r["isread"], 'type' => 'F', 'id' => $r["fid"], 'numvotes' => $r["numvotes"], 'time' => $r["time"]);
}
// If filter is None, there will be results from the Questions query in $feed already, so
// we need to sort the array to make sure the sorting is properly applied. If filter is All Feedback,
// we don't need to sort the array again, but it won't hurt to do so, and it saves a bit of control
// flow logic.
$feed = sortResults($feed, $sort);
} elseif ($filter == "Answered") {
// echo "Filter By: Answered</br>";
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
// echo "Sort By: Answered</br>";
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 1 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
// echo "Sort By: Priority</br>";
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 1 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
// echo "Sort By: None</br>";
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 1", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$qid = (int) $r["qid"];
$voted = hasVoted('Q', $qid, $uid, $db_conn);
$feed[] = array('voted' => $voted, 'text' => $r["text"], 'answered' => $r["answered"], 'type' => 'Q', 'id' => $r["qid"]);
}
} elseif ($filter == "Unanswered") {
// echo "Filter By: Unanswered</br>";
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
// echo "Sort By: Newest</br>";
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 0 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
// echo "Sort By: Priority</br>";
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 0 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
// echo "Sort By: None</br>";
$query = sprintf("SELECT * FROM Question WHERE sid = %d AND answered = 0", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$qid = (int) $r["qid"];
$voted = hasVoted('Q', $qid, $uid, $db_conn);
$feed[] = array('voted' => $voted, 'text' => $r["text"], 'answered' => $r["answered"], 'type' => 'Q', 'id' => $r["qid"]);
}
} elseif ($filter == "Unread") {
// echo "Filter By: Unread</br>";
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
// echo "Sort By: Newest</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 0 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
// echo "Sort By: Priority</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 0 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
// echo "Sort By: None</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 0", $sid);
}
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$fid = (int) $r["fid"];
$voted = hasVoted('F', $fid, $uid, $db_conn);
$feed[] = array('voted' => $voted, 'text' => $r["text"], 'isread' => $r["isread"], 'type' => 'F', 'id' => $r["fid"]);
}
} elseif ($filter == "Read") {
// echo "Filter By: Read</br>";
if ($sort == "Newest") {
// Get results sorted by timestamp in descending order
// echo "Sort By: Newest</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 1 ORDER BY time DESC", $sid);
} elseif ($sort == "Priority") {
// Get results sorted by the number of votes in descending order
// echo "Sort By: Priority</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 1 ORDER BY numvotes DESC", $sid);
} else {
// No sorting specified
// echo "Sort By: None</br>";
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d AND isread = 1", $sid);
}
// Run the query and fetch the results
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$fid = (int) $r["fid"];
$voted = hasVoted('F', $fid, $uid, $db_conn);
$feed[] = array('voted' => $voted, 'text' => $r["text"], 'isread' => $r["isread"], 'type' => 'F', 'id' => $r["fid"]);
}
}
mysql_close($db_conn);
return $feed;
}
public function testSort()
{
// Test sorting by vote count
$feed = array();
$feed[0] = array('voted' => 0, 'text' => 'test1', 'answered' => 0, 'type' => 'Q', 'id' => 12345, 'numvotes' => 1);
$feed[1] = array('voted' => 0, 'text' => 'test2', 'answered' => 0, 'type' => 'Q', 'id' => 23456, 'numvotes' => 0);
$feed[2] = array('voted' => 0, 'text' => 'test3', 'isread' => 0, 'type' => 'F', 'id' => 34567, 'numvotes' => 2);
$feed = sortResults($feed, "Priority");
$this->assertEquals(3, count($feed));
$this->assertEquals('test3', $feed[0]['text']);
$this->assertEquals('test1', $feed[1]['text']);
$this->assertEquals('test2', $feed[2]['text']);
// Test sorting by time
$db_conn = $this->initAndClearTables();
// Insert test values
$this->insertSubmission($db_conn, 'F', 'test1', 0, 0, 12345);
sleep(1);
$this->insertSubmission($db_conn, 'Q', 'test2', 0, 0, 12345);
sleep(1);
$this->insertSubmission($db_conn, 'F', 'test3', 0, 0, 12345);
sleep(1);
$this->insertSubmission($db_conn, 'Q', 'test4', 0, 0, 12345);
$feed = getQuestions(12345, "None", "Newest", 123);
$this->assertEquals(4, count($feed));
$this->assertEquals('test4', $feed[0]['text']);
$this->assertEquals('test3', $feed[1]['text']);
$this->assertEquals('test2', $feed[2]['text']);
$this->assertEquals('test1', $feed[3]['text']);
}