Search code examples
phpmysqlfor-loopscreen-scraping

php mysql queries aren't updating database in for-loop

I'm screen scraping a page with data I want to write in a mysql database. 

$url = "http://example.com/index.jsp";

$raw = file_get_contents($url);

$newlines = array("\t","\n","\r","\x20\x20","\0","\x0B");

$content = str_replace($newlines, "", $raw);

$start = strpos($content,'<table border="0" cellspacing="0" cellpadding="0" width="100%">"');

$end = strpos($content,'</table>',$start) + 8;

$table = substr($content,$start,$end-$start);

The Data processing works well, all the html tags, whitespaces, and html special characters such as   etc. have been removed from the data. The values are looking fine when echoed in the processing page.

function cleanSiteVal($siteval){
    $repl = array('value=', '"');
    $siteval = strip_tags($siteval);
    $siteval = str_replace($repl, '', $siteval);
    $siteval = html_entity_decode($siteval);

    return $siteval;
}

foreach ($rows[0] as $row){

    if ((strpos($row,'<td align')==true)){
                //echo $row;
                preg_match_all( '@value="([^"]*)"@', $row, $cells ) ;             
        $fln = cleanSiteVal($cells[0][0]);
        $flf = cleanSiteVal($cells[0][1]);
        $sch = cleanSiteVal($cells[0][2]);
        $est = cleanSiteVal($cells[0][3]);
        $trm = cleanSiteVal($cells[0][4]);
        $sts = cleanSiteVal($cells[0][5]);
        echo 'flnr: '.$fln.', from: '.$flf.', scheduled: '.$sch.', estimated: '.$est.', terminal: '.$trm.', status: '.$sts.'<br />';


        $fliarr[] = array(
                    0 => $fln,
                    1 => $flf,
                    2 => $sch,
                    3 => $est,
                    4 => $trm,
                    5 => $sts 
                    );
    }
}

I 'echo' the query i'm constructing out of this processed data and this queries executed within phpmyadmin are working fine, although the queries i'm executing within my for loop are not. 

for ($i = 0; $i < count($fliarr); $i++) {

    $nrfli = $fliarr[$i][0];

    $stat = $fliarr[$i][5];
    $term = $fliarr[$i][4];
    if ($fliarr[$i][3]!='' || !empty($fliarr[$i][3])) { $abr = $today.' '.$fliarr[$i][3].':00'; } else { $abr = $today.' '.$fliarr[$i][2].':00'; }

    //echo 'estimated/sched. time: '.$abr.', flugnr: '.$fliarr[$i][0].', status: '.$stat.'<br />';

    $sql = "UPDATE `some_table` SET `val1`='$stat', `val2`='$term', `val3`='$abr' WHERE (`datetime_field` BETWEEN '$sfrom' AND '$till') AND `val4`='$nrfli'";
    echo $sql.'<br />';
    $res = mysql_query($sql);

    if(!$res) {
        echo mysql_error().' Fehler bei der Update Abfrage';
    } else {
        //echo 'Ok, Update Buchungen'.$nrfli.'<br />';
    }
}

How do I manage this queries to work in my for loop? I know, this is bad practice and I also tried to do with a PDO layer and prepare my queries, but that didn't work either.

Again, the copied queries from the echo in the for loop are working in phpmyadmin. Why not in my for loop?

Solution

  • it turned out to be a problem with the & n b s p ; whitespace. html_entity_decode didn't get rid of it because of this explanation:

    You might wonder why trim(html_entity_decode(' ')); doesn't reduce the string to an empty string, that's because the '& n b s p ;' entity is not ASCII code 32 (which is stripped by trim()) but ASCII code 160 (0xa0) in the default ISO 8859-1 encoding.

    source: php docs htmlentitydecode

    Here is what I did to find out:

    1. I mada a full html page with doctype declaration and charset meta tag.
    2. I chose mysql_encoding to utf-8
    3. I echoed the queries in valid html paragraphs This showed me that they were odd characters within the spaces of the values for the query
    4. I added return utf8_encode($siteval); in cleanSiteVal function
    5. I added $siteval = str_replace('&nbsp;', ' ', $siteval);

    All these steps finally cleaned up the values coming form the scraped site and got the queries in my loop fired. Hurray!!!! This was a big lesson learned for me. I will always set mysql_encoding for my connections, that saves a lot of utf8_encode around echoed values in dynamically generated html code. And it shows the html & n b s p ; whitespaces left in the data.

    So finally here is the full cleanSiteVal() function:

       function cleanSiteVal($siteval){
    $repl = array('value=', '"', '&nbsp;');
    $siteval = strip_tags($siteval);
    $siteval = str_replace($repl, '', $siteval);
    $siteval = html_entity_decode($siteval);
    $siteval = str_replace('&auml;', 'ä', $siteval);
    $siteval = mysql_real_escape_string($siteval);
    return utf8_encode($siteval);
}