Can I update rows in sql based on a table generated in PHP?

Question

I apologize if this question isn't asked well - first post on here. Very new with PHP and SQL in general.

I have a table displayed using a PHP foreach loop.

query:

SELECT * FROM ftu_inventory WHERE issuedto='$_GET[id]' ORDER BY item ASC

(using id 134 as example - partid is a unique primary key auto-assigned when the line is inserted)

partid	item	issuedto	status
40	Beret	134	On Hand
16	Boots	134	On Hand
72	Jacket	134	On Hand
250	Jacket	134	On Hand
103	Pants	134	On Hand
240	Pants	134	On Hand

'Status' is displayed using a drop-down menu. I would like to have a button at the top of the badge to save all displayed rows with the new drop-down values in that column.

I know I could have a save button included in each row as part of the foreach loop, but there has to be a more effective way, no?

Please post the (HTML) form code as well. In short you could name the `select` as ` — DarkBee, Oct 04 '22 at 14:42
You are open to [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) by the way — DarkBee, Oct 04 '22 at 14:43
Use `` fields and a useful structure for [nested arrays in form data](https://www.php.net/manual/en/language.variables.external.php) to submit row ids and new values in one go… — deceze, Oct 04 '22 at 14:46

score 0 · Answer 1 · answered Oct 04 '22 at 14:48

If status will be updated to the same value, e.g. status = "On Hand", then you can do:

UPDATE ftu_inventory SET status = :newStatus WHERE id IN (...)

But if each item will update to it's own status, then you can group by new status, and then do fewer updates than just iterate through all:

   ...
   $statuses = array_column($items, null, 'status'); // Index by "status" array key

   foreach ($statuses as $status => $itemsBatch) {
       $this->update($status, array_column($itemsBatch, 'id'));
   }
   ...
}

public function update(string $status, array $ids): void
{
    $this->sql('UPDATE ftu_inventory SET status = :newStatus WHERE id IN :ids', [':newStatus' => $status, ':ids' => $ids]);
}

Code is pseudo-code and contains lots of missing actual code

Can I update rows in sql based on a table generated in PHP?

1 Answers1