CODE to: connect to Ms SQL, search google, and scrape sponsored links.

September 16, 2010

| No Comments

$server = ‘local server name’;
$user = ‘local user name’;
$password = ‘password’;

// Connect to MSSQL

$link = mssql_connect($server, $user, $password);

if(!$link)

{

die(‘Something went wrong while connecting to MSSQL’);

}

else

{

$msdb=mssql_select_db(“Leads”,$link);

$msquery = “SELECT top 1 * FROM [dbo].[tbl_keywords] where position =1”;

$msresults= mssql_query($msquery);

while ($row = mssql_fetch_array($msresults)) {

$request = urlencode($row[‘keyword’] );

$id = $row[‘id’] ;

}

$y = 1;

for ( $x = 1; $x <= 10; $x += 1) {

$handle = fopen(“http://www.google.co.uk/search?q={$request}&start=”.$x*10, “rb”);

$contents = ”;

while (!feof($handle)) {

$contents .= fread($handle, 8192);

}

fclose($handle);

$contents = stripwhite($contents);

preg_match_all(‘#<h2 style=”float:right;font-weight:normal;padding:1px 0 1px 1px;font-size:11px;margin:3px 0 0″>Sponsored links</h2>(.+?)<h3>#ei’, $contents, $Sponsoreds, PREG_SET_ORDER);

foreach ($Sponsoreds as $Sponsored){

preg_match_all(‘#<cite>(.+?)</cite>#ei’, $Sponsored[1], $urls, PREG_SET_ORDER);

}

$y = 1;

preg_match_all(‘#<h2 style=”font-size:11px;padding:1px 0 4px;margin:0;text-align:left”>Sponsored links</h2>(.+?)See your ad here#ei’, $contents, $Sponsored1, PREG_SET_ORDER);

foreach ($Sponsored1 as $Sponsored){

preg_match_all(‘#<cite>(.+?)</cite>#ei’, $Sponsored[1], $urls1, PREG_SET_ORDER);

}

$urls1 =array_merge($urls, $urls1 );

$msquery = “UPDATE [Leads].[dbo].[tbl_keywords]SET [position] = 2”

. ” WHERE id like ‘$id'”;

$msresults= mssql_query($msquery);

foreach ($urls1 as $url){

$url[1] =strip_tags($url[1]);

$msquery = “INSERT INTO [Leads].[dbo].[tbl_contacts]([keyword], [url])”

. ” VALUES (‘$request’,’$url[1]’)”;

$msresults= mssql_query($msquery);

$y++;

}

echo “done” ;

function stripwhite($text){

$text = str_replace(“n”, ”, $text);

$text = str_replace(“r”, ”, $text);

$text = preg_replace(‘/ss+/’, ‘ ‘, $text);

$text = str_replace(“> “,”>”,$text);

$text = str_replace(” <“,”<“,$text);

$text = str_replace(“”,””,$text);

return $text;

}

mssql_close($msdb);

// Connect to MSSQL$link = mssql_connect($server, ‘user’, ‘password’);
if(!$link){die(‘Something went wrong while connecting to MSSQL’);}else{$msdb=mssql_select_db(“Leads”,$msconnect);$msquery = “SELECT top 1 * FROM [dbo].[tbl_keywords] where position =1”;$msresults= mssql_query($msquery);while ($row = mssql_fetch_array($msresults)) {

$request = urlencode($row[‘keyword’] );$id = $row[‘id’] ;}}$y = 1;for ( $x = 1; $x <= 10; $x += 1) {
$handle = fopen(“http://www.google.co.uk/search?q={$request}&start=”.$x*10, “rb”);$contents = ”;
while (!feof($handle)) { $contents .= fread($handle, 8192);}fclose($handle); $contents = stripwhite($contents);
preg_match_all(‘#<h2 style=”float:right;font-weight:normal;padding:1px 0 1px 1px;font-size:11px;margin:3px 0 0″>Sponsored links</h2>(.+?)<h3>#ei’, $contents, $Sponsoreds, PREG_SET_ORDER);
foreach ($Sponsoreds as $Sponsored){
preg_match_all(‘#<cite>(.+?)</cite>#ei’, $Sponsored[1], $urls, PREG_SET_ORDER);
}
$y = 1;
preg_match_all(‘#<h2 style=”font-size:11px;padding:1px 0 4px;margin:0;text-align:left”>Sponsored links</h2>(.+?)See your ad here#ei’, $contents, $Sponsored1, PREG_SET_ORDER);
foreach ($Sponsored1 as $Sponsored){
preg_match_all(‘#<cite>(.+?)</cite>#ei’, $Sponsored[1], $urls1, PREG_SET_ORDER);
}
$urls1 =array_merge($urls, $urls1 );$msquery = “UPDATE [Leads].[dbo].[tbl_keywords]SET [position] = 2″. ” WHERE id like ‘$id'”;$msresults= mssql_query($msquery);
foreach ($urls1 as $url){$url[1] =strip_tags($url[1]); $msquery = “INSERT INTO [Leads].[dbo].[tbl_contacts]([keyword], [url])” . ” VALUES (‘$request’,’$url[1]’)”;$msresults= mssql_query($msquery);
$y++;} }echo “done” ;function stripwhite($text){ $text = str_replace(“n”, ”, $text); $text = str_replace(“r”, ”, $text); $text = preg_replace(‘/ss+/’, ‘ ‘, $text); $text = str_replace(“> “,”>”,$text); $text = str_replace(” <“,”<“,$text); $text = str_replace(“”,””,$text); $text = str_replace(“”,””,$text); return $text;}
mssql_close($msdb);?>

mssql

Clive Roberts