function getPageRating($url, $url_param)
{
$return_string = "";
$query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
$result = mysql_query($query);
$webpage_id = -1;
if (mysql_num_rows($result) == 0) {
$webpage_id = registerPage($url);
} else {
$result_row = mysql_fetch_assoc($result);
$webpage_id = $result_row['id'];
}
$webpagequery_id = getPageParameterId($webpage_id, $url_param);
$query = "SELECT (ROUND(AVG(rating),2)) AS avgRating FROM WebPageRating" . " WHERE webpagequery_id={$webpagequery_id}";
$result = mysql_query($query);
$result_row = mysql_fetch_assoc($result);
$average_rating = $result_row['avgRating'];
$return_string .= "<averagerating>{$average_rating}</averagerating>\n";
if (isset($_SESSION['user_id'])) {
$user_id = $_SESSION['user_id'];
$query = "SELECT * FROM WebPageRating WHERE user_id={$user_id}" . " AND webpagequery_id={$webpagequery_id}";
$result = mysql_query($query);
if (mysql_num_rows($result) > 0) {
$result_row = mysql_fetch_assoc($result);
$user_rating = $result_row['rating'];
$return_string .= "<userrating>{$user_rating}</userrating>\n";
} else {
$return_string .= "<userrating>0</userrating>\n";
}
} else {
$return_string .= "<userrating>0</userrating>\n";
}
return $return_string;
}
function updatePageParameter($webpage_id, $url_param, $title)
{
global $connection;
$id = getPageParameterId($webpage_id, $url_param);
$query = "UPDATE WebPageQuery SET title='{$title}'" . "WHERE id={$id}";
mysql_query($query, $connection);
return $id;
}
function deleteTag($url, $url_param, $tag)
{
$return_string = "";
if (!isset($_SESSION['user_name'])) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>You're not logged in!</errormessage>\n";
return $return_string;
}
$query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>No such tag</errormessage>\n";
return $return_string;
}
$result_row = mysql_fetch_assoc($result);
$webpage_id = $result_row['id'];
$webpagequery_id = getPageParameterId($webpage_id, $url_param);
$user_id = $_SESSION['user_id'];
$query = "SELECT * FROM Tag WHERE text='{$tag}'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>No such tag</errormessage>\n";
return $return_string;
}
$result_row = mysql_fetch_assoc($result);
$tag_id = $result_row['id'];
$query = "SELECT * FROM WebPageTag " . "WHERE webpagequery_id={$webpagequery_id} " . "AND user_id={$user_id} AND tag_id={$tag_id}";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>No such tag</errormessage>\n";
return $return_string;
} else {
$query = "DELETE FROM WebPageTag " . "WHERE webpagequery_id={$webpagequery_id} " . "AND user_id={$user_id} AND tag_id={$tag_id}";
$result = mysql_query($query);
$return_string .= $query;
}
$return_string .= "<returncode>1</returncode>";
return $return_string;
}
function rateTag($url, $url_param, $tag, $up)
{
$return_string = "";
if (!isset($_SESSION['user_name'])) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>You're not logged in!</errormessage>\n";
return $return_string;
}
$query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>No such tag</errormessage>\n";
return $return_string;
}
$result_row = mysql_fetch_assoc($result);
$webpage_id = $result_row['id'];
$webpagequery_id = getPageParameterId($webpage_id, $url_param);
$user_id = $_SESSION['user_id'];
$query = "SELECT * FROM Tag WHERE text='{$tag}'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>No such tag</errormessage>\n";
return $return_string;
}
$result_row = mysql_fetch_assoc($result);
$tag_id = $result_row['id'];
$query = "SELECT * FROM WebPageTagRating " . "WHERE webpagequery_id={$webpagequery_id} " . "AND rater_user_id={$user_id} AND tag_id={$tag_id}";
$result = mysql_query($query);
$rating = $up == 'true' ? 1 : -1;
if (mysql_num_rows($result) == 0) {
$query = "INSERT INTO WebPageTagRating (webpagequery_id, " . "rater_user_id, tag_id, rating) " . "VALUES({$webpagequery_id}, {$user_id}, {$tag_id}, {$rating})";
$result = mysql_query($query);
} else {
$query = "UPDATE WebPageTagRating " . "SET rating={$rating} " . "WHERE webpagequery_id={$webpagequery_id} " . "AND rater_user_id={$user_id} AND tag_id={$tag_id}";
$result = mysql_query($query);
}
$return_string .= "<returncode>1</returncode>";
return $return_string;
}
function addTag($url, $url_param, $tag)
{
$return_string = "";
if (!isset($_SESSION['user_name'])) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>You're not logged in!</errormessage>\n";
return $return_string;
}
$query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
$result = mysql_query($query);
$webpage_id = -1;
if (mysql_num_rows($result) == 0) {
$webpage_id = registerPage($url);
} else {
$result_row = mysql_fetch_assoc($result);
$webpage_id = $result_row['id'];
}
$webpagequery_id = getPageParameterId($webpage_id, $url_param);
$user_id = $_SESSION['user_id'];
$query = "SELECT * FROM Tag WHERE text='{$tag}'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$query2 = "INSERT INTO Tag (text) VALUES('{$tag}')";
$result = mysql_query($query2);
$result = mysql_query($query);
}
$result_row = mysql_fetch_assoc($result);
$tag_id = $result_row['id'];
$query = "SELECT * FROM WebPageTag " . "WHERE webpagequery_id={$webpagequery_id} " . "AND user_id={$user_id} AND tag_id={$tag_id}";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$query = "INSERT INTO WebPageTag (webpagequery_id, user_id, tag_id) " . "VALUES({$webpagequery_id}, {$user_id}, {$tag_id})";
$result = mysql_query($query);
}
$return_string .= "<returncode>1</returncode>";
return $return_string;
}
function updateTitle($url, $url_param, $title)
{
$return_string = "";
if (!isset($_SESSION['user_name'])) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>You're not logged in!</errormessage>\n";
return $return_string;
}
$query = "SELECT id FROM WebPage WHERE url = '{$url}'";
$result = mysql_query($query);
$webpage_id = -1;
if (mysql_num_rows($result) == 0) {
$webpage_id = registerPage($url);
} else {
$result_row = mysql_fetch_assoc($result);
$webpage_id = $result_row['id'];
}
$webpagequery_id = getPageParameterId($webpage_id, $url_param);
$user_id = $_SESSION['user_id'];
$query = "UPDATE WebPageQuery SET title='" . mysql_real_escape_string($title) . "' WHERE id={$webpagequery_id}";
$result = mysql_query($query);
$return_string .= "<returncode>1</returncode>";
return $return_string;
}
function getPageRatingBulk($urlString, $paramsString, $user_name)
{
$return_string = "";
$urls = explode("|||", $urlString);
$params = explode("|||", $paramsString);
$i = 0;
$url_sql_string = "(";
$params_sql_string = "(";
for ($i = 0; $i < count($urls); $i++) {
$url_sql_string .= "'" . $urls[$i] . "', ";
$params_sql_string .= "'" . $params[$i] . "', ";
}
$url_sql_string = substr($url_sql_string, 0, strlen($url_sql_string) - 2);
$url_sql_string .= ")";
$params_sql_string = substr($params_sql_string, 0, strlen($params_sql_string) - 2);
$params_sql_string .= ")";
$user_id = -1;
if (isset($_SESSION['user_id'])) {
$user_id = $_SESSION['user_id'];
}
$query = "SELECT tab_1.link, tab_1.avg_rating, IFNULL(user_rating, 0) AS user_rating " . "FROM " . " (SELECT CONCAT(wp.url, wpq.query) as link, (ROUND(AVG(wpr.rating),2)) AS avg_rating " . " FROM " . " (SELECT id, url FROM WebPage WHERE url IN " . $url_sql_string . ") wp, " . " (SELECT * FROM WebPageQuery WHERE query IN " . $params_sql_string . ") wpq, " . " WebPageRating wpr " . " WHERE wp.id = wpq.webpage_id " . " AND wpq.id = wpr.webpagequery_id " . " GROUP BY CONCAT(wp.url, wpq.query) " . " ) tab_1 LEFT JOIN " . " (SELECT CONCAT(wp.url, wpq.query) as link, (ROUND(AVG(wpr.rating),2)) AS user_rating " . " FROM " . " (SELECT id, url FROM WebPage WHERE url IN " . $url_sql_string . ") wp, " . " (SELECT * FROM WebPageQuery WHERE query IN " . $params_sql_string . ") wpq, " . " WebPageRating wpr " . " WHERE wp.id = wpq.webpage_id " . " AND wpq.id = wpr.webpagequery_id " . " AND wpr.user_id = {$user_id} " . " GROUP BY CONCAT(wp.url, wpq.query) " . " ) tab_2 ON (tab_1.link = tab_2.link)";
$result = mysql_query($query);
while ($result_row = mysql_fetch_assoc($result)) {
$current_link = $result_row['link'];
$current_average_rating = $result_row['avg_rating'];
$current_user_rating = $result_row['user_rating'];
for ($i = 0; $i < count($urls); $i++) {
$link = $urls[$i] . $params[$i];
if ($current_link == $link) {
$return_string .= "<link num='{$i}'>\n";
$return_string .= "<averagerating>{$current_average_rating}</averagerating>\n";
$return_string .= "<userrating>{$current_user_rating}</userrating>\n";
$return_string .= "</link>\n";
}
}
}
return $return_string;
// -------------------------------------------------------------------
foreach ($urls as $url) {
$query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
$result = mysql_query($query);
$webpage_id = -1;
if (mysql_num_rows($result) == 0) {
$webpage_id = registerPage($url);
} else {
$result_row = mysql_fetch_assoc($result);
$webpage_id = $result_row['id'];
}
$url_param = $params[$i];
$webpagequery_id = getPageParameterId($webpage_id, $url_param);
$query = "SELECT (ROUND(AVG(rating),2)) AS avgRating FROM WebPageRating" . " WHERE webpagequery_id={$webpagequery_id}";
$result = mysql_query($query);
$average_rating = 0;
$result_row = mysql_fetch_assoc($result);
if ($result_row['avgRating']) {
$average_rating = $result_row['avgRating'];
}
if ($average_rating > 0) {
$return_string .= "<link num='{$i}'>\n";
$return_string .= "<averagerating>{$average_rating}</averagerating>\n";
if (isset($_SESSION['user_id'])) {
$user_id = $_SESSION['user_id'];
$query = "SELECT * FROM WebPageRating WHERE user_id={$user_id}" . " AND webpagequery_id={$webpagequery_id}";
$result = mysql_query($query);
if (mysql_num_rows($result) > 0) {
$result_row = mysql_fetch_assoc($result);
$user_rating = $result_row['rating'];
$return_string .= "<userrating>{$user_rating}</userrating>\n";
} else {
$return_string .= "<userrating>0</userrating>\n";
}
} else {
$return_string .= "<userrating>0</userrating>\n";
}
$return_string .= "</link>\n";
}
$i++;
}
return $return_string;
}
function rateLink($from_url, $from_url_param, $to_url, $to_url_param, $up)
{
$return_string = "";
if (!isset($_SESSION['user_name'])) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>You're not logged in!</errormessage>\n";
return $return_string;
}
$from_webpage_id = getWebpageId($from_url);
$to_webpage_id = getWebpageId($to_url);
$from_webpagequery_id = getPageParameterId($from_webpage_id, $from_url_param);
$to_webpagequery_id = getPageParameterId($to_webpage_id, $to_url_param);
$user_id = $_SESSION['user_id'];
$query = "SELECT * FROM Link " . "WHERE from_webpagequery_id={$from_webpagequery_id} " . "AND to_webpagequery_id={$to_webpagequery_id}";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$return_string .= "<returncode>0</returncode>\n";
$return_string .= "<errormessage>No such link!</errormessage>\n";
return $return_string;
}
$query = "SELECT * FROM LinkRating WHERE rater_user_id={$user_id} " . "AND from_webpagequery_id={$from_webpagequery_id} " . "AND to_webpagequery_id={$to_webpagequery_id}";
$result = mysql_query($query);
$rating = $up == 'true' ? 1 : -1;
if (mysql_num_rows($result) == 0) {
$query2 = "INSERT INTO LinkRating(rater_user_id, " . "from_webpagequery_id, to_webpagequery_id, rating) " . "VALUES({$user_id}, {$from_webpagequery_id}, " . "{$to_webpagequery_id}, {$rating})";
$result2 = mysql_query($query2);
} else {
$query2 = "UPDATE LinkRating SET rating={$rating} " . "WHERE rater_user_id={$user_id} " . "AND from_webpagequery_id={$from_webpagequery_id} " . "AND to_webpagequery_id={$to_webpagequery_id}";
$result2 = mysql_query($query2);
}
$return_string .= "<returncode>1</returncode>";
return $return_string;
}
