Website Scraping with PHP,Curl and MySQL Database

sorry am having bit of trouble here storing scraped content into MYSQL database.

So what am trying to do is save the Module Code and Module Title from this site [http://www.ucc.ie/modules/descriptions/page014.html][1] into MYSQL database. Am able to get content from the site alright but I just cant seems to be able to save scraped content into database.I keep getting the error “Query was empty” while the query is not.

Been spending sometime doing this and can’t seems to get around it. Any help in solving this problem would be appreciated.

<?php

//Here is a simple web scraping example using the PHP DOM that tries to get the largest      text body of a HTML document. I needed it for a spider 
//that had to show a short description for a page. It assumes that document annotation can be the largest <div>, <td> or <p> element in the //page.
//In the example I show a way to prevent a bug in the DOM as it sometimes just doesn't recognize html encoding. It seems to work if you put 
//charset meta tag right after the head tag of the document.

$host="localhost";
$user="root";
$password="";
mysql_connect($host,$user,$password) or die("could not connect to the host");
mysql_select_db("plot_a_coursedb");


$ch= curl_init();
curl_setopt ($ch, CURLOPT_URL, 'http://www.ucc.ie/modules/descriptions/page014.html' );
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch,CURLOPT_VERBOSE,1);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.13) Gecko/20080311 Firefox/2.0.0.13');
curl_setopt ($ch, CURLOPT_REFERER, 'http://localhost:8080/extractsite/index2.html');     //just a fake referer
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch,CURLOPT_POST,0);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 20);

$html= curl_exec($ch);
$html1= curl_getinfo($ch);

//try to get page encoding as it was sent from server
if ($html1['content_type']){
$arr= explode('charset=',$html1['content_type']);
$csethdr= strtolower(trim($arr[1]));
} else {
$csethdr= false;
}

$cset= false;
$arr= array();

//This has to replace page meta tags for charset with utf-8, but it doesn't actually help(see the bug info).
if (preg_match_all('/(<metas*http-equiv="Content-Type"s*content="[^;]*;
s*charset=([^"]*?)(?:"|;)[^>]*>)/' //merge this line
,$html,$arr,PREG_PATTERN_ORDER)){
$cset= strtolower(trim($arr[2][0]));
if ($cset!='utf-8'||$cset!=$csethdr){
    $new= str_replace($arr[2][0],'utf-8',$arr[1][0]);
    $html= str_replace($arr[1][0],$new,$html);
    $cset= $csethdr;
} else {
    $cset= false;
}

if ($cset=='utf-8'){
    $cset= false;
}
}
unset($arr);
if ($cset){
$html= iconv($cset,'utf-8',$html);
}
unset($cset);

//solve dom bug
$html=preg_replace('/<head[^>]*>/','<head><META HTTP-EQUIV="Content-Type"   CONTENT="text/html; charset=utf-8">',$html);

@$dom= new DOMDocument();
@$dom->loadHTML($html);
@$dom->preserveWhiteSpace = false;

function getMaxTextBody($dom){
$content = $dom->getElementsByTagname('div');
$content2= $dom->getElementsByTagname('td');
$content3= $dom->getElementsByTagname('p');
$content4 = $dom->getElementsByTagname('B');

$new = array();
foreach ($content as $value) {
    $new[]= $value;
    unset($value);
}
unset($content);

foreach ($content2 as $value) {
    $new[]= $value;
    unset($value);
}
unset($content2);

foreach ($content3 as $value) {
    $new[]= $value;
    unset($value);
}
unset($content3);

foreach ($content4 as $value) {
    $new[]= $value;
    unset($value);
}
unset($content4);

$maxlen= 0;
$result= '';
foreach ($new as $item)
{
    $str= $item->nodeValue;
    if (strlen($str)>$maxlen){
        $content1= $item->getElementsByTagName('div');
        $content2= $item->getElementsByTagname('td');
        $content3= $item->getElementsByTagname('p');
        $content4 = $dom->getElementsByTagname('b');
        
        $contentnew= array();
        foreach ($content1 as $value) {
            $contentnew[]= $value;
            unset($value);
        }
        unset($content1);
        
        foreach ($content2 as $value) {
            $contentnew[]= $value;
            unset($value);
        }
        unset($content2);
        
        foreach ($content3 as $value) {
            $contentnew[]= $value;
            unset($value);
        }
        unset($content3);
        
        foreach ($content4 as $value) {
            $contentnew[]= $value;
            unset($value);
        }
        unset($content4);



        // Insert data into database query
        $query = mysql_query("INSERT INTO data (div,td,p,b) VALUES ('$content1','$content2','$content3','$content4')"); 
        mysql_query($query) or die (mysql_error());

        // Close the database connection
        mysql_close();


        if (count($contentnew)==0){
            $result= $str;
        } else {
            foreach ($contentnew as $value) {
                $str1= getMaxTextBody($value);
                $str2= $value->nodeValue;
                    //let's say largest body has more than 50% of the text in its parent
                                if (strlen($str1)*2<strlen($str2)){
                    $str1= $str2;
                }
                if (strlen($str1)*2>strlen($str)&&strlen($str1)>$maxlen){
                    $result= $str1;
                } elseif (strlen($str1)>$maxlen){
                    $result= $str1;
                }
                $maxlen= strlen($result);
            }
        }
        $maxlen= strlen($result);
        unset($contnentnew);
    }
}

unset($new);
return $result;

}
print getMaxTextBody($dom);


