Scrape an HTML table and INSERT row data into a database table

Question

I have a result generated using SimpleHtmlDom.

include('simple_html_dom.php');
$html = file_get_html('http://www.example.com');

$count =count($html->find('table tbody tr td')) -

1; //$count =170

for ($i = 1; $i < $count; $i++) {
    echo $val[$i] = $html->find('table tbody tr td', $i)->plaintext;
}

I don't want the first and last output, so used $x = 1 and $count - 1.

The result of output is

enter image description here

here, the Stock Symbol is same for every row, and there is a loop after every 8th array value.

my mysql table is records with columns "id,date,stock_symbol,buyer,seller,quantity,rate,amount"

"INSERT INTO records 
(id,date,stock_symbol,buyer,seller,quantity,rate,amount) 
VALUE ('$val1','$val2','$val3','$val4','$val5','$val6','$val7','$val8')",
('$val9','$val10','$val11','$val12','$val13','$val14','$val15','$val16')"

I could have done it manually if it was less in number, but it is more than couple of hundreds.

I tried

$values = array('English', 'Arabic');
$statement = 'INSERT INTO contact (`language1`, `language2`) VALUES ("' . implode('", "', $values) . '")';
echo $statement;

from How insert the array value one by one to MySQL table in different column, but my array doesn't have comma (,) and didn't get any result

EDIT: edited after the ans

include('inc/simple_html_dom.php');
include('mysql.php');
// get DOM from URL or file

header('Content-Type: text/html; charset=utf-8');
// get DOM from URL or file
$html = file_get_html('http://www.seismonepal.gov.np/index.php?action=earthquakes&show=recent');

// remove all image
foreach($html->find('div [class=block2-content] span') as $e)
$array=  str_replace(' ', '', $e->plaintext);

//split all you data into the size chunks you want.
//$array = array('English', 'French', 'Dutch', 'English', 'French', 'Dutch', 'English', 'French', 'Dutch', 'English', 'French', 'Dutch', 'English', 'French');
$chunks = array_chunk($array, 7);

//get the amount of chunks you have.
$c = count($chunks);
$a = 0;

//then run through each chunk entering it into the database.

do{
    $values = null;
    $x = 1;

    foreach ($chunks[$a] as $value) {
        $values .= "?";
        if ($x < count($chunks[$a])) {
            $values .= ', ';
        }

        $x++;
    }

    $sql = "INSERT INTO earthquake(id,date_np,local_time,lattitude,longitude,magnitude,epic_center,remarks) VALUES ({$values})";    
    $query = $db->prepare($sql);

    if (count($chunks[$a])) {
        $y = 1;
        foreach ($chunks[$a] as $param) {
            $query->bindValue($y, $param);
            $y++;
        }    
    }

    if ($query->execute()) {
        echo "Done {$a}" . "<br/>";
    } else {
        echo "Not Done {$a}" . "<br/>";
    }
    $a++;  

} while ($a < $c);

akaBase · Accepted Answer · 2015-05-28 14:04:31Z

1

This will make multiple querys but this is a way you could do it.

Split all you data into the size chunks you want.

$array = array('English', 'French', 'Dutch', 'English', 'French', 'Dutch', 'English', 'French', 'Dutch', 'English', 'French', 'Dutch', 'English', 'French');
$chunks = array_chunk($array, 2);

get the amount of chunks you have.

$c = count($chunks);
$a = 0;

then run through each chunk entering it into the database.

do{
    $values = null;
    $x = 1;

    foreach($chunks[$a] as $value) {
        $values .= "?";
        if($x < count($chunks[$a])) {
            $values .= ', ';
        }

        $x++;
    }

    $sql = "INSERT INTO `table` (`language1`, `language2`) VALUES ({$values})";    
    $query = $db->prepare($sql);

    if(count($chunks[$a])){
        $y = 1;
        foreach($chunks[$a] as $param) {
            $query->bindValue($y, $param);
            $y++;
        }    
    }


    if($query->execute()){
        echo "Done {$a}" . "<br/>";
    }else{
        echo "Not Done {$a}" . "<br/>";
    }
    $a++;  

}while($a < $c);

Hope this helps.

EDIT For follow up question.

You can put each of them into an array as you find them like this.

$array = array();

foreach($html->find('div [class=block2-content] span') as $e){
    array_push($array, $e->plaintext);
}

Which you can then use array_chunk on.

edited May 28, 2015 at 14:04

answered May 25, 2015 at 2:23

akaBase

2,2501 gold badge20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aarvee Thapa Over a year ago

Hello Parody, Thanks for the ans, I have edited the questions and included the code, but still gives error, array_chunk() expects parameter 1 to be array,

akaBase Over a year ago

@AarveeThapa i've edited my post to include an answer to your follow up question.

Aarvee Thapa Over a year ago

Thanks again for the help.. but I am new to "prepare" function, searched over internet and tried new connection strings to mysql and database, still failed. I am getting such error, "Call to a member function prepare() on a non-object". Can you please provide a little more help here. Thanks in advance.

Aarvee Thapa Over a year ago

I got it, $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');.. thanks a lot once again

Collectives™ on Stack Overflow

Scrape an HTML table and INSERT row data into a database table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related