Simple Multiple Update Query from HTML textboxes

[mushbucket]

I know this has been asked plenty, and I have tried many things but I can't get anything to work and codes that do seem to work look ridiculously large for the purpose I need them for.

What I basically need is something that gets all the rows from a database and puts the information into individual text boxes, then just the one button at the bottom to update all the text boxes back into their appropriate rows.

My table structure is literally just ID and Name.

Cheers,

Robbie.

[Will Gresham]

Something like this would echo textboxes with the database values:

<form action="pagename.php" method="post">
<?
$sql_query = mysql_query("PUT THE SELECT QUERY HERE")or die(mysql_error());
$sql_results = mysql_fetch_assoc( $sql_query )or die(mysql_error());
foreach( $sql_results as $key => $value) {
  echo "<input type=\"text\" value=\"$value\" name=\"$key\" /> ";
}
?>
</form>

Then to put it back in the database something like:

<?
$sql_query = "UPDATE table_name SET";
foreach( $_POST as $key => $value) {
  //Do some validation on the data in the field here
  $sql_query .= " $key = '$value',";
}
//Remove the final , from the query
$sql_query = substr_replace($sql_query, "", -1);
mysql_query("$sql_query WHERE condition_here")or die(mysql_error());
?>

There is no data cleansing or validation in the above script, this leaves your code open to injection, make sure you validate any input before processing it.

[mushbucket]

Thanks very much for your reply, xan!

I had to change your code around a bit to work with the rest of my code, however I thought I may as well post my final version here (your one does work fine). I realise the below probably isn't all-too-mighty but it works so that'll do me. :)

For the form:

$result = mysql_query("SELECT name, id FROM table ORDER BY name DESC");
	while($r=mysql_fetch_array($result))
		{	
			echo '<input type="text" value="'.$r[0].'" name="'.$r[1].'" /> ';
		}

To update:

foreach($_POST as $key => $value) {
	if ((is_numeric($key)) && ($value > "")){
	//Do some validation on the data in the field here
		$sql_query = "UPDATE table_name SET";
		$sql_query .= " `column_name` = '".$value."',";
	//Remove the final , from the query
		$sql_query = substr_replace($sql_query, "", -1);
		mysql_query("$sql_query WHERE `id` = '".$key."'")or die(mysql_error());
		echo $sql_query;
	}
}

I had the is_numeric($key) as there is other information sent with the form so that just parses it out.

I do have some validation bits in my personal version, don't worry. :)

Thanks again!

[Will Gresham]

To update:

foreach($_POST as $key => $value) {
	if ((is_numeric($key)) && ($value > "")){
	//Do some validation on the data in the field here
		$sql_query = "UPDATE table_name SET";
		$sql_query .= " `column_name` = '".$value."',";
	//Remove the final , from the query
		$sql_query = substr_replace($sql_query, "", -1);
		mysql_query("$sql_query WHERE `id` = '".$key."'")or die(mysql_error());
		echo $sql_query;
	}
}

This would be ok for just updating 2 columns in the DB, but if you look at the code, you have

$sql_query = "UPDATE table_name SET";
		$sql_query .= " `column_name` = '".$value."',";

inside the foreach loop, this isn't needed, what it is doing is each time you run through the foreach it is executing an sql query, so if you have 2 fields it is running 2 queries..

I would suggest trying it as it was in my post:

$sql_query = "UPDATE table_name SET";
foreach( $_POST as $key => $value) {
if ((is_numeric($key)) && ($value > "")) {
	//Do some validation on the data in the field here
		$sql_query .= " `column_name` = '".$value."',";
}
}
//Remove the final , from the query
$sql_query = substr_replace($sql_query, "", -1);
mysql_query("$sql_query WHERE condition_here")or die(mysql_error());
?>

Basically your code will do run 2 seperate queries:

mysql_query("UPDATE table_name SET `column_name` = '$value'");
mysql_query("UPDATE table_name SET `column_name` = '$value'");

rather than running it all at once after the loop:

mysql_query("UPDATE table_name SET `column_name` = '$value', `column_name` = '$value'");

[mushbucket]

My problem with that is how does the UPDATE syntax know what rows to update? The individual query will do it by ID#, however I can't figure it out the way you're doing it.

Should it be ORDER BY id DESC instead (for both display and update)? My head is stuck on this.

[Will Gresham]

Sorry, my mistake. This would work for what you want to do, I misunderstood things..

Although, rather than having the query spread over 2 lines, you may as well remove the $sql_query .= and put it all onto one line.