Updating Database Records

[TymArtist]

Hey guys,

Quick one for ya. I'm trying to write some code that pulls a column of data from a MySQL database into form fields. The form fields can be changed, and then upon submit, the form will post to itself and run an update query to modify the database.

I've successfully pulled the records from that column into a form, but upon submit it does not change the records. Please take a look at my code if you have a moment, as I'm sure that I'm missing just one or two things in the Update query. Thanks!

The update query on top of the page:

<?php
if (isset($_POST['database'])) {
db_connect();
$query = "UPDATE rates SET program=" . $_POST['value'] "where program=$row[0]";
$result = @mysql_query ($query);
?>

The Form in the body of the page:

  <form name="updaterecord" id="updaterecord" method="post" action="">
<?php
	db_connect();
	$query = "SELECT program FROM rates";
	$result = @mysql_query($query);
	$num = mysql_num_rows($result);
	$i = 0;

if ($num > 1) {
while ($row = mysql_fetch_array($result, MYSQL_NUM)) { 
$newid = "program" . $i;
echo "<input name='$newid' id='$newid' value='$row[0]' /><br />";
$i++;
}
mysql_free_result ($result);
}
else { ?>
<p>Sorry, no rates could be found. Please try again later. </p>
<p> <? mysql_error(); ?> </p> <?
}
mysql_close();?>
<input type="submit" name="database" id="database" value="Update" />
</form>

[Shrike]

Your insert query seems a bit wrong. Your using values which don't exist, like $POST['value'] and $row[0]. Add a line print_r( $POST ) before the insert to see what your submitting in the form.

[TymArtist]

Here's the output I get from the printf:

Array ( [program0] => Fixed - 30 Year [program1] => Fixed - 30 Year [program2] => Fixed - 15 Year [program3] => Fixed - 10 Year [program4] => Fixed - Jumbo - 30 Year [program5] => Fixed - Jumbo - 15 Year [program6] => Ajd - 1 Year - Treasury Index [program7] => Adj - 3/1 Year [program8] => Adj - 5/1 Year [program9] => Adj - 7/1 Year [program10] => Adj - 10/1 Year [program11] => Adj - Jumbo - 1 Year [program12] => Adj - Jumbo - 3/1 Year [program13] => Adj - Jumbo - 5/1 Year [program14] => Ajd - Jumbo - 7/1 Year [program15] => Adj - Jumbo - 10/1 Year [database] => Update )

Since my understanding of Update Queries is limited, is there a method I can use (such as a for=>each loop) to update these 16 rows of a single table column? Honestly the problem I'm running into is that I'm not sure what variables I'm supposed to use, so if anyone could assist in that your help would be much appreciated 🙂

[TymArtist]

Ok I rewrote this a bit and almost have it. The only problem is that it's pulling the name or value from the Submit button ("Update" or "Database") instead of the correct Array value from $program. Any ideas?

Form:

  <form name="updaterecord" id="updaterecord" method="post" action="">
<?php
	db_connect();
	$query = "SELECT program FROM rates";
	$result = @mysql_query($query);
	$num = mysql_num_rows($result);

if ($num > 1) {
$i = 0;
while ($row = mysql_fetch_array($result, MYSQL_NUM)) { 
$program[$i] = "$row[0]";
$i++;
}
foreach($program as $k => $v) {
$name = "program" . $k;
echo "<p>$k, $v<br /><input name=\"$name\" id=\"$name\" value=\"$v\" /></p>";
}
}
else { ?>
<p>Sorry, no rates could be found. Please try again later. </p>
<p> <? mysql_error(); ?> </p> <?
}
mysql_close();?>
<input type="submit" name="database" id="database" value="Update" />
</form>

update query

if (isset($_POST['database'])) {
db_connect();
foreach($_POST as $k => $v) {
	$updatestring = "UPDATE rates SET program='$v'";
	$update = @mysql_query($updatestring);
	 }
}