[RESOLVED] UPDATE but won't redisplay data

robkir

I'm sure there is some error staring me in the face...but darned if I can find it. The problem is that when a user makes a change to a field (the only one currently set up is the variable tournProblem) it will UPDATE the record, but it doesn't redisplay the fields, after the user makes his changes and clicks on the Save Me button.

Thanks for any help you can provide.

<body> <span class="red_italic_text_medium">
<img src="../artwork/CupLogo.png" width="265" height="94">Control Panel<hr></span>

 <!-- DROP DOWN MENU to choose division -->	
	 	<form method="post"><label class="small_type">	<strong>FIRST </strong>select your division</label>
	 			<select name="showWhat_ud"> 
				<option value="View All"> View All </option>
				<option value="U10 Boys"> U10 Boys</option>
				<option value="U12 Boys">U12 Boys</option>
				<option value="U14 Boys">U14 Boys</option>
				<option value="U16 Boys">U16 Boys</option>
				<option value="U19 Boys">U19 Boys</option>
								</select> 

			<input name="Submit1" type="submit" value="See Division" />
		</form>
<?php 
//Determines if the user is looking at a NEW division, or the SAME division

		if ($_POST['showWhat_ud'])
			{
				$showWhat = $_POST['showWhat_ud']; 
				$_SESSION['old'] = $showWhat; 
					}
						else

					{

						$showWhat = $_SESSION['old'];
			}			
?>				

<form method="post">
<table style="width: 100%" class="cell_border">
	<tr class="background_color">
	<td class="cell_border" width="25%"><strong>Coach</strong></td>
	<td class="cell_border" width="5%"><strong>Region</strong></td>
	<td class="cell_border" width="5%"><strong>Rec'd <br />Packet</strong></td>
	<td class="cell_border" width="5%"><strong>Problem</strong></td>
	<td class="cell_border" width="5%"><strong>Approved</strong></td>
	<td class="cell_border" width="50%"><strong>Notes</strong></td>
	<td class="cell_border" width="5%"Send <br />email</td>
	<td class="cell_border" width="5%">See<br />App</td>

	</tr>


<?php ;

if ($showWhat OR ($_POST['saveStatus'])) //what to see 
{

//--| load records from team_info isset 

			$i = 0; 
			if($showWhat == "View All")
			{ 

				$result=mysql_query("SELECT * FROM team_info ORDER BY coachLast ASC");
				} else { 

				 	$result=mysql_query("SELECT * FROM team_info WHERE whatDiv= '".$showWhat."' ORDER BY coachLast ASC");
			} 

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

						$idTeam[$i] = $contact['idTeam'];

?>

	<tr>
		<td class="coach_left_just" ><?php echo $contact['coachLast']. ", " . $contact['coachFirst'] . " " .$contact['idTeam'] ;?></td>
		<td class="cell_border"><?php echo $contact['region'] ?></td>
		<td class="cell_border"><input name="Checkbox1" type="checkbox" /></td>
		<td class="cell_border"><input name="Checkbox2" type="checkbox" /></td>
		<td class="cell_border"><input name="Checkbox3" type="checkbox" /></td>
		<td class="cell_border"><?php echo "<input type =\"text\" name = \"tournProblem_ud[$i]\" value =\"{$contact['tournProblem']}\"
		style=\"width: 500px; background-color: #81F7F3;\">"; ?></td>
		<td class="table_small_type">EMAIL</td>
		<td class="table_small_type">GO</td>

	</tr>

<?php 
		$i++; $totalRecords++;

} // end while loop
} // end what to see loop ?>

		</table>
		<input name="saveStatus" type="submit" value="Save Me"></form>

<?php 
	if($_POST['saveStatus'])
	{
		$k = 0; 
			while ($k < $i) 
			{
				$tournProblem = $_POST['tournProblem_ud'][$k];


			$query = "UPDATE team_info SET 
					tournProblem ='$tournProblem'

				 WHERE idTeam = '$idTeam[$k]' LIMIT 1";
		    	 mysql_query($query) or die ("Error in query: $query");

		    	 $k++;

		 }
}		
?>

</body>
</html>

djjjozsi

Try to take the update program on the top of your file.

bradgrafelman

In other words, look at your order of operations: you're SELECT'ing the data to re-display it again before you UPDATE it. If you want the displayed data to reflect the changes, either don't re-run the SELECT query and just use the data POST'ed, or else process the UPDATE query before you run the SELECT query.

robkir

thanks for your input. I put the UPDATE at the beginning of the file. But I can't seem to figure out why it still isn't processing in the proper sequence.

