<?php
//----------------------------------------------------------------------
// CrawlTrack 3.2.0
//----------------------------------------------------------------------
// Crawler Tracker for website
//----------------------------------------------------------------------
// Author: Jean-Denis Brun
//----------------------------------------------------------------------
// Website: www.crawltrack.net
//----------------------------------------------------------------------
// That script is distributed under GNU GPL license
//----------------------------------------------------------------------
// file: display-keyword.php
//----------------------------------------------------------------------
// Last update: 03/01/2010
//----------------------------------------------------------------------
if (!defined('IN_CRAWLT'))
{
echo"<h1>Hacking attempt !!!!</h1>";
exit();
}
//initialize array
$visitkeywordgoogle=array();
$visitkeywordYahoo=array();
$visitkeywordMSN=array();
$visitkeywordask=array();
$visitkeywordexalead=array();
$visitkeyword=array();
$visitkeyworddisplay=array();
$positioncd=array();
$positionstart=array();
$position=array();
$cachename=$navig.$period.$site.$displayall.$firstdayweek.$localday.$graphpos.$crawltlang;
//start the caching if fopen exist
if(function_exists('fopen'))
{
cache($cachename);
}
//database connection
$connexion = mysql_connect($crawlthost,$crawltuser,$crawltpassword) or die("MySQL connection to database problem");
$selection = mysql_select_db($crawltdb) or die("MySQL database selection problem");
//include menu
include("include/menumain.php");
include("include/menusite.php");
include("include/timecache.php");
//clean table from crawler entry
include("include/cleaning-crawler-entry.php");
//limite to
if($displayall=='no')
{
$limitquery='LIMIT '.$rowdisplay;
}
else
{
$limitquery='';
}
//request to have the keyword for Googlebot
$sqlgoogle = "SELECT keyword, count(DISTINCT CONCAT(crawlt_ip, crawlt_browser))
FROM crawlt_visits_human
INNER JOIN crawlt_keyword
ON crawlt_visits_human.crawlt_keyword_id_keyword=crawlt_keyword.id_keyword
WHERE $datetolookfor
AND crawlt_site_id_site='".sql_quote($site)."'
AND crawlt_id_crawler= '1'
GROUP BY keyword";
$requetegoogle = db_query($sqlgoogle, $connexion);
$nbrresultgoogle=mysql_num_rows($requetegoogle);
if($nbrresultgoogle>=1)
{
while ($ligne = mysql_fetch_row($requetegoogle))
{
$visitkeywordgoogle[$ligne[0]] = $ligne[1];
}
}
//query to get google details position
$sqlgoogle2 = "SELECT referer, keyword
FROM crawlt_visits_human
INNER JOIN crawlt_keyword
ON crawlt_visits_human.crawlt_keyword_id_keyword=crawlt_keyword.id_keyword
LEFT OUTER JOIN crawlt_referer
ON crawlt_visits_human.crawlt_id_referer=crawlt_referer.id_referer
WHERE $datetolookfor
AND crawlt_site_id_site='".sql_quote($site)."'
AND crawlt_id_crawler= '1'";
$requetegoogle2 = db_query($sqlgoogle2, $connexion);
$nbrresult=mysql_num_rows($requetegoogle2);
if($nbrresult>=1)
{
while ($ligne = mysql_fetch_row($requetegoogle2))
{
$referertreatment = parse_url($ligne[0]);
parse_str($referertreatment['query'],$tabvar);
if(isset($tabvar['cd']))
{
if(isset($positioncd[$ligne[1]]))
{
if($tabvar['cd']< $positioncd[$ligne[1]])
{
$positioncd[$ligne[1]]= $tabvar['cd'];
}
}
else
{
$positioncd[$ligne[1]]=$tabvar['cd'];
}
}
elseif(isset($tabvar['start']))
{
if(isset($positionstart[$ligne[1]]))
{
if($tabvar['start']< $positionstart[$ligne[1]])
{
$positionstart[$ligne[1]]= $tabvar['start'];
}
}
else
{
$positionstart[$ligne[1]]=$tabvar['start'];
}
}
}
foreach($visitkeywordgoogle as $key=>$value)
{
if (isset($positioncd[$key]) && !isset($positionstart[$key]))
{
$position[$key]=$positioncd[$key];
}
elseif (!isset($positioncd[$key]) && isset($positionstart[$key]))
{
$position[$key]=$positionstart[$key]." ≤ ? ≤ ".($positionstart[$key]+9);
}
elseif (isset($positioncd[$key]) && isset($positionstart[$key]))
{
if($positioncd[$key] < ($positionstart[$key]+10))
{
$position[$key]=$positioncd[$key];
}
else
{
$position[$key]=$positionstart[$key]." ≤ ? ≤ ".($positionstart[$key]+9);
}
}
}
}
//request to have the keyword for Yahoo
$sqlYahoo = "SELECT keyword, count(DISTINCT CONCAT(crawlt_ip, crawlt_browser))
FROM crawlt_visits_human
INNER JOIN crawlt_keyword
ON crawlt_visits_human.crawlt_keyword_id_keyword=crawlt_keyword.id_keyword
WHERE $datetolookfor
AND crawlt_site_id_site='".sql_quote($site)."'
AND crawlt_id_crawler= '2'
GROUP BY keyword";
$requeteYahoo = db_query($sqlYahoo, $connexion);
$nbrresultYahoo=mysql_num_rows($requeteYahoo);
if($nbrresultYahoo>=1)
{
while ($ligne = mysql_fetch_row($requeteYahoo))
{
$visitkeywordYahoo[$ligne[0]] = $ligne[1];
}
}
//request to have the keyword for MSN
$sqlMSN = "SELECT keyword, count(DISTINCT CONCAT(crawlt_ip, crawlt_browser))
FROM crawlt_visits_human
INNER JOIN crawlt_keyword
ON crawlt_visits_human.crawlt_keyword_id_keyword=crawlt_keyword.id_keyword
WHERE $datetolookfor
AND crawlt_site_id_site='".sql_quote($site)."'
AND crawlt_id_crawler= '3'
GROUP BY keyword";
$requeteMSN = db_query($sqlMSN, $connexion);
$nbrresultMSN=mysql_num_rows($requeteMSN);
if($nbrresultMSN>=1)
{
while ($ligne = mysql_fetch_row($requeteMSN))
{
$visitkeywordMSN[$ligne[0]] = $ligne[1];
}
}
//request to have the keyword for Ask
$sqlask = "SELECT keyword, count(DISTINCT CONCAT(crawlt_ip, crawlt_browser))
FROM crawlt_visits_human
INNER JOIN crawlt_keyword
ON crawlt_visits_human.crawlt_keyword_id_keyword=crawlt_keyword.id_keyword
WHERE $datetolookfor
AND crawlt_site_id_site='".sql_quote($site)."'
AND crawlt_id_crawler= '4'
GROUP BY keyword";
$requeteask = db_query($sqlask, $connexion);
$nbrresultask=mysql_num_rows($requeteask);
if($nbrresultask>=1)
{
while ($ligne = mysql_fetch_row($requeteask))
{
$visitkeywordask[$ligne[0]] = $ligne[1];
}
}
//request to have the keyword for Baidu
$sqlexalead = "SELECT keyword, count(DISTINCT CONCAT(crawlt_ip, crawlt_browser))
FROM crawlt_visits_human
INNER JOIN crawlt_keyword
ON crawlt_visits_human.crawlt_keyword_id_keyword=crawlt_keyword.id_keyword
WHERE $datetolookfor
AND crawlt_site_id_site='".sql_quote($site)."'
AND crawlt_id_crawler= '5'
GROUP BY keyword";
$requeteexalead = db_query($sqlexalead, $connexion);
$nbrresultexalead=mysql_num_rows($requeteexalead);
if($nbrresultexalead>=1)
{
while ($ligne = mysql_fetch_row($requeteexalead))
{
$visitkeywordexalead[$ligne[0]] = $ligne[1];
}
}
//calculation of total number of entry per keyword
$visitkeyword=array();
if($nbrresultgoogle>=1)
{
foreach($visitkeywordgoogle as $key=>$value)
{
$visitkeyword[$key]=$value;
}
}
if($nbrresultYahoo>=1)
{
foreach($visitkeywordYahoo as $key=>$value)
{
$visitkeyword[$key]=@$visitkeyword[$key] + $value;
}
}
if($nbrresultMSN>=1)
{
foreach($visitkeywordMSN as $key=>$value)
{
$visitkeyword[$key]=@$visitkeyword[$key] + $value;
}
}
if($nbrresultask>=1)
{
foreach($visitkeywordask as $key=>$value)
{
$visitkeyword[$key]=@$visitkeyword[$key] + $value;
}
}
if($nbrresultexalead>=1)
{
foreach($visitkeywordexalead as $key=>$value)
{
$visitkeyword[$key]=@$visitkeyword[$key] + $value;
}
}
arsort($visitkeyword);
//mysql connexion close
mysql_close($connexion);
//display----------------------------------------------------------------------------------------------------------
echo"<div class=\"content2\"><br><hr>\n";
echo"</div>\n";
//to close the menu rollover
echo"<div width='100%' height:'5px' onmouseover=\"javascript:montre();\"> </div>\n";
echo"<div class='tableaularge' align='center'>\n";
if(count($visitkeyword)>=1)
{
echo"<table cellpadding='0px' cellspacing='0' width='100%'>\n";
echo"<tr><th class='tableau1' colspan=\"2\" rowspan=\"2\">\n";
echo"".$language['keyword']."\n";
echo"</th>\n";
echo"<th class='tableau1' rowspan=\"2\">\n";
echo"".$language['googleposition']."\n";
echo"</th>\n";
echo"<th class='tableau2'colspan=\"5\">\n";
echo"".$language['nbr_tot_visit_seo']."\n";
echo"</th></tr>\n";
echo"<tr>\n";
echo"<th class='tableau20'>\n";
echo"".$language['ask']."\n";
echo"</th>\n";
echo"<th class='tableau20'>\n";
echo"".$language['baidu']."\n";
echo"</th>\n";
echo"<th class='tableau20'>\n";
echo"".$language['google']."\n";
echo"</th>\n";
echo"<th class='tableau20'>\n";
echo"".$language['msn']."\n";
echo"</th>\n";
echo"<th class='tableau200'>\n";
echo"".$language['yahoo']."\n";
echo"</th>\n";
echo"</tr>\n";
//counter for alternate color lane
$comptligne=2;
//counter to limite number of datas displayed
$comptdata=0;
foreach ($visitkeyword as $keyword => $value)
{
$crawlencode=urlencode($keyword);
$keyworddisplay = crawltcutkeyword($keyword,35);
if(isset($visitkeywordask[$keyword]))
{
$visitask=$visitkeywordask[$keyword];
}
else
{
$visitask='-';
}
if(isset($visitkeywordgoogle[$keyword]))
{
$visitgoogle=$visitkeywordgoogle[$keyword];
}
else
{
$visitgoogle='-';
}
if(isset($visitkeywordMSN[$keyword]))
{
$visitmsn=$visitkeywordMSN[$keyword];
}
else
{
$visitmsn='-';
}
if(isset($visitkeywordYahoo[$keyword]))
{
$visityahoo=$visitkeywordYahoo[$keyword];
}
else
{
$visityahoo='-';
}
if(isset($visitkeywordexalead[$keyword]))
{
$visitexalead=$visitkeywordexalead[$keyword];
}
else
{
$visitexalead='-';
}
if(isset($position[$keyword]))
{
$positionkeyword=$position[$keyword];
}
else
{
$positionkeyword="-";
}
//to limit the display to the selected number
if($comptdata<$rowdisplay)
{
if ($comptligne%2 ==0)
{
echo"<tr><td class='tableau3'";
if($keywordcut==1)
{
echo"onmouseover=\"javascript:montre('smenu".($comptligne+9)."');\" onmouseout=\"javascript:montre();\"";
}
echo"><a href='index.php?navig=16&period=".$period."&site=".$site."&crawler=".$crawlencode."&graphpos=".$graphpos."' >".$keyworddisplay."</a></td>\n";
echo"<td class='tableau6' width=\"8%\"". crawltkeywordwindow($crawlencode).">\n";
echo"<a href=\"#\">\n";
echo" <img src=\"./images/information.png\" width=\"16\" height=\"16\" border=\"0\" ></a>\n";
echo"</td> \n";
echo"<td class='tableau3' width=\"11%\">".$positionkeyword."</td>\n";
echo"<td class='tableau3' width=\"11%\">".numbdisp($visitask)."</td>\n";
echo"<td class='tableau3' width=\"11%\">".numbdisp($visitexalead)."</td>\n";
echo"<td class='tableau3' width=\"11%\">".numbdisp($visitgoogle)."</td>\n";
echo"<td class='tableau3' width=\"11%\">".numbdisp($visitmsn)."</td>\n";
echo"<td class='tableau5' width=\"11%\">".numbdisp($visityahoo)."</td></tr>\n";
}
else
{
echo"<tr><td class='tableau30'";
if($keywordcut==1)
{
echo"onmouseover=\"javascript:montre('smenu".($comptligne+9)."');\" onmouseout=\"javascript:montre();\"";
}
echo"><a href='index.php?navig=16&period=".$period."&site=".$site."&crawler=".$crawlencode."&graphpos=".$graphpos."' >".$keyworddisplay."</a></td>\n";
echo"<td class='tableau60' width=\"8%\"". crawltkeywordwindow($crawlencode).">\n";
echo"<a href=\"#\">\n";
echo" <img src=\"./images/information.png\" width=\"16\" height=\"16\" border=\"0\" ></a>\n";
echo"</td> \n";
echo"<td class='tableau30' width=\"11%\">".$positionkeyword."</td>\n";
echo"<td class='tableau30' width=\"11%\">".numbdisp($visitask)."</td>\n";
echo"<td class='tableau30' width=\"11%\">".numbdisp($visitexalead)."</td>\n";
echo"<td class='tableau30' width=\"11%\">".numbdisp($visitgoogle)."</td>\n";
echo"<td class='tableau30' width=\"11%\">".numbdisp($visitmsn)."</td>\n";
echo"<td class='tableau50' width=\"11%\">".numbdisp($visityahoo)."</td></tr>\n";
}
if($keywordcut==1)
{
echo"<div id=\"smenu".($comptligne+9)."\" style=\"display:none; font-size:14px; font-weight:bold; color:#ff0000; font-family:Verdana,Geneva, Arial, Helvetica, Sans-Serif; text-align:left; border:2px solid navy; position:absolute; top:".(270+(($comptligne-5)*25))."px; left:20px; background:#fff;\">\n";
echo" ".stripslashes(htmlentities(utf8_decode(urldecode($keyword))))." \n";
echo"</div>\n";
}
$comptligne++;
if($displayall=='no')
{
$comptdata++;
}
}
}
echo"</table>\n";
if(count($visitkeyword)>=$rowdisplay && $displayall=='no')
{
echo"<h2><span class=\"smalltext\">\n";
printf($language['100_lines'],$rowdisplay);
echo"<br>\n";
$crawlencode = urlencode($crawler);
echo"<a href=\"index.php?navig=$navig&period=$period&site=$site&crawler=$crawlencode&order=$order&displayall=yes&graphpos=$graphpos\">".$language['show_all']."</a></span></h2>";
}
echo"<br>\n";
}
else
{
echo"<h1>".$language['no_visit']."</h1>\n";
echo"<br>\n";
}
?>