• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

Editing Multiple Records in MySQL Database

I am attempting to edit multiple records in a MySQL table.  There are 15 records involved, and one field on each record is supposed to be editable.

The database connection seems to be operational, because the form populates with the current data.  However, I've been unable to get the UPDATE to work correctly.

I've pasted the code for the page into this document.  I'm sure you'll want to see the live page, and to do that, I'll have to disable the authentication on the page.  That's fine, because this page isn't able to be seen by anyone else yet anyways.

If you have other questions, please let me know, and I'll answer to the best of my ability.
<?php
if(isset($_SESSION['admin']) && $_SESSION['admin'] == 1)
{	
	$title = "MSUSBC Senior Championships Lanes Used";

	@include('Connections/mysqli_connect.php');
	$q = "SELECT * FROM `seniorsquads12` ORDER BY `squadID` ASC";
	$r = mysqli_query($dbc2, $q);
	
	$count = mysqli_num_rows($r);
	
	error_reporting(-1);
?>

<h2 align="center">2012 MSUSBC SENIOR CHAMPIONSHIPS</h2><br />
<h3 align="center">LANES AVAILABLE INPUT</h3><br />
<form id="lanesUsed" name="lanesUsed" method="post" action="">

<table width="60%" border="0" cellpadding="5" cellspacing="0" bordercolor="#283168">
		<th>
			<td>Squad #</td><td>Day</td><td>Date</td><td>Time</td><td>Lanes Used</td>
		</th>
	<?php
	while ($row = mysqli_fetch_array($r))
	{
		$id[] = $row['squadID'];
		echo '<tr>';
		echo '<td>'.$row['squadNumber'].'</td><td>'.$row['day'].'</td><td>'.$row['date'].'</td><td>'.$row['time'].'</td><td><input name="lanes[]" type="number" id="lanes" value="'.$row['lanes'].'">';
		echo '</td></tr>';
	}
	
	echo '<tr>';
	echo '<td colspan="5" align="center"><input type="submit" name="Submit" value="Submit"></td>';
	echo '</tr>';
	
	?>
</table>	
</form>	
	
<?php
if($Submit)
{	
	for($i=0; $i<$count; $i++)
	{
		$q2 = "UPDATE `seniorsquads12` SET `lanes` = '$lanes[$i]' WHERE `squadID` = '$id[$i]'";
		$r2 = mysqli_query($dbc2, $q2);
	}
}

if($r2)
{	
	echo 'header("location:'.$_SERVER['PHP_SELF'].';?content=lanesUsedSenior")';
}
mysqli_close();
?>	
	
<?php } else {
echo "<div style=\"padding:10px; color:red;\"><h1>Please Login</h1></div>";
include ('login.inc.php');
}
?>

Open in new window

<?php
if(isset($_SESSION['admin']) && $_SESSION['admin'] == 1)
{	
	$title = "MSUSBC Senior Championships Lanes Used";

	@include('Connections/mysqli_connect.php');
	$q = "SELECT * FROM `seniorsquads12` ORDER BY `squadID` ASC";
	$r = mysqli_query($dbc2, $q);
	
	$count = mysqli_num_rows($r);
	
	error_reporting(-1);
?>

<h2 align="center">2012 MSUSBC SENIOR CHAMPIONSHIPS</h2><br />
<h3 align="center">LANES AVAILABLE INPUT</h3><br />
<form id="lanesUsed" name="lanesUsed" method="post" action="">

<table width="60%" border="0" cellpadding="5" cellspacing="0" bordercolor="#283168">
		<th>
			<td>Squad #</td><td>Day</td><td>Date</td><td>Time</td><td>Lanes Used</td>
		</th>
	<?php
	while ($row = mysqli_fetch_array($r))
	{
		$id[] = $row['squadID'];
		echo '<tr>';
		echo '<td>'.$row['squadNumber'].'</td><td>'.$row['day'].'</td><td>'.$row['date'].'</td><td>'.$row['time'].'</td><td><input name="lanes[]" type="number" id="lanes" value="'.$row['lanes'].'">';
		echo '</td></tr>';
	}
	
	echo '<tr>';
	echo '<td colspan="5" align="center"><input type="submit" name="Submit" value="Submit"></td>';
	echo '</tr>';
	
	?>
</table>	
</form>	
	
<?php
if($Submit)
{	
	for($i=0; $i<$count; $i++)
	{
		$q2 = "UPDATE `seniorsquads12` SET `lanes` = '$lanes[$i]' WHERE `squadID` = '$id[$i]'";
		$r2 = mysqli_query($dbc2, $q2);
	}
}

if($r2)
{	
	echo 'header("location:'.$_SERVER['PHP_SELF'].';?content=lanesUsedSenior")';
}
mysqli_close();
?>	
	
<?php } else {
echo "<div style=\"padding:10px; color:red;\"><h1>Please Login</h1></div>";
include ('login.inc.php');
}
?>