I changed

if($showWhat OR ($_POST['saveStatus']))

 OR ($_POST['saveStatus'])

. I must be missing something. It still appears to run the SELECT statement before the UPDATE statement when the user clicks the SAVE ME button.

I'm still working on it, but if you can see an easy fix, please let me know.

bradgrafelman

robkir wrote:
It still appears to run the SELECT statement before the UPDATE statement when the user clicks the SAVE ME button.

Well yes, that's what you're telling PHP to do.

Start from the top of your script and start scrolling down. Which line do you get to first - the one with the SELECT statement, or the UPDATE statement? That's the one that's going to be executed first.

EDIT: D'oh - for some reason, my browser decided to scroll past the first line of your post. Oops!

Can you post a new copy of your code for us?

robkir

Here's the code. I've spent the last three hours trying every variation I could think of to get it to work--this is the current model. The output appears as an array so that multiple records can be updated simultaneously. It feels like this should work -- but I guess feelings and hard code are two separate worlds.

Thanks for staying with me on this.

<?php
session_start();
include('../validateUser_incl.php'); //checks to be sure that the user is logged in
require("../functions/checkEntries.php");
include_once "../DBconMOD.php";
include_once "../style_sheet.php";

	$k = 0; 
		while ($k < $totalRecords) 
		{
			$tournProblem = $_POST['tournProblem_ud'][$k];

			$query = "UPDATE team_info SET 
					tournProblem ='$tournProblem'

				 WHERE idTeam = '$idTeam[$k]' LIMIT 1";
		    	 mysql_query($query) or die ("Error in query: $query");

		    	 $k++;

		 }


?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Language" content="en-us" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
<link rel="stylesheet" type="text/css" href="../style_sheets/tournament_style_sheet.css">
<style type="text/css">
.table_small_type {
	border: 1px solid #008000;
	text-align: center;
	font-family: Arial, Helvetica, sans-serif;
	font-size: xx-small;
	text-decoration: underline;
}
.coach_left_just {
	border: 1px solid #008000;
	text-align: left;
	font-family: Arial, Helvetica, sans-serif;
	font-size: small;
}
</style>
</head>

<body> <span class="red_italic_text_medium">
<img src="../artwork/CupLogo.png" width="265" height="94">Control Panel<hr></span>

 <!-- DROP DOWN MENU to choose division -->	
	 	<form method="post"><label class="small_type">	<strong>FIRST </strong>select your division</label>
	 			<select name="showWhat_ud"> 
				<option value="View All"> View All </option>
				<option value="U10 Boys"> U10 Boys</option>
				<option value="U12 Boys">U12 Boys</option>
				<option value="U14 Boys">U14 Boys</option>
				<option value="U16 Boys">U16 Boys</option>
				<option value="U19 Boys">U19 Boys</option>
				<option value="U10 Girls">U10 Girls</option>
				<option value="U12 Girls">U12 Girls</option>
				<option value="U14 Girls">U14 Girls</option>
				<option value="U16 Girls">U16 Girls</option>
				<option value="U19 Girls">U19 Girls</option>
				<option value="U10 Coed">U10 Coed</option>
				<option value="U12 Coed">U12 Coed</option>
				<option value="U14 Coed">U14 Coed</option>
				<option value="U16 Coed">U16 Coed</option>
				<option value="U19 Coed">U19 Coed</option>
				</select> 

			<input name="Submit1" type="submit" value="See Division" />
		</form>
<?php 
//Determines if the user is looking at a NEW division, or the SAME division

		if ($_POST['showWhat_ud'])
			{
				print "order of operation 1 <br/>";
				$showWhat = $_POST['showWhat_ud']; 
				$_SESSION['old'] = $showWhat; 
					}
						else

					{

						$showWhat = $_SESSION['old'];
			}			

?>				

<form method="post">
<table style="width: 100%" class="cell_border">
	<tr class="background_color">
	<td class="cell_border" width="25%"><strong>Coach</strong></td>
	<td class="cell_border" width="5%"><strong>Region</strong></td>
	<td class="cell_border" width="5%"><strong>Rec'd <br />Packet</strong></td>
	<td class="cell_border" width="5%"><strong>Problem</strong></td>
	<td class="cell_border" width="5%"><strong>Approved</strong></td>
	<td class="cell_border" width="50%"><strong>Notes</strong></td>
	<td class="cell_border" width="5%"Send <br />email</td>
	<td class="cell_border" width="5%">See<br />App</td>

	</tr>


<?php 

