2 
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 ))
Improve this question
4
  • 1
    Is id a primary key or has any other constraint? Commented Jan 5, 2012 at 15:49
  • 1
    What does the $services_global array look like? Also, mysql_* functions will soon be deprecated. Please look into using mysqli_* functions or the PDO MySQL library. Commented Jan 5, 2012 at 15:49
  • 1
    Why don't you use foreach() instead of for() Commented Jan 5, 2012 at 15:51
  • 1
    Are you sure you want NULL between single quotes, 'NULL'? Commented Jan 5, 2012 at 15:51

4 Answers 4

Reset to default
5

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();
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I tried removing the id(that is the primary key) but it keeps adding to the database only the first value of the array.(in the above case $services_global[0])
@g0dl3ss Try with my edited code above which should hopefully tell you why the query is failing.
This helped a lot thanks(the problem was that i did set rif_fattura as a unique key while it should not be unique since every row of the query loop has the has the same value for that variable. Thanks a lot
2

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!

Improve this answer

2 Comments

Are you sure you want NULL between single quotes, 'NULL'?
@Konerak Sorry, I didn't look at that part. I edited my answer.
0

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!

Improve this answer

Comments

0

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!

Improve this answer

2 Comments

Yep id is auto incrementing, i removed the id value from the query but still only 1 row gets to the db.
Dump the resulting query to the variable and look at it. And escape the special characters from your input.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.