juust ~ php oddities

google trends III

juust | 25/12/2008

How to get the urls and snippets from the Google Trends details page. The news articles on the details page are listed with an ‘Ajax’ call, they are not sent to the browser in the html source. No easy way to scrape that.

The blog articles are pretty straight forward : first the ugly fast way :

$mytitle='manuel benitez';
$mydate=''; //2008-12-24
$html=file_get_contents('http://www.google.com/trends/hottrends?q='.urlencode($mytitle).'&date=&sa=X');
$start = strpos($html, '<div class="gsc-resultsbox-visible">');
$end = strpos($html, '<div class="gsc-trailing-more-results">');
$content = substr($html, $start, $end-$start);
echo $content;
</div></div>

That returns the blog snippets, ugly. The other way : regular pattern matching : you can grab the divs that each content item has, marked with

div class=”gs-title”
div class=”gs-relativePublishedDate”
div class=”gs-snippet”
div class=”gs-visibleUrl”

from the html-source and organize them as “Content” array, after which you can list the content items with your own markup or store them in a database.

//I assume $mytitle is taken from the $_GET array.
//array 'Content' with it's members
Class Content {
var $id;
var $title;
var $pubdate;
var $snippet;
var $url;
public function __construct($id) {
$this->id=$id;
}
}
//grab the source from the google page
$html=file_get_contents('http://www.google.com/trends/hottrends?q='.urlencode($mytitle).'&date=&sa=X');
//cut out the part I want
$start = strpos($html, '<div class="gsc-resultsbox-visible">');
$end = strpos($html, '<div class="gsc-trailing-more-results">');
$content = substr($html, $start, $end-$start);
//grab the divs that contain title, publish date, snippet and url with regular pattern match
preg_match_all('!<div class=\”gs-title\”>.*?< \/div>!si', $html, $titles);
preg_match_all('!<div class=\”gs-relativePublishedDate\”>.*?< \/div>!si', $html, $pubDates);
preg_match_all('!<div class=\”gs-snippet\”>.*?< \/div>!si', $html, $snippets);
preg_match_all('!<div class=\”gs-visibleUrl\”>.*?< \/div>!si', $html, $urls);
$Contents = array();
//organize them under Content;
$count=0;
foreach($titles[0] as $title) {
//make a new instance of Content;
$Contents[] = new Content($count);
//add title
$Contents[$count]->title=$title;
$count++;
}
$count=0;
foreach($pubDates[0] as $pubDate) {
//add publishing date (contains some linebreak, remove it with strip_tags)
$Contents[$count]->pubdate=strip_tags($pubDate);
$count++;
}
$count=0;
foreach($snippets[0] as $snippet) {
//add snippet
$Contents[$count]->snippet=$snippet;
$count++;
}
$count=0;
foreach($urls[0] as $url) {
//add display url
$Contents[$count]->url=$url;
$count++;
}
//leave $count as is, the number of content-items with a 0-base array
//add rel=nofollow to links to prevent pagerank assignment to blogs
for($ct=0;$ct< $count;$ct++) {
$Contents[$ct]->url = preg_replace('/ target/', ' rel="nofollow" target', $Contents[$ct]->url);
$Contents[$ct]->title = preg_replace('/ target/', ' rel="nofollow" target', $Contents[$ct]->title);
}
//its complete, list all content-items with some markup
for($ct=0;$ct< $count;$ct++) {
echo '<h3>'.$Contents[$ct]->title.'';
echo '<p><strong>'.$Contents[$ct]->pubdate.'</strong>:<em>'.$Contents[$ct]->snippet.'</em></p>';
echo $Contents[$ct]->url.'<br />';
}
</div></div></div></div></div></div>

It ain’t perfect, but it works. the highlighter I use gets a bit confused about the preg_match_all statements containing unclosed div’s, so copying the code of the blog may not work, a text file with the source code is on trends.trismegistos.net. I added it that snippet to trendinfo.php, works fine.

Comments: 5 Comments »
Categories: google
Tags: google, scrape, trends
Comments rss
Trackback

google trends II

juust | 22/12/2008

