Mysql query inside a php loop

Question

mysql_query("INSERT INTO fatture_servizi (id, rif_fattura, servizio, quantita, prezzo_unitario, prezzo_servizio, iva) VALUES ('NULL', '$invoice_rif', '{$services_global[$i]['service']}', '{$services_global[$i]['amount']}', '{$services_global[$i]['unit_price']}', '{$services_global[$i]['service_price']}', '{$services_global[$i]['service_vat']}')");

i need to put it inside a php loop like

$c=count($services);
for($i=0;$i<$c;$i++){
mysql_query("INSERT INTO fatture_servizi (id, rif_fattura, servizio, quantita, prezzo_unitario, prezzo_servizio, iva) VALUES ('NULL', '$invoice_rif', '{$services_global[$i]['service']}', '{$services_global[$i]['amount']}', '{$services_global[$i]['unit_price']}', '{$services_global[$i]['service_price']}', '{$services_global[$i]['service_vat']}')");
};

The problem is that it only inserts into the db the first value of the array and not every value.

The $services_global array looks like this but it could change since it comes from some dynamically generated inputs(into the main page you can add as many services as you wish).

Array ( [0] => Array ( [service] => Hostess [amount] => 1 [unit_price] => Eu 120,00 [service_price] => Eu 120,00 [service_vat] => 21 ) [1] => Array ( [service] => Pullman [amount] => 4 [unit_price] => Eu 122,00 [service_price] => Eu 488,00 [service_vat] => 21 ) [2] => Array ( [service] => Cena [amount] => 100 [unit_price] => Eu 100,00 [service_price] => Eu 10.000,00 [service_vat] => 10 ))

Answer 1

I'm guessing that the problem here is that id is your auto-incrementing primary key, and you are trying to assign the static string 'NULL' to it on every iteration.

Try this:

mysql_query("INSERT INTO fatture_servizi (id, rif_fattura, servizio, quantita, prezzo_unitario, prezzo_servizio, iva) VALUES (NULL, '$invoice_rif', '{$services_global[$i]['service']}', '{$services_global[$i]['amount']}', '{$services_global[$i]['unit_price']}', '{$services_global[$i]['service_price']}', '{$services_global[$i]['service_vat']}')");

...or better yet, remove id query all together:

mysql_query("INSERT INTO fatture_servizi (rif_fattura, servizio, quantita, prezzo_unitario, prezzo_servizio, iva) VALUES ('$invoice_rif', '{$services_global[$i]['service']}', '{$services_global[$i]['amount']}', '{$services_global[$i]['unit_price']}', '{$services_global[$i]['service_price']}', '{$services_global[$i]['service_vat']}')");

Also, you would be better using a foreach loop for this (EDITED to help you debug the problem):

foreach ($services_global as $service) {
  $query = "INSERT INTO fatture_servizi
              (rif_fattura, servizio, quantita, prezzo_unitario, prezzo_servizio, iva) 
            VALUES
              ('$invoice_rif', '{$service['service']}', '{$service['amount']}', '{$service['unit_price']}', '{$service['service_price']}', '{$service['service_vat']}')";
  echo "Attempting query: $query\n";
  if (!mysql_query($query)) echo "MySQL Error: ".mysql_error();
}