Insert Every Result of Foreach Loop into MySQL

Question

Thanks for reading. I'm trying to parse an array of urls from Amazon, gather 4 variables ($name, $price, $stars, $reviews) and store the value of each of these into MySQL for every iteration in the Foreach Loop

The problem that I'm having is that only the information from the first url is being stored in MySQL (as you can see from the array I have 4 sites I'm trying to test this on.

I know that the code to get the correct values of the variables and such is correct because again this works for the first site, just doesn't go through the rest and I can't figure out why.

Any help would be greatly appreciated. Thanks!

function getSQL()
{
    include('simple_html_dom.php');

    $urlArray = array("http://www.amazon.com/kindle-fire-hdx-student-gaming-tablet/dp/B00BWYQ9YE/ref=sr_1_1?ie=UTF8&qid=1403276865&sr=8-1&keywords=kindle+fire+hdx", "http://www.amazon.com/Kindle-Fire-HDX-Display-Wi-Fi/dp/B00CUTT4HY/ref=sr_1_2?ie=UTF8&qid=1403276882&sr=8-2&keywords=kindle+fire+hdx", 
                "http://www.amazon.com/Kindle-Fire-HDX-Display-Wi-Fi/dp/B00BWYRF7E/ref=sr_1_3?ie=UTF8&qid=1403276882&sr=8-3&keywords=kindle+fire+hdx", "http://www.amazon.com/kindle-fire-hdx-best-movie-tablet-8.9/dp/B00BHJRYYS/ref=sr_1_5?ie=UTF8&qid=1403276882&sr=8-5&keywords=kindle+fire+hdx");

    foreach ($testArray as $url) 
    {

        $html = file_get_html("$url");
        $name = $html->find('h1[class="parseasinTitle"]', 0)->plaintext; //line 6455
        $price = $html->find('b[class=priceLarge]', 0)->plaintext; //line 6650

        $string = $html->plaintext;

        $wordToFind = 'stars';
        $numWordsToWrap = 4;

        $words = preg_split('/\s+/', $string);
        if (($pos = array_search($wordToFind, $words)) !== FALSE) {
        $start = ($pos - $numWordsToWrap > 0) ? $pos - $numWordsToWrap : 0;
        $length = (($pos + ($numWordsToWrap + 1) < count($words)) ? $pos + 1 : count($words) - 1) - $start;
        $slice = array_slice($words, $start, $length);
        $stars = implode(' ', $slice);
        } else echo 'I didn\'t find it'; 


        $wordToFind2 = 'reviews';
        $numWordsToWrap2 = 4;

        $words2 = preg_split('/\s+/', $string);
        if (($pos2 = array_search($wordToFind2, $words2)) !== FALSE) {
        $start2 = ($pos2 - $numWordsToWrap2 > 0) ? $pos2 + 1 : 0;
        $length2 = (($pos2 + ($numWordsToWrap2 + 1) < count($words2)) ? $pos2 + ($numWordsToWrap2 + 1) : count($words2) - 1) - $start2;
        $slice2 = array_slice($words2, $start2, $length2);
        $reviews = implode(' ', $slice2);
        $reviews = str_replace("&nbsp;", "", $reviews);
        } else echo 'I didn\'t find it';


        $amazon_all_sql = "

        insert into kindlefire
        values('$name', '$price', '$stars', '$reviews');
        ";

        return $amazon_all_sql; 
    }       
}

Your return statement is inside your foreach loop, so the function will return during the first iteration and never process the remaining entries. — user1864610
– user1864610, Commented Jun 23, 2014 at 20:15
You can change it to generator -> change foreach to yield and use it like foreach(getSql() as $sql) — Tomáš Blatný
– Tomáš Blatný, Commented Jun 23, 2014 at 20:17
I hope you have used proper SQL escaping to avoid severe SQL injection bugs because this looks terrifyingly insecure. — tadman
– tadman, Commented Jun 23, 2014 at 21:40

Chanicho · Accepted Answer · 2014-06-23 20:29:23Z

1

You need to replace the following line

return $amazon_all_sql;

The foreach loop does the 1st round and then encounters the return statement. This terminates all processing in your function. Remove it and replace it with a statement that executes your sql.

If you just want to see all of the SQL that gets generated, change the following line and then pull the return outside of the foreach loop. (Note the .= operator used to append all the SQL to the $amazon_all_sql variable)

   $amazon_all_sql .= "

    insert into kindlefire
    values('$name', '$price', '$stars', '$reviews');
    ";

}    

    return $amazon_all_sql;

answered Jun 23, 2014 at 20:29

Chanicho

3541 silver badge5 bronze badges

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

Comments

VikingBlooded · Accepted Answer · 2014-06-23 20:29:48Z

Fix your return and your array name.

 function getSQL()
    {
        include('simple_html_dom.php');

        $urlArray = array("http://rads.stackoverflow.com/amzn/click/B00BWYQ9YE", "http://rads.stackoverflow.com/amzn/click/B00CUTT4HY", 
                    "http://rads.stackoverflow.com/amzn/click/B00BWYRF7E", "http://rads.stackoverflow.com/amzn/click/B00BHJRYYS");

        foreach ($urlArray as $url) 
        {

            $html = file_get_html("$url");
            $name = $html->find('h1[class="parseasinTitle"]', 0)->plaintext; //line 6455
            $price = $html->find('b[class=priceLarge]', 0)->plaintext; //line 6650

            $string = $html->plaintext;

            $wordToFind = 'stars';
            $numWordsToWrap = 4;

            $words = preg_split('/\s+/', $string);
            if (($pos = array_search($wordToFind, $words)) !== FALSE) {
            $start = ($pos - $numWordsToWrap > 0) ? $pos - $numWordsToWrap : 0;
            $length = (($pos + ($numWordsToWrap + 1) < count($words)) ? $pos + 1 : count($words) - 1) - $start;
            $slice = array_slice($words, $start, $length);
            $stars = implode(' ', $slice);
            } else echo 'I didn\'t find it'; 


            $wordToFind2 = 'reviews';
            $numWordsToWrap2 = 4;

            $words2 = preg_split('/\s+/', $string);
            if (($pos2 = array_search($wordToFind2, $words2)) !== FALSE) {
            $start2 = ($pos2 - $numWordsToWrap2 > 0) ? $pos2 + 1 : 0;
            $length2 = (($pos2 + ($numWordsToWrap2 + 1) < count($words2)) ? $pos2 + ($numWordsToWrap2 + 1) : count($words2) - 1) - $start2;
            $slice2 = array_slice($words2, $start2, $length2);
            $reviews = implode(' ', $slice2);
            $reviews = str_replace("&nbsp;", "", $reviews);
            } else echo 'I didn\'t find it';


            $amazon_all_sql = "

            insert into kindlefire
            values('$name', '$price', '$stars', '$reviews');
            ";


        }  
    return $amazon_all_sql;     
    }

thanks for this it was the return statement being in the loop - rookie mistake, but fixed and works great!

Collectives™ on Stack Overflow

Insert Every Result of Foreach Loop into MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related