CODEWITHSUNDEEP
phpmysqlarrayscycle
g0dl3ss
g0dl3ss

Reputation: 393

Mysql query inside a php loop

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 ))

Upvotes: 2

Views: 2399

Answers (4)

DaveRandom
DaveRandom

Reputation: 88697

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();
}

Upvotes: 5

Minras
Minras

Reputation: 4356

I'll try to guess that id is your primary key and you are trying to insert all your records with the same id = 'NULL'. Usually primary key is integer so it is strange that you don't get the exception instead of data insertion. Most common solution is to have integer auto incremental primary key, then you don't have to worry about inserting it:

SQL:

CREATE TABLE `fatture_servizi` (
  `id` int(11) NOT NULL auto_increment,
  /* other columns */
);

PHP:

$c=count($services);
for($i=0;$i<$c;$i++){
    $sql = "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']}')";
    mysql_query($sql);
};

Besides, INSERT is rather heavy operation. If it is possible, I'd use bulk inserts:

$c=count($services);
$rows = array();
for($i=0;$i<$c;$i++){
    $rows[] = "('$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']}')";
}
if(!empty($rows)) {
    $sql = "INSERT INTO fatture_servizi (rif_fattura, servizio, quantita, prezzo_unitario, prezzo_servizio, iva) VALUES " . implode(',', $rows) . ";";
    mysql_query($sql);
}

And don't forget about escaping the data you pass to the database!

Upvotes: 0

Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

Why are you using $services_global in the VALUES() and using count($services)... Are they the same? This could be your error...

Also note, a good strategy for looping (its called a loop, not a cycle), you can use FOREACH...

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

I hope thats it!

Upvotes: 0

Remy Strijker
Remy Strijker

Reputation: 261

I think it is easier to use a foreach:

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

I hope this will fix your problem!

Upvotes: 2

Related Questions