Simple question - i think!

[NickyJay]

Hi All,

i have a form that outputs a record from my database for editing. I have a couple of dropdown options on there and want them to display the CURRENT value in the database record so they can alter it if needed. Im able to do this no probs in Coldfusion but am struggling to transfer the coding to php! any help? The dropdowns are initially populated with values from seperate lookup tables in the database, where a primary key in the lookup references a foreign key in the main record.

So far i have tried Selected="selected" in my option tag, but this brings up random records!

Thanks!

 

[feherke]

Hi

So far i have tried Selected="selected" in my option tag, but this brings up random records!

That is the way to do. Show us your code and we will tell you what is wrong.

Next time please put a meaningful subject to your thread.


Feherke.

http://rootshell.be/~feherke/

 

[NickyJay]

hiya, many thanks, the code im using is

// get list of all available Mediums to populate dropdown:
				$getMediums_sql = "SELECT * FROM $tbl_name2 ORDER BY MediumName ASC";
				$getMediums_results = mysql_query($getMediums_sql, $mysql) or die(mysql_error($mysql));

//	***** get the main recordset based on the passed url *****
				// Show the events for this day:
				$getEvent_sql = "SELECT EventIDPK, EventTitle, EventDetails, EventMediumIDFK, MediumName, EventTypeDesc, date_format(EventStartTime, '%H:%i') as fmt_time, EventDate, date_format(EventDate, '%l:%i %p') as fmt_date, EventTypeIDFK FROM $tbl_name1, $tbl_name2, $tbl_name3 WHERE tblevent.EventMediumIDFK = tblmedium.MediumIDPK AND tblevent.EventTypeIDFK = tbleventtype.EventTypeIDPK AND tblevent.EventIDPK = ".$_GET['ID'];
				$getEvent_res = mysql_query($getEvent_sql, $mysql) or die(mysql_error($mysql));

<?php echo"
<div class=\"add_entry\">Medium: </div>					
				<div class=\"add_entry_element\">".$event_medium."</div>
					<div class=\"add_entry_element\">
					<span style=\"color:red;\">Change to: </span>
						<select name=\"Medium\">"; 
						while ($row = mysql_fetch_array($getMediums_results)) 
						{ 
						extract($row); 
						echo "<option value='$MediumIDPK' selected=\"selected\">$MediumName</option>"; 
							} 
						echo "</select>
					</div>";?>

Sorry about the post title guys, didnt know how to condense it to fit!

 

[feherke]

Hi

You have to add the [tt]selected[/tt] attribute only to the [tt]option[/tt] which has to be selected :

[navy]$res[/navy][teal]=[/teal]mysql_fetch_assoc[teal]([/teal][navy]$getEvent_res[/navy][teal]);[/teal]

[b]while[/b] [teal]([/teal][navy]$row[/navy] [teal]=[/teal] mysql_fetch_array[teal]([/teal][navy]$getMediums_results[/navy][teal]))[/teal] [teal]{[/teal]
  extract[teal]([/teal][navy]$row[/navy][teal]);[/teal]
  [b]echo[/b] [green][i]"<option value='$MediumIDPK'"[/i][/green][teal].([/teal][navy]$MediumName[/navy][teal]==[/teal][navy]$res[/navy][teal][[/teal][green][i]'MediumName'[/i][/green][teal]]?[/teal][green][i]' selected="selected"'[/i][/green][teal]:[/teal][green][i]''[/i][/green][teal]).[/teal][green][i]">$MediumName</option>"[/i][/green][teal];[/teal]
[teal]}[/teal]

Note that the [tt]mysql_fetch_assoc()[/tt] call will fetch the first tuple, so you should not add that to your code, use your existing code to fetch it. Just make sure it is executed before the [tt]while[/tt] loop. And of course, you will have to modify the ternary operator's condition.


Feherke.

http://rootshell.be/~feherke/

 

[southbeach]

Further, simplify and generalize your code by writing UDFs which could be called and re-use your code.

Here is a snippet I use to do what you want to do.

function reftableselect($table,$value,$selectname,$orderby,$keyfield,$width='',$onchange='',$matching='') {
	$sql = 'SELECT * FROM '.$table.' WHERE 1 ORDER BY `'.$orderby.'`;';
	if ($matching != '') $sql = 'SELECT * FROM '.$table.' WHERE '.$keyfield.' = "'.$matching.'";';
	$query = getQuery($sql);
	if ($onchange != '') $onchange = ' onchange="'.$onchange.'(this.value);" ';
	if($width != '') $style = ' style="width: '.$width.';" ';
	$string = '
	<select name="'.$selectname.'" id="'.$selectname.'" '.$onchange.$style.'>
	  <option value="0">Select One!</option>';
	while ($row = mysql_fetch_assoc($query)) {
		$selected='';
		if ($row[$keyfield] == $value) $selected = ' selected ';
		$string .= '<option value="'.$row[$keyfield].'" '.$selected.'>'.$row[$orderby].'</option>';
	}
	$string .= '</select>';
	return ($string);
}

The parameters explained:
[COLOR=red yellow]$table[/color]
Table name to extract data from
[COLOR=red yellow]$value[/color]
Pre-Selected value for the combo box
[COLOR=red yellow]$selectname[/color]
What would you like to call the combo box
[COLOR=red yellow]$orderby[/color]
Name of table's column in which you want to order
[COLOR=red yellow]$keyfield[/color]
Name of table's column to use a key field
[COLOR=red yellow]$width=''[/color]
Combo box width - defaults to liquid
[COLOR=red yellow]$onchange=''[/color]
If you want to trigger a JS on change, name it here
[COLOR=red yellow]$matching=''[/color]
If you want to load rows matching a specific value, this should be that value

Notice getQuery(), this is also an UDF used to execute the query itself - This way, I do not have to type the entire mysql string every time. In addition to that, this method gives me a central place where all query are launched thus giving me the ability to go to a single place and edit things such as logging and error trapping code.

Hope you find this useful!

Regards,


--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.