Data insert problem within foreach loop

Question

The problem is that only some of the XML data is being Inserted into the my mysql database. 10 results are supposed to be entered into the database but it varies between 2 and 8 results. I have no idea why it is doing this and I have tried adding a sleep function to slow the script down, but the data that is inserted into the data base is never as much as when I echo it out on screen. Any help would be much appreciated..

function post_to_db($xml,$cat_id){

    if ($xml->Items->Request->IsValid == 'True'){

        $xml = $xml->Items->Item;

        foreach($xml as $item){
            $asin     = (string)$item->ASIN;
            $title    = (string)$item->ItemAttributes->Title;
            $content  = (string)
                             $item->EditorialReviews->EditorialReview->Content;
            $sku      = (string)$item->ItemAttributes->SKU;
            $brand    = (string)$item->ItemAttributes->Brand; 
            $feature  = (string)$item->ItemAttributes->Feature;
            $model_no = (string)$item->ItemAttributes->Model;
            $review   = (string)$item->ItemLinks->ItemLink[5]->URL;

            $check = "SELECT * FROM `products` WHERE `asin` = '$asin'";
            $checked = mysql_query($check);
            $numrows = mysql_num_rows($checked);

            if ($numrows == 0){ 
                $query         = "INSERT INTO `products`".
                                   "(`cat_id`,`asin`,`sku`,`brand`,".
                                   "`model_no`,`title`,`content`,`feature`) ".
                                 "VALUES".
                                   "('$cat_id','$asin','$sku','$brand',".
                                    "'$model_no','$title',".
                                    "'$content','$feature')";

                $result        = mysql_query($query);
                $post_id       = mysql_insert_id();
                $review_page[] = array($post_id=>$review);  

            }
        }
    }

    return $review_page;    
}

@diEcho Those are MySQL quotes. They need to be there to insert the data using mysql_query. — cwallenpoole
– cwallenpoole, Commented Jul 27, 2011 at 20:17

Dunhamzzz · Accepted Answer · 2011-07-27 20:10:02Z

2

My guess would be some of your variables from XML are creating an invalid query (do they contain quotes?)

Instead of this for each variable:

$asin           = (string)$item->ASIN;

Do this instead:

$asin = mysql_real_escape_string((string)$item->ASIN);

If the problem still persists, change your mysql_query line to this for debugging:

$result = mysql_query($query) or die(mysql_error());

answered Jul 27, 2011 at 20:10

Dunhamzzz

14.8k4 gold badges54 silver badges74 bronze badges

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

1 Comment

user866190 Over a year ago

Thanks, for that - added mysql_real_escape_string and set some of the fields to default null and it now works well

Collectives™ on Stack Overflow

Data insert problem within foreach loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related