[SOLVED] Update ?

[ainoy31]

Hello-

 

I have a little problem updating a record in my table.  Here is the structure of my table.

CREATE TABLE commodity
(
com_id serial NOT NULL,
quote_id integer NOT NULL,
com_name varchar(50) NOT NULL,
htsus varchar(50) NOT NULL,
eccn varchar(50) NULL

The quote_id is a foreign key of another table.

So right now I have a record with quote_id of 71 with two com_id's of 104 and 105 respectively.  So com_id 104 has com_name of Shoes with htsus of 1252.25.3652 and com_id 105 has com_name of Dresses with htsus of 1524.25.3652.  I am trying to have a page where someone can come in and edit these records.  Here is the form code to do so:

<table>
<tr>
<td>Commodity and HTSUS #:</td>
</tr>
<tr>
<td><? $sql = "SELECT com_name, htsus FROM commodity WHERE quote_id = '$id'";
$res = pg_query($sql);
while($c = pg_fetch_assoc($res))
{
?>
<input type="text" name="com[]" value="<? echo $c[com_name]; ?>" size="20" >
<input type="text" name="htsus[]" value="<? echo $c[htsus]; ?>" size="20" /><br>
<?	 
} 
?>
</td>
</tr>

 

 

Then I request the posted data and update the record as follows:

$com = $_REQUEST["com"];
$ht = $_REQUEST["htsus"];
foreach($com as $key => $var)
{
pg_query("UPDATE commodity SET com_name = '$var', htsus = '$ht[$key]' WHERE quote_id = '$id'") or die("Error in Query: " . pg_last_error());
}

 

The problem is that I have a duplicate record of whatever I enter first.  So if I have quote id if 54 with three com_id, all the com_id will have the same info.  I hope this is clear enough of what I am trying to do.  Much appreciation.  AM

[btherl]

Since com_id is unique, one solution is to attach com_id to each record in the form, and update by that instead of quote_id.  You can add another hidden field like this:

 

<td><? $sql = "SELECT com_id, com_name, htsus FROM commodity WHERE quote_id = '$id'";
$res = pg_query($sql);
while($c = pg_fetch_assoc($res))
{
?>
<input type="hidden" name="com_id[]" value="<? echo $c['com_id']; ?>">
<input type="text" name="com[]" value="<? echo $c[com_name]; ?>" size="20" >
<input type="text" name="htsus[]" value="<? echo $c[htsus]; ?>" size="20" /><br>
<?	 
} 

 

Then a corresponding change to your update code:

 

$com_id = $_REQUEST['com_id'];
$com = $_REQUEST["com"];
$ht = $_REQUEST["htsus"];
foreach($com as $key => $var)
{
pg_query("UPDATE commodity SET com_name = '$var', htsus = '{$ht[$key]}' WHERE com_id = '{$com_id[$key]}'") or die("Error in Query: " . pg_last_error());
}

 

I also added {} protection around the arrays used inside the string passed to pg_query().  While it's not always necessary, it does make things clearer and more robust.

[ainoy31]

Thanks for the suggestion and will try it out...

[ainoy31]

Solution given works.  Thanks.