if ($showWhat) //what to see 
{
//--| load records from team_info 
			$i = 0; 
			if($showWhat == "View All")
			{ 
				$result=mysql_query("SELECT * FROM team_info ORDER BY coachLast ASC");
				} 
				else 
				{ 
				 	$result=mysql_query("SELECT * FROM team_info WHERE whatDiv= '".$showWhat."' ORDER BY coachLast ASC");
			} 
	}				
					while ($contact = mysql_fetch_array($result)) 
					{

?>

	<tr>
		<td class="coach_left_just" ><?php echo $contact['coachLast']. ", " . $contact['coachFirst'] . " " .$contact['idTeam'] ;?></td>
        <td class="cell_border"><?php echo $contact['region'] ?></td>
        <td class="cell_border"><input name="Checkbox1" type="checkbox" /></td>
        <td class="cell_border"><input name="Checkbox2" type="checkbox" /></td>
        <td class="cell_border"><input name="Checkbox3" type="checkbox" /></td>
        <td class="cell_border"><?php echo "<input type =\"text\" name = \"tournProblem_ud[$i]\" value =\"{$contact['tournProblem']}\"
        style=\"width: 500px; background-color: #81F7F3;\">"; ?></td>
        <td class="table_small_type">EMAIL</td>
        <td class="table_small_type">GO</td> 			
	</tr>

<?php 
	$i++; $totalRecords++; $countme++;


} // end while loop

?>

		</table>
		<input name="saveStatus" type="submit" value="Save Me">saveStatus</form>
</body>


</html>

bradgrafelman

You still have a problem with the linear order of things. Look at your while condition; where is $totalRecords defined before you try to use it? Or $idTeam (inside the UPDATE query's WHERE clause)?

Also, down in the while loop for the SELECT statement, what are the three checkboxes for? No two HTML entities (especially in a form) can share the same name, so since you're outputting these checkboxes in a loop you probably aren't going to be able to use them properly (or at all).

My suggestion would be to alter the text box to contain the id of the record as an array index, like so:

echo '<input type ="text" name ="tournProblem_ud[' . $contact['idTeam'] . ']" value="' . $contact['tournProblem'] . '">';

Then, to do the UPDATE, you could do something like:

foreach($_POST['tournProblem_ud'] as $idTeam => $value) {
    // use $idTeam in the WHERE clause and $value.. well, for the value of course
}

EDIT: Forgot about those pesky checkboxes. Depending upon what you actually want to use them for, you can do something similar so that, when submitted, the array's key will be the id of the team for that row, keeping all of the data tied together and making it easier to work with.

robkir

Oops. I killed the idTeam when I was moving stuff around. The check boxes, as you surmised are placeholders. I'll rewrite the code with your suggestions tomorrow. Speaking of linear order, the UPDATE statement should come after the SELECT, right?

Thanks

bradgrafelman

robkir wrote:
Speaking of linear order, the UPDATE statement should come after the SELECT, right?

Only if you want to SELECT the old values before they are UPDATED, which I thought was the whole issue you were trying to fix.

robkir

hi bradgrafelman

Whew. It worked. I still have some work to do on the program, but I am out of time this morning. I am going to leave this thread open on the theory that there might be some other stumbling blocks to scale.

Thanks for simplifying the code. It really, really helped.

robkir

bradgrafelman

Feel free to post back with any problems or questions you have; sometimes working with "arrays" in HTML forms and whatnot can be a bit tricky.

Also, your code above (and I assume the way it is now, too) is vulnerable to SQL injection attacks as well as errors. User-supplied data should never be placed directly into a SQL query. Instead, it must first be sanitized with a function such as [man]mysql_real_escape_string/man.

robkir

bradgrafelman;10940496 wrote:
sometimes working with "arrays" in HTML forms and whatnot can be a bit tricky.
/man.

Tricky is an understatement. I've lost about half my head of hair on this short piece of code.

I am making progress--but still stumbling around. I've done check boxes before and what I have in this script should work (there I go again with the "should work" phrase. In reality it doesn't work). I'm sure its syntax. Where have I gone astray?

