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

[Brian Chandler]

It's because the foreach construct leaves the status of the last cycle + 1 available after the loop is over. To a naive programmer this can look like another entry is available. This is a VERY common problem.

I still can't parse "leaves the status of the last cycle + 1 available"... but I think you mean that after falling out of the loop, $value still has the last value from the array (and I suppose $key is still the index of the last value). Well, PHP is a classic of bad language design, and doesn't do variable scope properly at all. But unless you habitually refer to $value after it ought to have ceased to have any meaning (and I can't imagine any case where you would want to do this), I still can't see what the "Problem" is. In particular I can't imagine what you need to do to "take care of the overshoot". Can you give an example?

The problems with the OP's stuff seem to be more basic. What on earth is the point of the first bit (" Define Variables ") -- it actually just sets each of the variables to the string being its own name; later they get set to something meaningful, so this part could be deleted for a start. ... Oh, no, wait!

Instead of writing

CODE

$description = $entry->'description';

you write

CODE

$description = 'description';
$description = $entry->$description;

Wow! That is convoluted...

Incidentally, this is exactly where you should use a foreach. Define an array fields of the various fields in the structure:

CODE

$fields = array('
    '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');

// Now to do anything to every field...
// e.g. what the above achieves of copying every member out of the structure

foreach ($fields as $fld)
  $$fld = $entry -> $fld;

HTH

[Ephraim F. Moya]

CODE

$fields = array('
    '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');

// Now to do anything to every field...
// e.g. what the above achieves of copying every member out of the structure

foreach ($fields as $fld)
  $$fld = $entry -> $fld;

HTH

This is in error!
1) there's an extra apostrophe in the definition.
2) This describes a single entry in a larger array.
3) Even though you don't use the key, there is still one. It starts at 0 and extends to the end of the array of arrays.

CODE

[0]---|--->'advertiser_id'
      |--->'more fields'
      |--->'upc'

more entries

[49]---|--->'advertiser_id'
       |--->'more fields'
       |--->'upc'

What Brian has described is one entry. He also forgot to put in the value. Each entry should look like this:

$fields = array(
'advertiser_id' => $idNumber,
'buy_url' => $url,
etc.,
);

So if your array is named $products then each element can be referenced by:

$upc = $products[key]['upc'];



This post has been edited by Ephraim F. Moya: Aug 13 2012, 10:36 PM