update.php form update script.

[chris11]

Good morning. I am looking fo some help with an update script to update all rows in a table.

 

This is what I have for a form and all looks well. form.php

<?php

// run the query and put the results in an array variable called $result 
$result = mysql_query("SELECT * FROM table ORDER BY 'id', 'title', 'text', 'number'"); 


print "<form method='post' action='update.php'> 
<table width='100%' border='0' cellspacing='1' cellpadding='1'><tr> 

<td align='center'><strong>ID</strong></td> 
<td align='center'><strong>Title</strong></td> 
<td align='center'><strong>text</strong></td> 
<td align='center'><strong>Number</strong></td> 
</tr>\n"; 

// start a loop to print all of the courses with their book information 
// the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left 
while ($Update = mysql_fetch_array($result)) { 


  // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i]) 
  print "<td align='center'><p>{$Update['id']}</p></td>\n"; 
  print "<td align='center'><input type='text' name='title' value='{$Update['title']}' /></td>"; 

  print "<td align='center'><input type='text' size='40' name='text' value='{$Update['text']}' /></td>\n"; 
  print "<td align='center'><input type='text' size='40' name='number' value='{$Update['number']}' /></td>\n"; 
print "</tr>\n"; 
// add 1 to the count, close the loop, close the form, and the mysql connection 

} 
print "<tr> 
<td colspan='4' align='center'><input type='submit' value='submit' />"; 
print "</td> 
</tr> 
</table> 
</td> 
</tr> 
</form> 
</table>"; 

print "</tr>\n"; 
?><br /><br />

 

 

My question is. How do I update this info into the database with the proper info. ie. Update.php?

[litebearer]

depending upon who has editing rights, you may want to look into the following free scripts...

http://www.apphp.com/php-datagrid/index.php

http://www.phpmyedit.org/

[chris11]

I'm keeping the script in a secure area. I looked at the links you provided. it's a a bit more than I need. I only need to update 10 rows of data. There won't ever be more than ten rows.

 

I'll keep at it. if I find a solution I'll post back.

 

 

[fortnox007]

well, I guess what you could do is: on top of update.php get the post variables and place them in a new update query. // I would not let them change the ID i bet its a auto incrementing primary key right?

 

//start update.php
if (isset($POST['submit'])&&!empty($_POST['title'])&&!empty($_POST['text'])&&!empty($_POST['number'])){

$ID= (int)$_POST['ID'];
$title = mysqli_real_escape_string($_POST['title']);
$text = mysqli_real_escape_string($_POST['text']);
$number = mysqli_real_escape_string($_POST['number']);

$query = "UPDATE yourtablename SET title = '$title' SET text = '$text' SET number ='$number'
WHERE ID = '$ID' ";
}else{
     echo 'you need to fill in all fields';
     //maybe even output form here again or make a redirect to the form with a error message.
}

[litebearer]

As you are displaying and editing multiple records, you should make your form variables arrays, then in your processing page loop thru the arrays.

 

rough idea - untested - un-proofread - no error trapping or cleansing

 

<?php
$result = mysql_query("SELECT * FROM table ORDER BY 'id', 'title', 'text', 'number'"); 
$num_rows = mysql_num_rows{$result);
?>
<form method='post' action='update.php'> 
<input type="hidden" name="num_rows" value="<?PHP echo $num_rows; ?>">
<table width='100%' border='0' cellspacing='1' cellpadding='1'>
	<tr> 
		<td align='center'><strong>ID</strong></td> 
		<td align='center'><strong>Title</strong></td> 
		<td align='center'><strong>text</strong></td> 
		<td align='center'><strong>Number</strong></td> 
	</tr>
	<?PHP
	while ($Update = mysql_fetch_array($result)) {
		?>
		<tr>
			<td align="center"><p><?PHP echo $Update['id']; ?></p></td>
			<td align="center"><input type="text" name="title[]" value="<?PHP echo $Update['title']; ?>></td> 
			<td align="center"><input type="text" size="40" name="text[]" value="<?PHP echo $Update['text']; ?>"></td> 
			<td align="center"><input type="text" size="40" name="number[]" value="<?PHP echo $Update['number']; ?>"></td>
			<input type="hidden" name="wid[]" value="<?PHP echo $Update['id']; ?>">	
		</tr>
	}
	?>
	<tr><td colspan="4" align="center"><input type="submit" value="submit"></td></tr> 