?>

JavaScript
​x
 
<?php​//Here is a simple web scraping example using the PHP DOM that tries to get the largest      text body of a HTML document. I needed it for a spider //that had to show a short description for a page. It assumes that document annotation can be the largest <div>, <td> or <p> element in the //page.//In the example I show a way to prevent a bug in the DOM as it sometimes just doesn't recognize html encoding. It seems to work if you put //charset meta tag right after the head tag of the document.​$host="localhost";$user="root";$password="";mysql_connect($host,$user,$password) or die("could not connect to the host");mysql_select_db("plot_a_coursedb");​​$ch= curl_init();curl_setopt ($ch, CURLOPT_URL, 'http://www.ucc.ie/modules/descriptions/page014.html' );curl_setopt($ch, CURLOPT_HEADER, 0);curl_setopt($ch,CURLOPT_VERBOSE,1);curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.13) Gecko/20080311 Firefox/2.0.0.13');curl_setopt ($ch, CURLOPT_REFERER, 'http://localhost:8080/extractsite/index2.html');     //just a fake referercurl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);curl_setopt($ch,CURLOPT_POST,0);curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 20);​$html= curl_exec($ch);$html1= curl_getinfo($ch);​//try to get page encoding as it was sent from serverif ($html1['content_type']){$arr= explode('charset=',$html1['content_type']);$csethdr= strtolower(trim($arr[1]));} else {$csethdr= false;}​$cset= false;$arr= array();​//This has to replace page meta tags for charset with utf-8, but it doesn't actually help(see the bug info).if (preg_match_all('/(<metas*http-equiv="Content-Type"s*content="[^;]*;s*charset=([^"]*?)(?:"|;)[^>]*>)/' //merge this line,$html,$arr,PREG_PATTERN_ORDER)){$cset= strtolower(trim($arr[2][0]));if ($cset!='utf-8'||$cset!=$csethdr){    $new= str_replace($arr[2][0],'utf-8',$arr[1][0]);    $html= str_replace($arr[1][0],$new,$html);    $cset= $csethdr;} else {    $cset= false;}​if ($cset=='utf-8'){    $cset= false;}}unset($arr);if ($cset){$html= iconv($cset,'utf-8',$html);}unset($cset);​//solve dom bug$html=preg_replace('/<head[^>]*>/','<head><META HTTP-EQUIV="Content-Type"   CONTENT="text/html; charset=utf-8">',$html);​@$dom= new DOMDocument();@$dom->loadHTML($html);@$dom->preserveWhiteSpace = false;​function getMaxTextBody($dom){$content = $dom->getElementsByTagname('div');$content2= $dom->getElementsByTagname('td');$content3= $dom->getElementsByTagname('p');$content4 = $dom->getElementsByTagname('B');​$new = array();foreach ($content as $value) {    $new[]= $value;    unset($value);}unset($content);​foreach ($content2 as $value) {    $new[]= $value;    unset($value);}unset($content2);​foreach ($content3 as $value) {    $new[]= $value;    unset($value);}unset($content3);​foreach ($content4 as $value) {    $new[]= $value;    unset($value);}unset($content4);​$maxlen= 0;$result= '';foreach ($new as $item){    $str= $item->nodeValue;    if (strlen($str)>$maxlen){        $content1= $item->getElementsByTagName('div');        $content2= $item->getElementsByTagname('td');        $content3= $item->getElementsByTagname('p');        $content4 = $dom->getElementsByTagname('b');                $contentnew= array();        foreach ($content1 as $value) {            $contentnew[]= $value;            unset($value);        }        unset($content1);                foreach ($content2 as $value) {            $contentnew[]= $value;            unset($value);        }        unset($content2);                foreach ($content3 as $value) {            $contentnew[]= $value;            unset($value);        }        unset($content3);                foreach ($content4 as $value) {            $contentnew[]= $value;            unset($value);        }        unset($content4);​​​        // Insert data into database query        $query = mysql_query("INSERT INTO data (div,td,p,b) VALUES ('$content1','$content2','$content3','$content4')");         mysql_query($query) or die (mysql_error());​        // Close the database connection        mysql_close();​​        if (count($contentnew)==0){            $result= $str;        } else {            foreach ($contentnew as $value) {                $str1= getMaxTextBody($value);                $str2= $value->nodeValue;                    //let's say largest body has more than 50% of the text in its parent                                if (strlen($str1)*2<strlen($str2)){                    $str1= $str2;                }                if (strlen($str1)*2>strlen($str)&&strlen($str1)>$maxlen){                    $result= $str1;                } elseif (strlen($str1)>$maxlen){                    $result= $str1;                }                $maxlen= strlen($result);            }        }        $maxlen= strlen($result);        unset($contnentnew);    }}​unset($new);return $result;​}print getMaxTextBody($dom);​​?>​