I wanted to reply to a question elsewhere on the site, but a ‘comment’ box isn’t fit for it so I’ll put the reply here. The question was about creating ’search engine friendly’ descriptive URL’s based on keywords from the Google Trends atom feed, listing pages a graph of the trend.

I hacked a quick example together on a subdomain over at trends.trismegistos.net, just to be sure it works.

You can get a site to list http://domain.com/trend_title.html type url’s by using mod_rewrite, an apache module.

In the server directory of the application you can use an .htaccess file to set rules for file access in these folders. When the server gets request from browsers or servers it applies any rewriting rules you define in .htaccess to these requests.

I tried this one :

<ifmodule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*).html /trendinfo.php?title=$1
</ifmodule>

RewriteEngine On
sets the rewrite mechanism on

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
tell the apache server that rewriteconditions apply to file-requests that are not an existing file (F) or directory (D). If the requested filename is anywhere in the servers file table, the server dishes out that file, otherwise it will try to apply a RewriteRule. Applying the rule generates a new request, if that returns anything, the server dishes that out, otherwise it returns an htpp-404 ‘file not found’.

The actual url rewrite rule is :
RewriteRule ^(.*).html /trendinfo.php?title=$1
which means :

if any filename is requested that satisfies the mask ^(.*).html then

take everything before .html
add that as variable $1 to trendinfo.php?title=$1
see if it sticks

If the browser requests http://domain.com/bob+bowersox.html, the server will assert it is not a file or directory on the server, and test the available rules. When it notices it the requested file ends with .html, it applies the rewrite rule and tries to access http://domain.com/trendinfo.php?title=bob+bowersox.

A browsing user does not notice a thing.

In trendinfo.php I wrote some code to handle the ‘new’ request :

if(!isset($_REQUEST['title'])) {
//if there is no $1, added as title, fake a 404 "file not found" message
echo 'the emptiness…';
} else {
//get the title from the request
$mytitle=htmlentities($_REQUEST['title'], ENT_QUOTES, "UTF-8");
//put the google trends graph url together
$graphurl = 'http://www.google.com/trends/viz?hl=&q=';
$graphurl .= urlencode($mytitle);
$graphurl .= '&date='; //leave date blank to get the current graph
$graphurl .= '&graph=hot_img&sa=X';
echo "<img class=hotGraph width=280 height=190 src='$graphurl'/>";
}

…that outputs the Google trend graph on the url http://domain.com/bob+bowersox.html

I zipped the trends.trismegistos.net program files, but that might be a bit over the top, the download file contains a class that relies on a mysql table being filled every hour with new trends (by cron.php on an apache cron-job), parsing and storing the atom feed of google trends, and listing it as a cross-table in index.php spanning the past 24 hours.

You can also put this in index.php :

$feed = simplexml_load_file('http://www.google.com/trends/hottrends/atom/hourly');
$children = $feed->children('http://www.w3.org/2005/Atom');
$parts = $children->entry;
foreach ($parts as $entry) {
$details = $entry->children('http://www.w3.org/2005/Atom');
$dom = new domDocument();
$html=$details->content;
@$dom->loadHTML($html);
$anchors = $dom->getElementsByTagName('a');
foreach ($anchors as $anchor) {
$url = $anchor->getAttribute('href');
$urltext = $anchor->nodeValue;
echo '<a href="'.urlencode($urltext).'.html" target="_blank">'.$urltext.'</a> ';
}
}
unset($dom);
unset($anchors);
unset($parts);
unset($feed);

That lists the current 100 google trends with a link. If you use the .htaccess rewrite rules, the server reroutes all the links to trendinfo.php with descriptive urls.

I hope that helps.

Comments: 4 Comments »
Categories: google, php, seo
Tags: google, php, seo, trends
Comments rss
Trackback

google trends crosstab

juust | 22/11/2008

I was playing with Google Trends using another blog. Of the seven or eight posts I tried, five made it to the details page, which gave me some traffic. I am too lazy to follow 100 entries during the day, so I want a simple report on how trends develop the past day like this :

google trends watch

Now how do I get that done ?