</table>
</form>
</body>
</html>
------------------
update.php
<?PHP
$num_rows = $_POST['num_rows'];
$i = 0;
/* connect to db here */
while($i<$num_rows) {
$wid = $_POST['wid[$i]'];
$title = $_POST['title[$i]'];
$text = $_POST['text[$i]'];
$number = $_POST['number[$i]'];
$query = "UPDATE tablename set title = '$title', text = '$text', number = '$number' WHERE id = '$wid'";
$result = mysql_query($query);
}
?>

[chris11]

Yes I am updating all rows of a table. I have ten rows and wish to have a form where I can update all rows and fields of the table.

 

I'm getting parse error on line 5 of your code, Litebearer. Here is line 5.

 

$num_rows = mysql_num_rows{$result)

 

@fortnox007

 

The id's of the rows are 1 through 10. I'm just using this as a way of ordering. I don't wish them to be changed at all.

 

 

 

 

[BlueSkyIS]

when mentioning an error. but knowing nothing else i suspect the problem is a missing semi-colon on that line.

[litebearer]

please post your most recent code

[chris11]

kk. i got hung up on the "wid" I guess it was changed because id isn't good to use. Anyhow.

Here is the pay off for future searchers. (Hello the future. How are the dehydrated turkey pills?)

 

Because this script only updates the data you will need a table with some full rows.  4 columns. an int for the id and the rest text or var.

 

Here is the form page. It will display text input fields for each of the tables besides the "wid" table. That is just to order it by number since the 'number' field may have text in it like a dollar sign etc.

 

blabla.php

<?php
  $dbhost = "localhost";
$dbuser = "user_name";
$dbpass = "xxxxxx";
$db = "DB_name";

mysql_connect($dbhost,$dbuser,$dbpass); mysql_select_db($db);

$result = mysql_query("SELECT * FROM tablename ORDER BY wid");

$i = 0;

print "<form name='names' method='post' action='update.php'>\n";

while ($books = mysql_fetch_array($result)) {


print "<input type='hidden' name='wid[$i]' value='{$books['wid']}' />";
print "<p>{$books['wid']}: <input type='text' size='20' name='title[$i]' value='{$books['title']}' /><input type='text' size='30' name='info[$i]' value='{$books['info']}' /> <input type='text' size='5' name='number[$i]' value='{$books['number']}' /></p>\n";

++$i;
}
print "<input type='submit' value='submit' />";
print "</form>";

?>

 

and here is the update.php

<?php
$host="localhost"; // Host name 
$username="User_name"; // Mysql username 
$password="xxxxxxx"; // Mysql password 
$db_name="DB_Name"; // Database name 
$tbl_name="your_table_here"; // Table name


mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


$size = count($_POST['title']);


$i = 0;
while ($i < $size) {


$title= $_POST['title'][$i];
$info= $_POST['info'][$i];
$number= $_POST['number'][$i];
$id = $_POST['wid'][$i];


$query = "UPDATE tablename SET `title` = '$title', `info` = '$info', `number` = '$number' WHERE `wid` = '$id' LIMIT 1";
mysql_query($query) or die ("Error in query: $query");
print "$title---$info----$number---<font color='red'>Updated!</font><br /><br />";
++$i;
}

?>

 

I didn't close the database connection in either of the above. Set the connection with a var and close it it you like.

 

Thanks everybody. High Fives.

 

[fortnox007]

don't forget to sanitize like shown in pretty much every script above

[chris11]

Good point. Add this to the update.php just inside the 'while' statement so you don't get caught up on commas etc.

$title= mysql_real_escape_string($title);
$info= mysql_real_escape_string($info);
$number= mysql_real_escape_string($number);

 

[fortnox007]

put (int) before $id,  also hidden fields can be altered and you certainly don't want that to happen. with (int) you force it to be an integer, which i am pretty sure $id is.