Page 1 of 1

updating multiple records in database

Posted: Mon Nov 08, 2004 7:56 am
by leewad
Hi

I`m trying to build a little stock checking page for my work but having a slight problem updating the database. I need to enter an amount in a text box which then deducts the number from the database as shown in http://www.kwaddingtonroofing.com/stock_admin.php

I have named the text box quantity[1] quantity[2] etc

How can I update the change value into the database

Thanks

Posted: Mon Nov 08, 2004 8:19 am
by kettle_drum

Code: Select all

UPDATE my_table SET quantity = quantity-$value WHERE stock_id = '$x';

Posted: Mon Nov 08, 2004 9:56 am
by leewad
Yeah but what is the '$x' value ? thats what i`m stuck on - I can easliy update a record but how do I make a loop to update each record with the value from each text box ?

Thanks anyway

Posted: Mon Nov 08, 2004 1:12 pm
by swdev
Rather than name your variables quantity[1], quantity[2] etc, replace the 1, 2 with the primary key for the value from the database, such that you now have quantity[pri_key for Ruberoid 1F], quantity[pri_key for Hartington Conway RT66] etc.

Then, where you want to update your database, you go

Code: Select all

foreach ($quantity as $pri_key => $value)
{
  $sql = ' UPDATE ' . $table_name .
            ' SET quantity = quantity - ' . $value .
            ' WHERE stock_id = '' . $key . ''';
  execute $sql
}
I haven't tested this code but hopfully you get the idea

Posted: Tue Nov 09, 2004 2:41 am
by leewad
Many Thanks

Got it working fine!