function get_cats($parent)
{
global $db;
$query = "SELECT * FROM " . TABLE_PREFIX . "categories WHERE parent_num={$parent}";
$result = $db->query($query);
echo sql_errorstring(__FILE__, __LINE__);
$arr[] = $parent;
while ($row = $result->fetch()) {
$id = $row[category_id];
$arr = add_arrays($arr, get_cats($id));
}
return $arr;
}
function search($searchstr, $category, $start, $per_page, $type, $domain)
{
global $length_of_link_desc, $show_meta_description, $merge_site_results, $stem_words;
global $did_you_mean_enabled, $did_you_mean_always;
global $matchless, $equivalent, $language;
global $db;
$possible_to_find = 1;
$stat = $db->prepare("SELECT domain_id FROM " . TABLE_PREFIX . "domains WHERE domain = :domain");
$stat->execute(array(':domain' => $domain));
if ($row = $stat->fetch()) {
$domain_qry = "and domain = " . $row[0];
} else {
$domain_qry = "";
}
$stat->closeCursor();
/* if there are no words to search for, quit */
if (!isset($searchstr['+']) || count($searchstr['+']) == 0) {
return null;
}
/* find all words that should _not_ be included in the result */
if (isset($searchstr['-'])) {
$wordarray = $searchstr['-'];
} else {
$wordarray = array();
}
$notlist = array();
$not_words = 0;
while ($not_words < count($wordarray)) {
if ($stem_words == 1) {
$searchword = stem($wordarray[$not_words]);
} else {
$searchword = $wordarray[$not_words];
}
$wordmd5 = substr(md5($searchword), 0, 1);
$stat = $db->prepare("SELECT link_id from " . TABLE_PREFIX . "link_keyword{$wordmd5}, " . TABLE_PREFIX . "keywords where " . TABLE_PREFIX . "link_keyword{$wordmd5}.keyword_id= " . TABLE_PREFIX . "keywords.keyword_id and keyword = :keyword");
$stat->execute(array(':keyword' => $searchword));
while ($row = $stat->fetch()) {
$notlist[$not_words]['id'][$row[0]] = 1;
}
$not_words++;
}
/* find all phrases */
if (isset($searchstr['+s'])) {
$wordarray = $searchstr['+s'];
} else {
$wordarray = array();
}
$phrase_words = 0;
while ($phrase_words < count($wordarray)) {
$searchword = $wordarray[$phrase_words];
$searchword = str_replace("|", "", $searchword);
$searchword = str_replace("%", "|%", $searchword);
$searchword = str_replace("_", "|_", $searchword);
$stat = $db->prepare("SELECT link_id from " . TABLE_PREFIX . "links where fulltxt like :keyword escape '|'");
$stat->execute(array(':keyword' => "%" . $searchword . "%"));
echo sql_errorstring(__FILE__, __LINE__);
$row = $stat->fetch();
if (!$row) {
$possible_to_find = 0;
$stat->closeCursor();
break;
}
$phraselist[$phrase_words]['id'][$row[0]] = 1;
while ($row = $stat->fetch()) {
$phraselist[$phrase_words]['id'][$row[0]] = 1;
}
$phrase_words++;
}
if ($category > 0 && $possible_to_find == 1) {
$allcats = get_cats($category);
$catlist = implode(",", $allcats);
$result = $db->query("SELECT link_id FROM " . TABLE_PREFIX . "links, " . TABLE_PREFIX . "sites, " . TABLE_PREFIX . "categories, " . TABLE_PREFIX . "site_category where " . TABLE_PREFIX . "links.site_id = " . TABLE_PREFIX . "sites.site_id and " . TABLE_PREFIX . "sites.site_id = " . TABLE_PREFIX . "site_category.site_id and " . TABLE_PREFIX . "site_category.category_id in ({$catlist})");
echo sql_errorstring(__FILE__, __LINE__);
$row = $result->fetch();
if (!$row) {
$possible_to_find = 0;
} else {
$category_list[$row[0]] = 1;
while ($row = $result->fetch()) {
$category_list[$row[0]] = 1;
}
}
$result->closeCursor();
}
/* find individual words */
$word_not_found = array();
$wordarray = $searchstr['+'];
$words = 0;
while ($words < count($wordarray) && $possible_to_find == 1) {
if ($stem_words == 1) {
$searchword = stem($wordarray[$words]);
} else {
$searchword = $wordarray[$words];
}
$wordmd5 = substr(md5($searchword), 0, 1);
$stat = $db->prepare("SELECT distinct link_id, weight, domain FROM " . TABLE_PREFIX . "link_keyword{$wordmd5}, " . TABLE_PREFIX . "keywords WHERE " . TABLE_PREFIX . "link_keyword{$wordmd5}.keyword_id= " . TABLE_PREFIX . "keywords.keyword_id AND keyword=:keyword {$domain_qry}\tORDER\tBY\tweight\tDESC");
$stat->execute(array(':keyword' => $searchword));
echo sql_errorstring(__FILE__, __LINE__);
$row = $stat->fetch();
if (!$row) {
$word_not_found[$wordarray[$words]] = 1;
if ($type != "or") {
$possible_to_find = 0;
$stat->closeCursor();
break;
}
}
if ($type == "or") {
$indx = 0;
} else {
$indx = $words;
}
do {
$linklist[$indx]['id'][] = $row[0];
$domains[$row[0]] = $row[2];
$linklist[$indx]['weight'][$row[0]] = $row[1];
} while ($row = $stat->fetch());
$words++;
}
if ($type == "or") {
$words = 1;
}
$result_array_full = array();
if ($possible_to_find != 0) {
if ($words == 1 && $not_words == 0 && $category < 1) {
//if there is only one search word, we already have the result
$result_array_full = $linklist[0]['weight'];
} else {
//otherwise build an intersection of all the results
$j = 1;
$min = 0;
while ($j < $words) {
if (count($linklist[$min]['id']) > count($linklist[$j]['id'])) {
$min = $j;
}
$j++;
}
$j = 0;
$temp_array = $linklist[$min]['id'];
$count = 0;
while ($j < count($temp_array)) {
$k = 0;
//and word counter
$n = 0;
//not word counter
$o = 0;
//phrase word counter
$weight = 1;
$break = 0;
while ($k < $words && $break == 0) {
if (isset($linklist[$k]['weight'][$temp_array[$j]]) && $linklist[$k]['weight'][$temp_array[$j]] > 0) {
$weight = $weight + $linklist[$k]['weight'][$temp_array[$j]];
} else {
$break = 1;
}
$k++;
}
while ($n < $not_words && $break == 0) {
if ($notlist[$n]['id'][$temp_array[$j]] > 0) {
$break = 1;
}
$n++;
}
while ($o < $phrase_words && $break == 0) {
if (!isset($phraselist[$n]['id'][$temp_array[$j]]) || $phraselist[$n]['id'][$temp_array[$j]] != 1) {
$break = 1;
}
$o++;
}
if ($break == 0 && $category > 0 && $category_list[$temp_array[$j]] != 1) {
$break = 1;
}
if ($break == 0) {
$result_array_full[$temp_array[$j]] = $weight;
$count++;
}
$j++;
}
}
}
if ((count($result_array_full) == 0 || $possible_to_find == 0 || $did_you_mean_always == 1) && $did_you_mean_enabled == 1) {
/* search for word pairs written as two words where a single words
for example: when the user typed "full colour", also search for
fullcolour and full-colour */
for ($idx = 0; $idx < count($searchstr['+']) - 1; $idx++) {
$word = $searchstr['+'][$idx] . " " . $searchstr['+'][$idx + 1];
$near_word = $searchstr['+'][$idx] . $searchstr['+'][$idx + 1];
/* words that are in the "nonpareil" list are excluded in searching
for alternatives */
if (!isset($matchless[$near_word])) {
$stat = $db->prepare("SELECT keyword FROM " . TABLE_PREFIX . "keywords WHERE keyword=:keyword");
if ($stat->execute(array(':keyword' => $near_word)) && ($row = $stat->fetch())) {
$near_words[$word] = latin1_to_html($near_word);
$stat->closeCursor();
}
}
$near_word = $searchstr['+'][$idx] . "-" . $searchstr['+'][$idx + 1];
if (!isset($matchless[$near_word])) {
$stat = $db->prepare("SELECT keyword FROM " . TABLE_PREFIX . "keywords WHERE keyword=:keyword");
if ($stat->execute(array(':keyword' => $near_word)) && ($row = $stat->fetch())) {
$near_words[$word] = latin1_to_html($near_word);
$stat->closeCursor();
}
}
}
/* then search for "near words" for the individual words */
reset($searchstr['+']);
foreach ($searchstr['+'] as $word) {
/* words that are in the "nonpareil" list are excluded in searching
for alternatives */
if (isset($matchless[$word]) && $matchless[$word] == 1) {
continue;
}
/* search for alternatives in the explicit equivalents word list first */
if (isset($equivalent[$word]) && strlen($equivalent[$word]) > 0) {
$near_words[$word] = latin1_to_html($equivalent[$word]);
continue;
}
/* if there are misspelled words, show only alternatives for the
misspelled words, (so, if the current word is not in the list
of misspelled words, exclude it from the search for alternatives */
if (count($word_not_found) > 0 && !(isset($word_not_found[$word]) && $word_not_found[$word] == 1)) {
continue;
}
$word = sanitize($word);
/* use the double-metaphone to find close words */
$meta = double_metaphone($word);
if (!isset($meta["primary"]) || strlen($meta["primary"]) == 0) {
continue;
}
/* no metaphone, don't match anything */
$where = "metaphone1='" . $meta["primary"] . "' OR metaphone2='" . $meta["primary"] . "'";
if (isset($meta["secondary"]) && strlen($meta["secondary"]) > 0) {
$where .= " OR metaphone1='" . $meta["secondary"] . "' OR metaphone2='" . $meta["secondary"] . "'";
}
$result = $db->query("SELECT keyword FROM " . TABLE_PREFIX . "keywords WHERE {$where}");
/* adapted from http://www.mdj.us/web-development/php-programming/creating-better-search-suggestions-with-sphider/
but using a double-metaphone filter (instead of SOUNDEX) and
adding a filter for accented characters */
$max_distance = 3;
$max_similar = 0;
$near_word = "";
while ($result && ($row = $result->fetch())) {
$item = $row[0];
if (strcasecmp($item, $word) != 0) {
$distance = levenshtein($item, $word);
$distance_na = levenshtein(remove_accents($item), $word);
if ($distance_na < $distance) {
$distance = $distance_na;
}
if ($distance < $max_distance) {
$max_distance = $distance;
$near_word = $item;
}
if ($distance == $max_distance) {
$similar = similar_text($item, $word);
if ($similar >= $max_similar) {
$max_distance = $distance;
$max_similar = $similar;
$near_word = $item;
}
}
}
}
if ($near_word != "") {
$near_words[$word] = latin1_to_html($near_word);
} else {
if (isset($word_not_found[$word]) && $word_not_found[$word] == 1 && count($wordarray) > 1) {
$near_words[$word] = "/{$word}";
}
}
}
if (!isset($near_words)) {
$near_words = "";
}
$res['did_you_mean'] = $near_words;
if (count($result_array_full) == 0 || $possible_to_find == 0) {
return $res;
}
}
if (count($result_array_full) == 0) {
return null;
}
arsort($result_array_full);
if ($merge_site_results == 1 && $domain_qry == "") {
while (list($key, $value) = each($result_array_full)) {
if (!isset($domains_to_show[$domains[$key]])) {
$result_array_temp[$key] = $value;
$domains_to_show[$domains[$key]] = 1;
} else {
if ($domains_to_show[$domains[$key]] == 1) {
$domains_to_show[$domains[$key]] = array($key => $value);
}
}
}
} else {
$result_array_temp = $result_array_full;
}
while (list($key, $value) = each($result_array_temp)) {
$result_array[$key] = $value;
if (isset($domains_to_show[$domains[$key]]) && $domains_to_show[$domains[$key]] != 1) {
list($k, $v) = each($domains_to_show[$domains[$key]]);
$result_array[$k] = $v;
}
}
$results = count($result_array);
$keys = array_keys($result_array);
$maxweight = $result_array[$keys[0]];
for ($i = ($start - 1) * $per_page; $i < min($results, ($start - 1) * $per_page + $per_page); $i++) {
$in[] = $keys[$i];
}
if (!is_array($in)) {
$res['results'] = $results;
return $res;
}
$inlist = implode(",", $in);
if ($length_of_link_desc == 0) {
$fulltxt = "fulltxt";
} else {
$fulltxt = "substring(fulltxt, 1, {$length_of_link_desc})";
}
$query = "SELECT distinct link_id, url, title, description, language, {$fulltxt}, size FROM " . TABLE_PREFIX . "links WHERE link_id in ({$inlist})";
$result = $db->query($query);
echo sql_errorstring(__FILE__, __LINE__);
$i = 0;
while ($row = $result->fetch()) {
$res[$i]['title'] = $row[2];
$res[$i]['url'] = $row[1];
if (isset($row[3]) && $row[3] != null && $show_meta_description == 1) {
$res[$i]['summary'] = $row[3];
} else {
$res[$i]['summary'] = "";
}
$res[$i]['lang'] = $row[4];
$res[$i]['fulltxt'] = $row[5];
$res[$i]['size'] = $row[6];
$res[$i]['weight'] = $result_array[$row[0]];
/* if a language has been set for this page, and it is _not_ the
* same language as the user language, decrease the weight
*/
if (isset($row[4]) && $row[4] != null && strlen($row[4]) > 0 && strcasecmp($row[4], $language) != 0) {
$res[$i]['weight'] *= 0.5;
}
$dom_result = $db->query("select domain from " . TABLE_PREFIX . "domains where domain_id='" . $domains[$row[0]] . "'");
$dom_row = $dom_result->fetch();
$res[$i]['domain'] = $dom_row[0];
$i++;
}
if ($merge_site_results && $domain_qry == "") {
sort_with_domains($res);
} else {
usort($res, "cmp");
}
echo sql_errorstring(__FILE__, __LINE__);
/* sorting destroys the other columns in the array, restore */
if (isset($near_words)) {
$res['did_you_mean'] = $near_words;
}
$res['maxweight'] = $maxweight;
$res['results'] = $results;
return $res;
/**/
}
function check_for_removal($url)
{
global $command_line;
global $db;
$result = $db->query("select link_id, visible from " . TABLE_PREFIX . "links" . " where url='{$url}'");
echo sql_errorstring(__FILE__, __LINE__);
$row = $result->fetch();
$result->closeCursor();
if ($row) {
$link_id = $row[0];
$visible = $row[1];
if ($visible > 0) {
$visible--;
$db->exec("update " . TABLE_PREFIX . "links set visible={$visible} where link_id={$link_id}");
echo sql_errorstring(__FILE__, __LINE__);
} else {
$db->exec("delete from " . TABLE_PREFIX . "links where link_id={$link_id}");
echo sql_errorstring(__FILE__, __LINE__);
for ($i = 0; $i <= 15; $i++) {
$char = dechex($i);
$db->exec("delete from " . TABLE_PREFIX . "link_keyword{$char} where link_id={$link_id}");
echo sql_errorstring(__FILE__, __LINE__);
}
printStandardReport('pageRemoved', $command_line);
}
}
}
function statisticsForm($type)
{
global $log_dir;
global $db;
?>
<div id='submenu'>
<ul>
<li><a href="admin.php?f=statistics&type=keywords">Top keywords</a></li>
<li><a href="admin.php?f=statistics&type=pages">Largest pages</a></li>
<li><a href="admin.php?f=statistics&type=top_searches">Most popular searches</a></li>
<li><a href="admin.php?f=statistics&type=log">Search log</a></li>
<li><a href="admin.php?f=statistics&type=spidering_log">Spidering logs</a></li>
</ul>
</div>
<?php
if ($type == "") {
$cachedSumQuery = "select sum(length(fulltxt)) from " . TABLE_PREFIX . "links";
$result = $db->query("select sum(length(fulltxt)) from " . TABLE_PREFIX . "links");
echo sql_errorstring(__FILE__, __LINE__);
if ($row = $result->fetch()) {
$cachedSumSize = $row[0];
}
$cachedSumSize = number_format($cachedSumSize / 1024, 2);
$sitesSizeQuery = "select sum(size) from " . TABLE_PREFIX . "links";
$result = $db->query("{$sitesSizeQuery}");
echo sql_errorstring(__FILE__, __LINE__);
if ($row = $result->fetch()) {
$sitesSize = $row[0];
}
$sitesSize = number_format($sitesSize, 2);
$stats = getStatistics();
print "<br/><div align=\"center\"><table cellspacing =\"0\" cellpadding=\"0\" class=\"darkgrey\"><tr><td><table cellpadding=\"3\" cellspacing = \"1\"><tr class=\"grey\"><td><b>Sites:</b></td><td align=\"center\">" . $stats['sites'] . "</td></tr>";
print "<tr class=\"white\"><td><b>Links:</b></td><td align=\"center\"> " . $stats['links'] . "</td></tr>";
print "<tr class=\"grey\"><td><b>Categories:</b></td><td align=\"center\"> " . $stats['categories'] . "</td></tr>";
print "<tr class=\"white\"><td><b>Keywords:</b></td><td align=\"center\"> " . $stats['keywords'] . "</td></tr>";
print "<tr class=\"grey\"><td><b>Keyword-link relations:</b></td><td align=\"center\"> " . $stats['index'] . "</td></tr>";
print "<tr class=\"white\"><td><b>Cached texts total:</b></td><td align=\"center\"> {$cachedSumSize} kb</td></tr>";
print "<tr class=\"grey\"><td><b>Sites size total:</b></td><td align=\"center\"> {$sitesSize} kb</td></tr>";
print "</table></td></tr></table></div>";
}
if ($type == 'keywords') {
$class = "grey";
print "<br/><div align=\"center\"><table cellspacing =\"0\" cellpadding=\"0\" class=\"darkgrey\"><tr><td><table cellpadding=\"3\" cellspacing = \"1\"><tr class=\"grey\"><td><b>Keyword</b></td><td><b>Occurrences</b></td></tr>";
for ($i = 0; $i <= 15; $i++) {
$char = dechex($i);
$result = $db->query("select keyword, count(" . TABLE_PREFIX . "link_keyword{$char}.keyword_id) as x from " . TABLE_PREFIX . "keywords, " . TABLE_PREFIX . "link_keyword{$char} where " . TABLE_PREFIX . "keywords.keyword_id = " . TABLE_PREFIX . "link_keyword{$char}.keyword_id group by keyword order by x desc limit 30");
echo sql_errorstring(__FILE__, __LINE__);
while ($result && ($row = $result->fetch())) {
$topwords[$row[0]] = $row[1];
}
$result->closeCursor();
}
arsort($topwords);
$count = 0;
while ((list($word, $weight) = each($topwords)) && $count <= 15 * 30) {
$count++;
if ($class == "white") {
$class = "grey";
} else {
$class = "white";
}
print "<tr class=\"{$class}\"><td align=\"left\">" . $word . "</td><td> " . $weight . "</td></tr>\n";
}
print "</table></td></tr></table></div>";
}
if ($type == 'pages') {
$class = "grey";
?>
<br/><div align="center">
<table cellspacing ="0" cellpadding="0" class="darkgrey"><tr><td>
<table cellpadding="2" cellspacing="1">
<tr class="grey"><td>
<b>Page</b></td>
<td><b>Text size</b></td></tr>
<?php
$result = $db->query("select " . TABLE_PREFIX . "links.link_id, url, length(fulltxt) as x from " . TABLE_PREFIX . "links order by x desc limit 20");
echo sql_errorstring(__FILE__, __LINE__);
while ($row = $result->fetch()) {
if ($class == "white") {
$class = "grey";
} else {
$class = "white";
}
$url = $row[1];
$sum = number_format($row[2] / 1024, 2);
print "<tr class=\"{$class}\"><td align=\"left\"><a href=\"{$url}\">" . $url . "</td><td align= \"center\"> " . $sum . "kb</td></tr>";
}
print "</table></td></tr></table></div>";
}
if ($type == 'top_searches') {
$class = "grey";
print "<br/><div align=\"center\"><table cellspacing =\"0\" cellpadding=\"0\" class=\"darkgrey\"><tr><td><table cellpadding=\"3\" cellspacing = \"1\"><tr class=\"grey\"><td><b>Query</b></td><td><b>Count</b></td><td><b> Average results</b></td><td><b>Last\tqueried</b></td></tr>";
$result = $db->query("select query, count(*) as c, max(time), avg(results) from " . TABLE_PREFIX . "query_log group by query order by c desc");
echo sql_errorstring(__FILE__, __LINE__);
while ($row = $result->fetch()) {
if ($class == "white") {
$class = "grey";
} else {
$class = "white";
}
$word = utf8_decode($row[0]);
$times = $row[1];
$date = $row[2];
$avg = number_format($row[3], 1);
print "<tr class=\"{$class}\"><td align=\"left\">" . htmlentities($word) . "</td><td align=\"center\"> " . $times . "</td><td align=\"center\"> " . $avg . "</td><td align=\"center\"> " . $date . "</td></tr>";
}
print "</table></td></tr></table></div>";
}
if ($type == 'log') {
$class = "grey";
print "<br/><div align=\"center\"><table cellspacing =\"0\" cellpadding=\"0\" class=\"darkgrey\"><tr><td><table cellpadding=\"3\" cellspacing = \"1\"><tr class=\"grey\"><td align=\"center\"><b>Query</b></td><td align=\"center\"><b>Results</b></td><td\talign=\"center\"><b>Queried\tat</b></td><td\talign=\"center\"><b>Time\ttaken</b></td></tr>";
$result = $db->query("select query, time, elapsed, results from " . TABLE_PREFIX . "query_log order by time desc");
echo sql_errorstring(__FILE__, __LINE__);
while ($row = $result->fetch()) {
if ($class == "white") {
$class = "grey";
} else {
$class = "white";
}
$word = utf8_decode($row[0]);
$time = $row[1];
$elapsed = $row[2];
$results = $row[3];
print "<tr class=\"{$class}\"><td align=\"left\">" . htmlentities($word) . "</td><td align=\"center\"> " . $results . "</td><td align=\"center\"> " . $time . "</td><td align=\"center\"> " . $elapsed . "</td></tr>";
}
print "</table></td></tr></table></div>";
}
if ($type == 'spidering_log') {
$class = "grey";
$files = get_dir_contents($log_dir);
if (count($files) > 0) {
print "<br/><div align=\"center\"><table cellspacing =\"0\" cellpadding=\"0\" class=\"darkgrey\"><tr><td><table cellpadding=\"3\" cellspacing = \"1\"><tr class=\"grey\"><td align=\"center\"><b>File</b></td><td align=\"center\"><b>Time</b></td><td\talign=\"center\"><b></b></td></tr>";
for ($i = 0; $i < count($files); $i++) {
$file = $files[$i];
$year = substr($file, 0, 2);
$month = substr($file, 2, 2);
$day = substr($file, 4, 2);
$hour = substr($file, 6, 2);
$minute = substr($file, 8, 2);
if ($class == "white") {
$class = "grey";
} else {
$class = "white";
}
print "<tr class=\"{$class}\"><td align=\"left\"><a href='{$log_dir}/{$file}' tareget='_blank'>{$file}</a></td><td align=\"center\"> 20{$year}-{$month}-{$day} {$hour}:{$minute}</td><td align=\"center\"> <a href='?f=delete_log&file={$file}' id='small_button'>Delete</a></td></tr>";
}
print "</table></td></tr></table></div>";
} else {
?>
<br/><br/>
<center><b>No saved logs.</b></center>
<?php
}
}
}
die("Cannot read zip file");
}
while (!gzeof($zp)) {
$data = gzgets($zp, 8192);
// buffer php
fwrite($fp, $data);
}
fclose($fp);
gzclose($zp);
$file = "backup.sql";
flush();
set_time_limit(1000);
$file_temp = fread(fopen($backup_path . $file, "r"), filesize($backup_path . $file));
$query = explode(";#%%\n", $file_temp);
for ($i = 0; $i < count($query) - 1; $i++) {
$db->exec($query[$i]) or die(sql_errorstring(__FILE__, __LINE__));
}
unlink($backup_path . $file);
echo "<table width=\"94%\"><tr><td><b>Your restore\r\nrequest was processed.</b> If you did not receive any errors on the screen, then\r\nyou should find that your database tables have been restored.<br></td></tr></table>";
}
}
if (isset($file) && $del == 1) {
@unlink($backup_path . $file);
}
?>
</TD>
</TR>
<TR>
<TD valign="top">
<table width="100%" cellspacing="0">
function get_domains()
{
global $db;
$result = $db->query("select domain_id, domain from " . TABLE_PREFIX . "domains");
echo sql_errorstring(__FILE__, __LINE__);
$domains = array();
while ($row = $result->fetch()) {
$domains[$row[1]] = $row[0];
}
return $domains;
}