Open in new window

0
DennisHacker
Asked:
DennisHacker
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
IrogSintaCommented:
I'm not really a MySQL or php expert but I'm wondering if it would work if you change your Update line to this:

$q2 = "UPDATE `seniorsquads12` SET `lanes` = '$lanes[$i]' WHERE `squadID` = '".$id[$i]."'";

Open in new window

0
 
DennisHackerAuthor Commented:
Thanks for the attempt.  I'm not sure why there would be a need to concatenate the variable on the WHERE part of the query when you don't have to do it on the SET part?
0
 
IrogSintaCommented:
You know what, I wasn't even paying attention there.  I was just focused on the Where clause.  But you're right, I really should have posted it this way:
$q2 = "UPDATE `seniorsquads12` SET `lanes` = '".$lanes[$i]."' WHERE `squadID` = '".$id[$i]."'";

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mohamed AbowardaSoftware EngineerCommented:
You are either storing integer as varchar or using invalid syntax:
WHERE `squadID` = '$id[$i]'";

Open in new window

Should be:
WHERE `squadID` = $id[$i]";

Open in new window

0
 
Robert SchuttSoftware EngineerCommented:
I'm missing this code:
$lanes = $_POST['lanes'];

Open in new window

Or is that hidden in the connect include?
0
 
Ray PaseurCommented:
Add error_reporting(E_ALL); and ini_set('display_errors', TRUE); to the top of the scripts.  It appears to me that $lanes is an undefined variable.  You might want to try printing out the contents of $_POST and the contents of the query string in the $q2 variable.  You can use var_dump() to get this information.
http://us2.php.net/manual/en/function.var-dump.php
0
 
DennisHackerAuthor Commented:
So Ray:  Thanks for reminding me to look at errors.  The first problem was an undefined variable called $Submit.  It wasn't allowing me to test for the $_POST that way.  I changed that to:

if($_SERVER['REQUEST_METHOD'] == "POST")

which worked, and then the script gave me all of the undefined variable errors for $lanes (thanks also to robert_schutt), which I fixed by simply defining that variable in the i loop.  Now the form works properly, and updates the database.

One final question on this:  what is the best way to get the page to refresh with the new values in the database, once they press submit?
0
 
Ray PaseurCommented:
Thanks for the points.  
get the page to refresh with the new values...
In the action script, update the data base, then run a SELECT query to retrieve the data that was just updated.  Prepopulate the form with the values from the data base query.

As a general rule, I use this to see if the form has been submitted.

if (!empty($_POST)) { /* process form data */ }
0
 
DennisHackerAuthor Commented:
Thanks Ray, but isn't that code already there when the page loads?  I have it so that it pre-populates the form with the data as the page first loads.  However, I'm having headers already sent issues if I simply try to reload the page that way.

Your test to see if the form is submitted is much simpler than the one I used.  I implemented it.  Thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now