updating multiple records in database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

updating multiple records in database

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Code: Select all

UPDATE my_table SET quantity = quantity-$value WHERE stock_id = '$x';
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Post 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
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post 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
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Post by leewad »

Many Thanks

Got it working fine!
Post Reply