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; }