[RESOLVED] UPDATE problems with Drop Down Menu

[robkir]

My UPDATE procedure is failing. I'm sure its something obvious. I've tried six trillion variations but can't seem to find my problem:

I have four drop down's, here's the code for two of them:

//--| REFEREE 1 

//--| Load Sites into a drop down menu
	$sql_ref ="SELECT *
		FROM ref_table 
		ORDER BY refLast ";
		$res_ref = mysql_query($sql_ref, $link )or die(mysql_error($mysql));

echo "<td>";
print "<span class='table_small_type' width ='10%'>

<select name=ref1_ud['".$contact['gameID']."']>";
		do{
		print "<option value='" . $refInfo['refID'] . "'>" . $refInfo['refLast'] . ", " .$refInfo['refFirst']. " #" .$refInfo['region'] . "</option> \n";
		  }
		while($refInfo = mysql_fetch_array($res_ref, MYSQL_ASSOC));
		print "</select></td>";

//--| REFEREE 2 

//--| Load Sites into a drop down menu
	$sql_ref2 ="SELECT *
		FROM ref_table 
		ORDER BY refLast ";
		$res_ref2 = mysql_query($sql_ref2, $link )or die(mysql_error($mysql));

echo "<td>";
print "<span class='table_small_type' width ='10%'>

<select name=ref2_ud['".$contact['gameID']."']>";
		do{
		print "<option value='" . $refInfo2['refID'] . "'>" . $refInfo2['refLast'] . ", " .$refInfo2['refFirst']. " #" .$refInfo2['region'] . "</option> \n";
		  }
		while($refInfo2 = mysql_fetch_array($res_ref2, MYSQL_ASSOC));
		print "</select></td>";

Here's the UPDATE statement

//---| UPDATE

if($_POST['submit']=="Save")
{

   foreach($_POST['ref1_ud'] as $gameID => $value) 
	{
		$ref1= (protect($_POST['ref1_ud'][$gameID]));
		$ref2= (protect($_POST['ref2_ud'][$gameID]));
		$ref3= (protect($_POST['ref3_ud'][$gameID])) ;
		$ref4= (protect($_POST['ref4_ud'][$gameID])) ;


$query = "UPDATE master_game_sched SET ref1 = '$ref1'
WHERE gameID='$gameID' LIMIT 1";
mysql_query($query)or die ("Error in query: $query");
		}			
	}

I can see that the value of $game and $ref1, $ref2, $ref3, $ref4 are coming through but the error message says:

Error in query: UPDATE master_game_sched SET ref1 = '123' WHERE gameID=''89'' LIMIT 1

I am hoping its not a stupid mistake...but at the moment, its beyond me.

[bradgrafelman]

What's the actual error being returned by MySQL? (Hint: you didn't check the value of [man]mysql_error/man).

My guess is that it's the double quotes in the WHERE clause - try changing those to single quotes instead.

[robkir]

You were, as usual, right. It was the quotes. However, that raises a question. The code said:

$query = "UPDATE master_game_sched SET ref1 = '$ref1', ref2 = '$ref2' , ref3 = '$ref3' , ref4 = '$ref4'
WHERE gameID='$gameID' LIMIT 1";
mysql_query($query)or die ("Error in query: $query"); 

with a single quote around the WHERE clause '$gameID'. I actually lifted that from another routine that I knew worked. I fixed the current code by removing the single quotes around $gameID. That fix made it work...but I don't understand why, since the original code worked with the single quotes in place. Both the original code that I borrowed and this code are virtually identical.

I have another challenge that I could use some pointers on. I am using a do loop to create a drop down. I am dealing with two tables. The ref_table which has the ref's ID and name and the master_game_schedule which stores only the referee's ID number.

The problem is that I can get the updated value ($refInfo['refID'] ) or the current value of the referee ($refInfo['refID']) for a specific game from the master_game_schedule, but I can't figure out how to tie the refID number in with the ref_table to get the referees name. The $contact['ref1'] comes from the master_game_schedule table.

Here's the code: The option values come from the ref_table and the option selected comes from the master_game_schedule.

//--| REFEREE 1 
	$sql_ref ="SELECT *
		FROM ref_table
		ORDER BY refLast ";
		$res_ref = mysql_query($sql_ref, $link )or die(mysql_error($mysql));