if ($showWhat) //what to see 
	{
	//--| load records from team_info 

		if($showWhat == "View All")
			{ 
				$result=mysql_query("SELECT * FROM team_info ORDER BY coachLast ASC");
				} 
				else 
				{ 
				 	$result=mysql_query("SELECT * FROM team_info WHERE whatDiv= '".$showWhat."' ORDER BY coachLast ASC");
			} 
}				
					while ($contact = mysql_fetch_array($result)) 
					{

					//---| set up checkboxes

						$packetRecd_status = 'unchecked';
						if ($contact['packetRecd']['idTeam'] == 1)
						{

							$packetRecd_status = 'checked';
							} else {

							$packetRecd_status = 'unchecked';
						}

?>	


	<tr>
		<td class="coach_left_just" ><?php echo $contact['coachLast']. ", " . $contact['coachFirst'] . " " .$contact['idTeam'] ;?></td>
		<td class="cell_border"><?php echo $contact['region'] ?></td>
		<td class="cell_border"><?php echo '<input type ="checkbox"
				name ="packetRecd_ud[' . $contact['idTeam'] . ']" 
				value="1' .$packetRecd_status.'">';?>	


		<td class="cell_border"><input name="Checkbox2" type="checkbox" /></td>
        <td class="cell_border"><input name="Checkbox3" type="checkbox" /></td>
        <td class="cell_border">
		<?php echo '<input type ="text"
			style="background-color: #81F7F3;" 
            size ="75px"
			name ="tournProblem_ud[' . $contact['idTeam'] . ']" 
			value="' . $contact['tournProblem'] . '">'; ?>

		 <td class="table_small_type"><a href="mailto:<?php echo $contact['coachEmail'];?>">EMAIL</a> </td>
        <td class="table_small_type">GO</td> 			
	</tr>


<?php 
						} // end while loop
?>
			</table>
			<input name="saveStatus" type="submit" value="Save Me">saveStatus</form>

You also reminded me to protect my data and I will. You gave me some pointers in a previous post which I have incorporated into all my scripts. In the interest of getting the script to work I usually build it before adding those components.

Thanks for your help.

johanafm

When learning new things, it can be a good idea to keep things simple while you get the hang of the new stuff.
But to leave out security on a general basis and "adding it later" is not a good idea. Ever. You always run the risk of missing to fix a piece of code or two...

And if you always use prepared statements, you don't have to deal with this by yourself, as it is taken care of behind the scenes. See mysqli or pdo at www.php.net.

robkir

Hi johanafm

Thanks for the advice. I haven't really looked into prepared statements yet but I'll follow up the lead.

robkir

Hi bradgrafelman

I'm still having a dickens of a time getting the check boxes to work right. Any chance you could review and advise?

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

					//---| set up checkboxes

						$packetRecd_status = 'unchecked';
						if ($contact['packetRecd']['idTeam'] == 1)
						{

							$packetRecd_status = 'checked';
							} else {

							$packetRecd_status = '';
						}
?>	


	<tr>
		<td class="coach_left_just" ><?php echo $contact['coachLast']. ", " . $contact['coachFirst'] . " " .$contact['idTeam'] ;?></td>
		<td class="cell_border"><?php echo $contact['region'] ?></td>
		<td class="cell_border"> <input type ="checkbox" 
				<?php echo 'name ="packetRecd_ud[' . $contact['idTeam'] . ']" 
				value="1" $packetRecd_status>'; ?> </td>

Thanks. I am really stuck.

djjjozsi

$contact is just an associated = one dimensional array!

<?php printf('<input type="checkbox" name="packetRecd_ud[%d]" value="1"%s />' ,
 $contact['idTeam'] ,
 (($contact['packetRecd']==0) ? '' : ' checked="checked"')); ?>

robkir

Hi djjjozsi
Thanks for the code. Please bear with me, some of this new territory for me.

This is, as you point out, a one dimensional array. Why doesn't $contact['packetRecd'] work in the 'value = ' ?

How do I extract the value from packetRecd_ud[%d] ? (by the way, I am actively trying to learn this. I did spent time trying to figure it out on my own, reading about arguement swapping and trying variations of $_POST['packetRecd_ud'][etc] before asking you to help.)

I also have a problem in my UPDATE code. I am using foreach. If I have a change in only the packetRecd_ud, or tournProblem_ud I can address that in an if statement. What if I have a change in both items, but not in the same record? How do I write that foreach statement ? I don't think it would take an AND or an OR modifier.