And below is the MYSQL table I created to store the content

DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
`div`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`td`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`p`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`b`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
PRIMARY KEY (`div`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;
}

JavaScript
 
DROP TABLE IF EXISTS `data`;CREATE TABLE `data` (`div`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,`td`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,`p`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,`b`  varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,PRIMARY KEY (`div`))ENGINE=InnoDBDEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;}​

Any help in figuring out why my content is not saving to the database would be appreciated.

Answer

Your content variables are indeed empty at the point at which you construct the sql query- they were unset above.

$query = mysql_query("INSERT INTO data (div,td,p,b) VALUES 
('$content1','$content2','$content3','$content4')");

JavaScript
 
$query = mysql_query("INSERT INTO data (div,td,p,b) VALUES ('$content1','$content2','$content3','$content4')"); ​

I think you probably want $value’s nodeValue content from each of your $content as $value strings to be placed in that mysql query (you can access the textual content of nodes using $value->nodeValue).

For example, if you want the textual content of a node such as this P node from your example, which looks like this when you print_r the node:

DOMElement Object
(
[tagName] => p
[schemaTypeInfo] =>
[nodeName] => p
[nodeValue] => Students should note that all of the modules below may not 
be available to them.
[nodeType] => 1
[parentNode] => (object value omitted)
[childNodes] => (object value omitted)
[firstChild] => (object value omitted)
[lastChild] => (object value omitted)
[previousSibling] => (object value omitted)
[nextSibling] => (object value omitted)
[attributes] => (object value omitted)
[ownerDocument] => (object value omitted)
[namespaceURI] =>
[prefix] =>
[localName] => p
[baseURI] =>
[textContent] => Students should note that all of the modules below may \
not be available to them.
)

JavaScript
 
DOMElement Object([tagName] => p[schemaTypeInfo] =>[nodeName] => p[nodeValue] => Students should note that all of the modules below may not be available to them.[nodeType] => 1[parentNode] => (object value omitted)[childNodes] => (object value omitted)[firstChild] => (object value omitted)[lastChild] => (object value omitted)[previousSibling] => (object value omitted)[nextSibling] => (object value omitted)[attributes] => (object value omitted)[ownerDocument] => (object value omitted)[namespaceURI] =>[prefix] =>[localName] => p[baseURI] =>[textContent] => Students should note that all of the modules below may \not be available to them.)​

you can see that there are two values in that node that might be interesting to you – textContent and nodeValue.

You can access these from your code by doing this:

  foreach ($content3 as $value) { // content3 contains the p nodes, I think?
           // let's see what the node looks like
          print_r($value); 
          // let's get hold of the text value from the node
          $mytempvariable=$value->nodeValue;
          print "CONTENT OF P NODE: nn$mytempvariablennn";
  }

JavaScript
 
  foreach ($content3 as $value) { // content3 contains the p nodes, I think?           // let's see what the node looks like          print_r($value);           // let's get hold of the text value from the node          $mytempvariable=$value->nodeValue;          print "CONTENT OF P NODE: nn$mytempvariablennn";  }​

This will print out the text from all your P nodes.

Advertisement

Answer