First I make a cronjob that runs a routine to pull the Google trends xml-feed in every hour and store it in a database. I add a string for the period (year-month-day-hour).

That gives me a table like this one :

DATE	SEARCHID	POS
2008120101	baboon	78
2008120101	monkey	13
2008120102	baboon	98
2008120102	monkey	5
2008120103	monkey	3

I want an output

	2008120101	2008120102	2008120103
baboon	78	98
monkey	13	5	3

The way to do that is a crosstable query, also called a pivot table .

MySql pivot table

I have to make a routine to turn that base table into a table with the dates as column, and per phrase the position in the correct column. I want a complete row, so I take the highest period from the table and the lowest, and from that intrapolate the other periods.

function make_periods($begin, $end) {
$BeginTime=mktime(substr($begin, 8, 2),0,0, substr($begin,4,2), substr($begin,6,2), substr($begin,0,4));
$EndTime=mktime(substr($end,8,2),0,0, substr($end,4,2), substr($end,6,2), substr($end,0,4));
//divide the difference by 60minutes*60seconds
$periods = ($EndTime-$BeginTime) / 3600;
//make a row of hour-periods with "+N hour",
for($i=0;$i< ($periods+1);$i++) $myperiods[] = strftime('%Y%m%d%H', strtotime("+$i hour", $BeginTime));
//return an array with all periods
//that are to be the column headers in the crosstable
return $myperiods;
}

At some point I have to clean up the table, and I want the data to fit in one page,
so I will use 24 periods

$plength=25;
if(count($dates)>$plength) {
for($j=0;$j< ($plength+1);$j++) $usedates[] = $dates[count($dates) - ($plength-$j)];
} else {
//note : this one needs some work.
$usedates[]=$dates;
}

... and delete all older records :

//make the periods
$dates=array();
$dates = make_periods($begin, $end);
//use the $dates array, see if I have more than 24 periods
if(count($dates) > 24) {
//get period number-last-minus-24
//delete all records before that period
mysql_query("DELETE FROM `serp_trends` WHERE `date`< '".$dates[count($dates)-24] ."'", $link);
}

...that keeps the table small enough for quick querying. I have 24 periods to make columns, how do I get the position as value in each column ? I read the 'wizard' article on mysql pivot tables and that sounded difficult.

I don't get it, but I got it running :

group the records by phrase ("searchid")
use the array of periods as columns
see if the phrase has a position in a specific period,
- if so, take the position as value (it only has one per period)
- otherwise take 0 as value
sum the values
name the column "T"+period

$usedates=$dates;
$CROSTAB = "SELECT `searchid`, AVG(`pos`) AS AP ";
for($j=0;$j<count ($usedates);$j++) {
$CROSTAB.=", SUM(IF(date='".$usedates[$j]."', pos, 0)) AS T".$usedates[$j];
}
$CROSTAB .= " FROM `serp_trends` GROUP BY `searchid` ORDER BY AP ASC";

I take the average position from the grouped records, order it ascending and output that as table, first the two columns phrase (searchid) and average position, then the periods as column names.

$link=connect_data();
$result=mysql_query($CROSTAB, $link) or die(mysql_error());
//add the zebra class marker for mootool
$CTB.= '<table class="zebra" cellpadding="0" cellspacing="0"><tbody>';
//make the header row
$CTB.='<tr><td>avg</td><td>search</td>';
for($j=0;$j<count ($usedates);$j++) {
$CTB.='<td >'.substr($usedates[$j], -2).'';
}
$CTB.='</count></tr>';
//output the crosstable query result
//which has the same format
//searchid, average position, period(01)-period(24).
while($row=mysql_fetch_assoc($result)) {
$CTB.='<tr><td>'.number_format($row['AP'], '0', '0', '1').'</td><td>'.$row['searchid'].'</td>';
for($j=0;$j<count ($usedates);$j++) {
$v=$row['T'.$usedates[$j]];
if($v=='0') {
$CTB.='<td>'; } else {
$CTB.='<td>'.$v.'</td>'; }
}
$CTB.='</count></tr>';
}
$CTB.='</tbody></table>';