<td class="coach_left_just" ><?php echo $contact['coachLast']. ", " . $contact['coachFirst'] . " " .$contact['idTeam'] ;?></td>
			<td class="cell_border"><?php echo $contact['region'] ?></td>
			<td class="cell_border"> 
						<?php printf('<input type="checkbox" 
						name="packetRecd_ud[%d]"
						value="1"%s />' , $contact['idTeam'] ,(($contact['packetRecd']==0) ? '' : ' checked="checked"')); ?>
						</td>
			<td class="cell_border"><input name="Checkbox2" type="checkbox" /></td>
            <td class="cell_border"><input name="Checkbox3" type="checkbox" /></td>
            <td class="cell_border">
			<?php echo '<input type ="text"
				style="background-color: #81F7F3;" 
	            size ="75px"
				name ="tournProblem_ud[' . $contact['idTeam'] . ']" 
				value="' . $contact['tournProblem'] . '">'; ?>

Here's the UPDATE portion. Since I don't know how to extract the packetRecd_ud yet, obviously I've got a bad variable here

//---| UPDATE
	if($_POST['submit']=="Save")
	{
				if ($_POST['tournProblem_ud']){

				foreach($_POST['tournProblem_ud'] as $idTeam => $value) 
				{
		    		$tournProblem = $_POST['tournProblem_ud'][$idTeam];

					$query = "UPDATE team_info SET
						tournProblem ='$tournProblem'
						WHERE idTeam = '$idTeam' LIMIT 1";
				    	mysql_query($query) or die ("Error in query: $query");
				}

				}elseif ($_POST['packetRecd_ud']){
					 	 $packetRecd_ud = $_POST['packetRecd_ud'][$idTeam]; // not sure how to state packet variable

					$query = "UPDATE team_info SET
						packetRecd ='$packetRecd_ud'
						WHERE idTeam = '$idTeam' LIMIT 1";
				    	mysql_query($query) or die ("Error in query: $query");
			}			
}

Thanks for your patience.

robkir

Finally--I got all the kinks ironed out. Thanks to everybody for your help.

For those of you stymied by the same problem here are the key snippits. You are welcome to copy, but I am still a rank armature. Use at your own risk. First the input

			if($showWhat == "View All")
				{ 
					$result=mysql_query("SELECT * FROM team_info ORDER BY coachLast ASC");
					} 
					else 
					{ 
					 	$result=mysql_query("SELECT * FROM team_info WHERE whatDiv= '".$showWhat."' ORDER BY coachLast ASC");
				} 
	}				
						while ($contact = mysql_fetch_array($result)) 
						{

?>	
		<tr>
			<td class="coach_left_just" ><?php echo $contact['coachLast']. ", " . $contact['coachFirst'] . " " .$contact['idTeam'] ;?></td>
			<td class="cell_border"><?php echo $contact['region'] ?></td>
			<td class="cell_border"> 
						<?php printf('<input type="checkbox" 
						name="packetRecd_ud[%d]"
						value="1"%s />' , $contact['idTeam'] ,(($contact['packetRecd']==0) ? '' : ' checked="checked"')); ?>
						</td>
			<td class="cell_border"><?php printf('<input type="checkbox" 
						name="packetProblem_ud[%d]"
						value="1"%s />' , $contact['idTeam'] ,(($contact['packetProblem']==0) ? '' : ' checked="checked"')); ?>
</td>
            <td class="cell_border"><?php printf('<input type="checkbox" 
						name="packetApproved_ud[%d]"
						value="1"%s />' , $contact['idTeam'] ,(($contact['packetApproved']==0) ? '' : ' checked="checked"')); ?>
</td>
            <td class="cell_border">
			<?php echo '<input type ="text"
				style="background-color: #81F7F3;" 
	            size ="75px"
				name ="tournProblem_ud[' . $contact['idTeam'] . ']" 
				value="' . (htmlentities($contact['tournProblem'])) . '">'; ?>

Once the user hits the submit button, it goes to the foreach routine:

//---| UPDATE
	if($_POST['submit']=="Save")
	{

				foreach($_POST['tournProblem_ud'] as $idTeam => $value) 
				{
		    		$tournProblem = (protect($_POST['tournProblem_ud'][$idTeam]));
		    		$packetRecd = $_POST['packetRecd_ud'][$idTeam] ;
		    		$packetProblem = $_POST['packetProblem_ud'][$idTeam] ;
		    		$packetApproved = $_POST['packetApproved_ud'][$idTeam] ;

					$query = "UPDATE team_info SET
						packetRecd ='$packetRecd' ,
						packetProblem ='$packetProblem' ,
						packetApproved = '$packetApproved' ,
						tournProblem ='$tournProblem'
						WHERE idTeam = '$idTeam' LIMIT 1";
				    	mysql_query($query) or die ("Error in query: $query");

				}			
}

And that's pretty much it.

bradgrafelman

Don't forget to sanitize all of the incoming data to prevent SQL injections (and/or errors).

robkir

I only have two types of data: check boxes and a tinytext. on the tinytext I've got mysql_real_escape_string on the field going into the database and htmlentities on the data coming out. Sufficient?