コード例 #1
0
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;
}
コード例 #3
0
 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']);
 }