Insert Multiple Rows into MySQL Database with PHP, Why does it only insert one row? Options

[Dante Monaldo]

I have some code that takes 50 products from an API catalog and should be adding them to a database on my site. However, when I run the code, 50 rows are created, but only one product is added. All the other rows are left blank.

Has anybody had this problem before? What am I missing?

CODE

$insert = mysql_query("INSERT INTO imported_products (advertiser_id, buy_url, catalog_id, currency, description, image_url, in_stock, isbn, manufacturer_name, manufacturer_sku, name, price, retail_price, sale_price, sku, upc)
                    VALUES('$advertiser_id', '$buy_url', '$catalog_id', '$currency', '$description', '$image_url', '$in_stock', '$isbn', '$manufacturer_name', '$manufacturer_sku', '$name', '$price', '$retail_price', '$sale_price', '$sku', '$upc')");
    if(!$insert){
        $update = mysql_query("UPDATE imported_products SET advertiser_id='$advertiser_id', buy_url='$buy_url', catalog_id='$catalog_id', currency='$currency', description='$description', image_url='$image_url', in_stock='$in_stock', isbn='$isbn', manufacturer_name='$manufacturer_name', manufacturer_sku='$manufacturer_sku', name='$name', price='$price', retail_price='$retail_price', sale_price='$sale_price', upc='$upc' WHERE sku='$sku'") or die (mysql_error());
        if(!$update){
            echo 'Could not update product. SKU: '.$sku.'';
        } else {
            echo 'UPDATE Mission Accomplished.';
        }
    } else {
        echo 'INSERT INTO Mission Accomplished.';
    }
}

I would really appreciate some help on this

[Dante Monaldo]

Yes, I think I hear what you are saying. I'm just confused how I should be using the $key => $value when there's multiple values that are related to each product (i.e. name, description, price, etc.)

You've been requesting the full code. Not sure how much help it's going to be, but here it is.

CODE

<?php
$webid="*******";
$CJ_ID="*******";
$url="https://product-search.api.cj.com/v2/product-search?website-id=$webid&advertiser-ids=joined";

// Define Variables
$advertiser_id = 'advertiser-id';
$buy_url = 'buy-url';
$catalog_id = 'catalog-id';
$currency = 'currency';
$description = 'description';
$image_url = 'image-url';
$in_stock = 'in-stock';
$isbn = 'isbn';
$manufacturer_name = 'manufacturer-name';
$manufacturer_sku = 'manufacturer-sku';
$name = 'name';
$price = 'price';
$retail_price = 'retail-price';
$sale_price = 'sale-price';
$sku = 'sku';
$upc = 'upc';

// Verify Headers
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_POST, FAlSE);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: '.$CJ_ID));
$xml = curl_exec($ch);
curl_close($ch);

function produce_XML_object_tree($raw_XML) {
    libxml_use_internal_errors(true);
    try {
        $xmlTree = new SimpleXMLElement($raw_XML);
    }
    catch (Exception $e) {
        $error_message = 'SimpleXMLElement threw an exception.';
        foreach(libxml_get_errors() as $error_line) {
            $error_message .= "\t" . $error_line->message;
            }
        trigger_error($error_message);
        return false;
    }
return $xmlTree;
}
$feed = produce_XML_object_tree($xml);

foreach ($feed->products as $entry) {
    $advertiser_id = $entry->$advertiser_id;
    $buy_url = $entry->$buy_url;
    $catalog_id = $entry->$catalog_id;
    $currency = $entry->$currency;
    $description = $entry->$description;
    $image_url = $entry->$image_url;
    $in_stock = $entry->$in_stock;
    $isbn = $entry->$isbn;
    $manufacturer_name = $entry->$manufacturer_name;
    $manufacturer_sku = $entry->$manufacturer_sku;
    $name = $entry->$name;
    $price = $entry->$price;
    $retail_price = $entry->$retail_price;
    $sale_price = $entry->$sale_price;
    $sku = $entry->$sku;
    $upc = $entry->$upc;

$insert = mysql_query("INSERT INTO imported_products (advertiser_id, buy_url, catalog_id, currency, description, image_url, in_stock, isbn, manufacturer_name, manufacturer_sku, name, price, retail_price, sale_price, sku, upc)
                    VALUES('$advertiser_id', '$buy_url', '$catalog_id', '$currency', '$description', '$image_url', '$in_stock', '$isbn', '$manufacturer_name', '$manufacturer_sku', '$name', '$price', '$retail_price', '$sale_price', '$sku', '$upc')");
    if(!$insert){
        $update = mysql_query("UPDATE imported_products SET advertiser_id='$advertiser_id', buy_url='$buy_url', catalog_id='$catalog_id', currency='$currency', description='$description', image_url='$image_url', in_stock='$in_stock', isbn='$isbn', manufacturer_name='$manufacturer_name', manufacturer_sku='$manufacturer_sku', name='$name', price='$price', retail_price='$retail_price', sale_price='$sale_price', upc='$upc' WHERE sku='$sku'") or die (mysql_error());
        if(!$update){
            echo 'Could not update product. SKU: '.$sku.'';
        } else {
            echo 'UPDATE Mission Accomplished.';
        }
    } else {
        echo 'INSERT INTO Mission Accomplished.';
    }
}
?>

[Ephraim F. Moya]

CODE

This is what your array should look like:

$key[first] ----- $value['id']
            |---- $value['firstParam']
            |---- $value['...']
            |---- $value['lastParam']
  .
  .
  .
$key[last] ----- $value['id']
           |---- $value['firstParam']
           |---- $value['...']
           |---- $value['lastParam']

I usually use a special field in the array as the key. Mostly I use numeric ids because it is easier for the computer to distinguish the keys that way. Often there are missing keys in the id field. You code MUST be able to handle these. Also, a foreach construct will 'overshoot' by one field so you must take care of that, too.

This post has been edited by Ephraim F. Moya: Aug 11 2012, 02:34 PM