Update multiple records with a single hiddenfield

smsmarketeers · May 5, 2011

If I am not mistaken what you are trying to do is quite simple. You have multiple records in your database. You select the records from the database and display them all in one form. You want to make updates to some of them but not all of them. What you need to do is use the unique identifier of each record in an array in the form field name. For example

<input name="name[identifier]" type="checkbox" />

Here is a working example that I put together. Create a database, import the table and the two entries, change the settings for the database in the script, and watch it work.

<?php

/*
	Written By: SMS Marketeers
	Website: http://www.smsmarketeers.com

	Update multiple database records using a unique identifer
	based on the submission of a form with multiple unique
	identifiers in an array.

	Database Structure

	CREATE TABLE IF NOT EXISTS `ship` (
	`ship_id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(32) CHARACTER SET latin1 NOT NULL,
	`health` int(11) NOT NULL,
	`date` datetime NOT NULL,
	PRIMARY KEY (`ship_id`)
	) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3;

	INSERT INTO `ship` (`ship_id`, `name`, `health`, `date`) VALUES (1, 'Ship A', 0, '2011-05-04 16:37:52'), (2, 'Ship B', 0, '2011-05-04 16:37:52');
*/

// Define Database Variables
$dbHostname = 'localhost';
$dbUsername = 'username';
$dbPassword = 'password';
$dbDatabase = 'database';

// Establish Database Connection
$dbCon = mysql_connect($dbHostname, $dbUsername, $dbPassword) or die('Error: Unable able to connect: ' . mysql_error());

// Select Working Database
$dbSel = mysql_select_db($dbDatabase, $dbCon) or die('Error: Unable to select database: ' . mysql_error());

?>

<html>
<head>
<title>Multiple Record Update from one Form</title>
<style type="text/css">
	*		{ font-size:12px; font-family:Arial; margin:0px; outline:0px; padding:0px; }
	body	{ background:#ffffff; color:#000000; margin:10px 0px 0px 0px; }
	img		{ border:0px; }
	p		{ margin:5px 0px 10px 0px; }
	form	{ border:none; margin:0px; padding:0px; }

	a				{ cursor:pointer; }
	a:link			{ color:#9AB324; text-decoration:none; }
	a:visited		{ color:#9AB324; text-decoration:none; }
	a:hover 		{ color:#9AB324; text-decoration:underline; }
	a:active 		{ color:#9AB324; text-decoration:none; }

	.container		{ margin:0px auto; width:600px; }
	.success		{ background:#EEF5CD; border:1px dashed #9AB324; color:#608339; margin-bottom:5px; padding:5px 5px 5px 25px; text-align:left; }
	.warning		{ background:#eed4d2; border:1px dashed #a94637; color:#ac241a; margin-bottom:5px; padding:5px 5px 5px 25px; text-align:left; }
	.attention		{ background:#fefbcc; border:1px dashed #e6db55; color:#ada019; margin-bottom:5px; padding:5px 5px 5px 25px; text-align:left; }

	table.data th				{ background:#9AB324; border-bottom:1px solid #596E0E; color:#ffffff; font-weight:bold; padding:5px; text-align:center; }
	table.data td				{ border-bottom:1px solid #dddddd; padding:5px; }
	table.data td.rowOne		{ background:#f5f5f5; }
	table.data td.rowTwo		{ background:#eeeeee; }
	table.data td.button		{ background:#ffffff; border:none; text-align:right; }
</style>
</head>
<body>

<div class="container">
<?php

	// Handle Update / Form Post
	if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['update'])) {
		$updated = 0;

		foreach ($_POST['update'] AS $key => $value) {
			$update = mysql_query("UPDATE ship SET health = health + 1, date = NOW() WHERE ship_id = '" . $key . "'") or die('Error: Unable to execute query: ' . mysql_error());
			$updated++;
		}

		echo '<div class="success">Updated <strong>' . $updated . '</strong> records</div>';
	} elseif ($_SERVER['REQUEST_METHOD'] == 'POST' && empty($_POST['update'])) {
		echo '<div class="warning">The form was submitted but no items were checked to be updated</div>';
	}

	// Select All Records from Database
	$query = mysql_query("SELECT * FROM ship ORDER BY ship_id ASC") or die('Error: Unable to execute query: ' . mysql_error());

	if ($query) {
		if (is_resource($query)) {
			$i = 0;
			$data = array();

			while ($result = mysql_fetch_assoc($query)) {
				$data[$i] = $result;
				$i++;
			}

			mysql_free_result($query);

			$results = array();
			$results = $data;

			unset($data);
		}
	} else {
		echo 'div class="warning">No results returned</div>';
	}

?>

<form action="" method="POST" name="form" id="form">
	<div class="attention">This form will update any and all records check upon submission. It submits the checkboxes as an array using the ship_id is the unique identifer.</div>

	<table align="center" border="0px" cellpadding="0px" cellspacing="1px" class="data" width="600px">
		<tr>
			<th width="70px">Update</th>
			<th>Ship Name</th>
			<th>Health</th>
			<th>Last Update</th>
		</tr>
		<?php

			$rowOne = 'rowOne';
			$rowTwo = 'rowTwo';
			$rowCount = 0;

		?>
		<?php foreach ($results as $ship) { ?>
			<?php $rowClass = ($rowCount % 2) ? $rowTwo : $rowOne; ?>
			<tr>
				<td align="center" class="<?php echo $rowClass; ?>"><input name="update[<?php echo $ship['ship_id']; ?>]" type="checkbox" /></td>
				<td class="<?php echo $rowClass; ?>"><?php echo $ship['name']; ?></td>
				<td align="center" class="<?php echo $rowClass; ?>"><?php echo $ship['health']; ?></td>
				<td align="center" class="<?php echo $rowClass; ?>"><?php echo $ship['date']; ?></td>
			</tr>
			<?php $rowCount++; ?>
		<?php } ?>
		<tr><td class="button" colspan="4"><input name="submit" type="submit" value="Update Selected" /></td></tr>
	</table>
</form>
</div>

<?php mysql_close($link); ?>

</body>
</html>

mrt003003 · May 6, 2011

Thank you all very much for the help! Ok so its clear that using forms to update multiple records in not really dooable. I didnt relaise that you could post data to the database with using a form so thats a great learning curve there! Thank you for your example i will study and master it and because of all of your help i've learnt alot!

Thanks again everyone

Sign In

Update multiple records with a single hiddenfield

Recommended Posts

smsmarketeers

Link to comment

Share on other sites

mrt003003

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information