PDA

Click to See Complete Forum and Search --> : Need help with Update multiple records

TimberWolf
05-24-2005, 06:57 AM
I need to update many records/rows of the same Mysql table. If I create a html table with a submit button for each row it works, but I can update a single record at a time like this:

<?php require_once('../Connections/conn.php'); ?>
<?php
mysql_select_db($database_conn, $conn);
$query_item = "SELECT * FROM actrav_libitem";
$item = mysql_query($query_item, $conn) or die(mysql_error());
$row_item = mysql_fetch_assoc($item);

if ((isset($_POST["Update"])) && ($_POST["Update"] == "UpForm")) {
$updateSQL = sprintf("UPDATE actrav_libitem SET caption='".$_POST['caption']."', `level`='".$_POST['level']."', item_order='".$_POST['item_order']."' WHERE ItemID='".$_POST['ItemID']."' ");
mysql_select_db($database_conn, $conn);
$Result1 = mysql_query($updateSQL, $conn) or die(mysql_error());
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<table border='1'>
<tr valign="top">
<td nowrap align="center">ID:</td>
<td nowrap align="center">Caption:</td>
<td nowrap align="center">Level:</td>
<td nowrap align="center">Item_order:</td>
<td nowrap align="center">&nbsp;</td>
</tr>
<?php do { ?>
<form method="post" name="UpForm" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="Update" value="UpForm">
<input type="hidden" name="ItemID" value="<?php echo $row_item['ItemID']; ?>">
<input type="hidden" name="caption" value="<?php echo $row_item['caption']; ?>">
<tr valign="top">
<td><?php echo $row_item['ItemID']; ?></td>
<td><?php echo $row_item['caption']; ?></td>
<td><input type="text" name="level" value="<?php echo $row_item['level']; ?>" size="6"></td>
<td><input type="text" name="item_order" value="<?php echo $row_item['item_order']; ?>" size="6"></td>
<td><input type="submit" value="GO"></td>
</tr>
</form>
<?php } while ($row_item = mysql_fetch_assoc($item)); ?>
</table>

<p>&nbsp;</p>
</body>
</html>
How can I do to enter values in many rows, maybe with a checkbox to select wich rows I want to affetct, and update all the affected rows with a single button?

I understand that it has to do with loops and arrays, but I'm only making mess like this:

<?php require_once('../Connections/conn.php'); ?>
<?php
mysql_select_db($database_conn, $conn);
$query_item = "SELECT * FROM actrav_libitem";
$item = mysql_query($query_item, $conn) or die(mysql_error());
$row_item = mysql_fetch_assoc($item);


if ((isset($_POST["Update"])) && ($_POST["Update"] == "UpForm")) {
for ($i = 0; $i < count($_POST['ItemID']); $i++)
{
$updateSQL = sprintf("UPDATE actrav_libitem SET caption='".$_POST['caption'.$i]."', `level`='".$_POST['level'.$i]."', item_order='".$_POST['item_order'.$i]."' WHERE ItemID='".$_POST['ItemID'.$i]."' ");

mysql_select_db($database_conn, $conn);
$Result1 = mysql_query($updateSQL, $conn) or die(mysql_error());
}
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form method="post" name="UpForm" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="Update" value="UpForm">
<input type="hidden" name="ItemID[]">
<input type="hidden" name="caption[]">
<table border='1'>
<tr valign="top">
<td nowrap align="center">ID:</td>
<td nowrap align="center">Caption:</td>
<td nowrap align="center">Level:</td>
<td nowrap align="center">Item_order:</td>
</tr>
<?php do { ?>

<tr valign="top">
<td><?php echo $row_item['ItemID']; ?></td>
<td><?php echo $row_item['caption']; ?></td>
<td><input type="text" name="level[]" value="<?php echo $row_item['level']; ?>" size="6"></td>
<td><input type="text" name="item_order[]" value="<?php echo $row_item['item_order']; ?>" size="6"></td>
</tr>
<?php } while ($row_item = mysql_fetch_assoc($item)); ?>
</table>
<td><input type="submit" value="GO"></td></form>
<p>&nbsp;</p>
</body>
</html>

Can anybody help me?
Thank you in advance
DaiWelsh
05-24-2005, 07:08 AM
<input type="hidden" name="ItemID[]">
<input type="hidden" name="caption[]">

these need to be inside the loop through your database items and need to be filled with the relevant values, although from what I can see you are not allowing them to update caption anyway so strictly that should go altogether (updating the database from hidden fields is just inviting hacking for no reason).

Also you are using the wrong method for accessing the fields when they are posted, if you have used the name="fieldname[]" notation in a field name then php creates an array for the items so you then access them as $_POST['fieldname'][0], $_POST['fieldname'][1], etc. not as $_POST['fieldname'.$i] .

Try the following code instead and it may at least move you on several steps even if it is not 100% correct (I don't have time or setup here to test it)...


<?php require_once('../Connections/conn.php'); ?>
<?php
mysql_select_db($database_conn, $conn);
$query_item = "SELECT * FROM actrav_libitem";
$item = mysql_query($query_item, $conn) or die(mysql_error());
$row_item = mysql_fetch_assoc($item);
if ((isset($_POST["Update"])) && ($_POST["Update"] == "UpForm")) {
for ($i = 0; $i < count($_POST['ItemID']); $i++)
{
$updateSQL = sprintf("UPDATE actrav_libitem SET `level`='".$_POST['level'][$i]."', item_order='".$_POST['item_order'][$i]."' WHERE ItemID='".$_POST['ItemID'][$i]."' ");

mysql_select_db($database_conn, $conn);
$Result1 = mysql_query($updateSQL, $conn) or die(mysql_error());
}
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form method="post" name="UpForm" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="Update" value="UpForm">
<table border='1'>
<tr valign="top">
<td nowrap align="center">ID:</td>
<td nowrap align="center">Caption:</td>
<td nowrap align="center">Level:</td>
<td nowrap align="center">Item_order:</td>
</tr>
<?php do { ?>
<input type="hidden" name="ItemID[]" value="<?php echo $row_item['ItemID']; ?>">
<tr valign="top">
<td><?php echo $row_item['ItemID']; ?></td>
<td><?php echo $row_item['caption']; ?></td>
<td><input type="text" name="level[]" value="<?php echo $row_item['level']; ?>" size="6"></td>
<td><input type="text" name="item_order[]" value="<?php echo $row_item['item_order']; ?>" size="6"></td>
</tr>
<?php } while ($row_item = mysql_fetch_assoc($item)); ?>
</table>
<td><input type="submit" value="GO"></td></form>
<p>&nbsp;</p>
</body>
</html>
TimberWolf
05-24-2005, 07:32 AM
Thank You, Very Much!!!
It works perfectly, By the way, I didn't mean to be so strict with hidden field, for testing reason I shortened the code
Thank you again