a little array INSERT help...?

[glennn.php]

i've been given a table designed thusly:

 

clients_b:

id

client_id

item_id

amount

notes;

 

which stores MANY values (amount, notes) per client...

 

having a form with the fields 'rent', 'elec', 'gas', 'water', and 20 more, the best query i can come up with is

 

$query = "UPDATE client_budgets SET amount = '$rent_amt', notes = '$rent_notes' WHERE client_id = '$cid' AND item_id = '13'";
$query = "UPDATE client_budgets SET amount = '$elec_amt', notes = '$elec_notes' WHERE client_id = '$cid' AND item_id = '14'";
$query = "UPDATE client_budgets SET amount = '$gas_amt', notes = '$gas_notes' WHERE client_id = '$cid' AND item_id = '15'";

 

but i don't want to do that 24 times for each form - i'm hoping someone will  help me get these values into an array that can be inserted (or updated) into this table...

 

or IS IT ok to run 24 queries like that on a submit...? sure doesn't seem to be...

 

thanks much!

 

GN

[Mchl]

If you need to update 24 distinct rows, there's no other way than to run 24 UPDATE queries.

[glennn.php]

so then maybe there's a better way to layout that database...

 

thanks much

[PFMaBiSmAd]

You would probably want to dynamically do this using a loop and perhaps a master array that contains an association between the item_id and the other values (which should be in an array if possible instead of individually named variables.)

[PFMaBiSmAd]

For example -

<?php
// Using HTML arrays $_POST['amount']['rent'], $_POST['amount']['elec'], ... and $_POST['notes']['rent'], $_POST['notes']['elec'], ...
// The master array is used to both generate the form and iterate over the form data. You would also use it in the vaidation logic...
$array = array(); // master array of item_id to index name.
// ... other entries
$array[13] = 'rent';
$array[14] = 'elec';
$array[15] = 'gas';
// ... other entries

// process the form
if(isset($_POST['submit'])){
$cid = 123; // testing testing
foreach($array as $item_id => $index_name){
	$query = "UPDATE client_budgets SET amount = '{$_POST['amount'][$index_name]}', notes = '{$_POST['notes'][$index_name]}' WHERE client_id = '$cid' AND item_id = $item_id";
	echo $query . "<br />";
}
}

// produce and output the form
$content = "<form method= 'post' action=''>\n";
foreach($array as $index_name){
$content .= ucfirst($index_name) . "- Amount: <input type='text' name='amount[$index_name]'>\n";
$content .= "Notes: <input type='text' name='notes[$index_name]'><br />\n";
}
$content .= "<input type='submit' name='submit'>\n";
$content .= "</form>\n";
echo $content;
?>