echo "<td>";
print "<span class='table_small_type' width ='10%'>
<select name=ref1_ud['".$contact['gameID']."']>";
	do{
	print "<option value='" . $refInfo['refID'] . "'>" . $refInfo['refLast'] . ", " .$refInfo['refFirst']. " #" .$refInfo['region'] . "</option> \n";
 	  }

	while($refInfo = mysql_fetch_array($res_ref, MYSQL_ASSOC));
	print "<option selected= '" . $contact['ref1'] . "'selected>" . $contact['ref1']. "</option>";	

Thanks much!

[bradgrafelman]

robkir wrote:

I fixed the current code by removing the single quotes around $gameID. That fix made it work...but I don't understand why, since the original code worked with the single quotes in place

Probably because $gameID had a numeric value. Numeric values in SQL queries - just like in PHP code - should not be surrounded with quotes (though SQL - like PHP - will usually attempt to convert the string into a numeric quantity).

robkir wrote:

The ref_table which has the ref's ID and name and the master_game_schedule which stores only the referee's ID number.

Speaking of the master_game_schedule, why do you have columns named ref1, ref2, ref3, and ref4? Do all games have 4 ref's?

Also, you don't want a do/while loop here:

        do{ 
        print "<option value='" . $refInfo['refID'] . "'>" . $refInfo['refLast'] . ", " .$refInfo['refFirst']. " #" .$refInfo['region'] . "</option> \n"; 
           } 

    while($refInfo = mysql_fetch_array($res_ref, MYSQL_ASSOC));

since the first iteration of the loop will execute before data is ever retrieved from MySQL. Use a while() loop instead.

[robkir]

Normally there are three refs, but we like to have a standby just in case one ref gets tarred and feathered...just kidding

I'm going to show off my ignorance...I still can't get it to work. Here's my latest effort. Obviously I don't get how to use the While statement here.

//--| REFEREE 1 
	$sql_ref ="SELECT *
		FROM ref_table
		ORDER BY refLast ";
		$res_ref = mysql_query($sql_ref, $link )or die(mysql_error($mysql));

echo "<td>";
print "<span class='table_small_type' width ='20%'>
<select name=ref1_ud['".$contact['gameID']."']>";
	while($refInfo = mysql_fetch_array($res_ref, MYSQL_ASSOC));
	  {
	print "<option value='" . $refInfo['refID'] . "'>" . $refInfo['refLast'] . ", " .$refInfo['refFirst']. " #" .$refInfo['region'] . "</option> \n";
 	  }
	print "<option value selected= '" . $contact['ref1'] . "'>" . $refInfo['refLast']. "</option>";	
	print "</select></td>";

Sorry to keep throwing out code that doesn't work. I've spent a considerable time trying to figure it out and trying to find examples on the web. Surely somebody else has done this before...?

[bradgrafelman]

Personally, what I would do, is retrieve all of the referee's into an array. Presumably, you're using this same data for the four referee dropdowns, correct? If so, then you're simply wasting resources by querying the DB for the same data several times. So, storing the referee data is a simple matter:

    $sql_ref ="SELECT * 
        FROM ref_table 
        ORDER BY refLast "; 
        $res_ref = mysql_query($sql_ref, $link )or die(mysql_error($mysql));

    $ref_list = array();
    while($row = mysql_fetch_assoc($res_ref))
            $ref_list[] = $row;

Now, each item in the $ref_list array is a row from the database. Do a [man]print_r[/man]() on it if you can't visualize the structure yourself.

To generate the dropdown for referee #1, then, you'd do something like:

foreach($ref_list as $ref) {
    echo '<option value="' . $ref['refID'] . '"' . ($category['ref1'] == $ref['refID'] ? ' selected="selected"' : '') . '> (ref data here) </option';
}

[robkir]

IT WORKS. I've learned a few new tricks, too.

I do have a slight side effect. If the user fills in some, but not all of the referees (doesn't choose a referee from the drop down) and saves the page, the array erroneously inserts the first record from the table. My work around was to create a blank record as the first record and then filter it out in later modules. Is there a way to avoid this at the original array? I suspect it has to do with an if statement--I took a stab at it, but ...well... all I managed to do was bring the page down (too bad I can't market that talent).

Thanks for your patience and advice. Both are appreciated.

[bradgrafelman]

It's common to add a blank OPTION element as the first option in a combobox to avoid this situation.

There's no need for any if() statements or any extra PHP code at all - just add the HTML code for the blank OPTION element just after the SELECT tag is print()'ed.

[robkir]

Hi bradgrafelman,

I am a happy camper. Everything works perfectly. Thanks again.

robkir