Populating Dropdown from Database

[tebrown]

Recently i've been working on a drop-down list that is populated with results from my database.

 

Ideally the code below would allow a manager to choose a player from the dropdown list and then save it. If the manager has already saved the lineup, and goes back to it, the player that he saved would populate that drop down automatically (so he doesn't have to choose him again).

 

At the moment, the code below is not populating the players from the database. Could you please have a look?

 

<td width="132" valign="top"><?

selectplayers="SELECT * FROM users WHERE club='" . $_SESSION['club'] . "' AND team='" . $_SESSION['team'] . "' AND role='Player' ORDER by name DESC";

$player=mysql_query($selectplayers);

while($rowplayer = mysql_fetch_array($player)) {
		$name=$rowplayer["name"];
}

$id = '<div id="showvalue"></div>';

$selectfixture="SELECT * FROM fixtures WHERE id='$id' AND club='" . $_SESSION['club'] . "' AND team='" . $_SESSION['team'] . "'";
$fixture=mysql_query($selectfixture);
while($rowfixture = mysql_fetch_array($fixture)) {

  $prop1= $rowfixture["prop1"];

}

echo '<select name="prop1"><option value="">Select A Player</option>';
	foreach($name as $o)  {
                        if(isset($_POST['prop1']) && $_POST['prop1'] == $o)
			{ $sel = 'selected="selected"'; }
			elseif(isset($_POST) && $o == $prop1)
			{ $sel = 'selected="selected"';}
			else{$sel='';}
			echo '<option '.$sel.'>'.$o.'</option>';
			}
			echo '</select>';

?></td>

 

Cheers guys much appreciated.

 

Tim

[darkfreaks]

check your  SQL for syntax errors

 

 

$player=mysql_query($selectplayers) or die(mysql_error());
$fixture= mysql_query($selectfixture) or die(mysql_error());

[Psycho]

You will do yourself a huge favor by separating the logic from the presentation in your code. makes it much easier to find and fix errors.

 

Having said that you should create a function to do this. Create a query to only get the fields you need and create a function to create the options list. You should be using an ID as the value of the options, but it's kind of hard to tell if that's the case above.

 

Below is some sample code to point you in the right direction

<?php

//Function to create options dynamically
function createOptions($optionsRS, $selectedValue)
{
    $optionsHTML = '';
    while($row = mysql_fetch_assoc($optionsRS))
    {
        $selected = ($row['id']==$selectedValue) ? ' selected="selected"' : '';
        $optionsHTML = "<option value='{$row['id']}'{$selected}>{$row['label']}</option>\n";
    }
    return $optionsHTML;
}
//Run query to get current selected value
$query = "SELECT obj_id as id
          FROM table_of_selections
          WHERE record_id = '{$_SESSION['record_id']}'
          LIMIT 1";
$result = mysql_query($query) or die*(mysql_error());
$selectedID = mysql_result($result, 0);

//Run query to get list of options (set 
$query = "SELECT obj_id as id, obj_name as name
          FROM table_of_options
          WHERE foo = '{$_SESSION['bar']}'
          ORDER BY name DESC";
$optionResults = mysql_query($query) or die(mysql_error());

$fieldOptions = createOptions($optionResults, $selectedID );


?>

<select name="field_name">
<?php echo $fieldOptions; ?>
</select>

[tebrown]

Thanks for your replys.

 

Ok i think the above code you posted relates to what im needing. Just to make sure ill explain.

 

Basically a manager gets a list of fixtures for upcoming games. He/ or she can then click 'create lineup', which then brings up a modal window so they can then pick their lineup. When the manager selects a player for each position he/ or she can then save the lineup, which would then close the modal window. If they decide go back into the modal window and edit the lineup, the players that he/ or she selected would automatically fill the select option.

 

Would the code above be ideal for this?

 

[tebrown]

In the mean time i had done this. But it seems to disappear/ lost when the page is loaded. Basically i want it to show the database result in the dropdown (if there is any). The manager can then choose a different player if he wanted to.

 

<td width="132" valign="top"><?

$conn = mysql_connect('localhost', 'root', 'root') or die("Couldn't connect to database.");
mysql_select_db('2012MP', $conn) or die("Error selecting database.");

$selectplayers="SELECT * FROM users WHERE club='Inglewood' AND team='Senior As' AND role='Player' ORDER BY name DESC";
$playerselected=mysql_query($selectplayers);

while($rowplayer = mysql_fetch_array($playerselected)) {
$name=$rowplayer["name"];
$full_name[] = $name;
}

$id = '<div id="showvalue"></div>'; 

$selectfixture= mysql_query("SELECT * FROM fixtures WHERE id='$id' AND club='Inglewood' AND team='Senior As'") or die(mysql_error());

while($rowfixture = mysql_fetch_array($selectfixture)) {
$prop1= $rowfixture["prop1"];





?>

<?

echo '<select name="prop1" style="width: 150px"><option value="">Select A Player</option>';
foreach($full_name as $o)
{
if(isset($_POST['prop1']) && $_POST['prop1'] == $o)
{$sel = 'selected="selected"';}
elseif(isset($_POST) && $o == $prop1)
{$sel = 'selected="selected"';}
else{$sel='';}
echo '<option '.$sel.'>'.$o.'</option>';
}
echo '</select>';

}

?></td>

[Christian F.]

The reason it disappears when the page is loaded, is because you're not saving it anywhere. You need to write the code, and tables, to store the lineup in the database.

 

Also, in addition to the comments I've given you on IRC already about coding practises, there's one obvious error in your code:

$id = '<div id="showvalue"></div>';

$query = "SELECT * FROM fixtures WHERE id='$id' AND club='Inglewood' AND team='Senior As'";

 

I've also taken the liberty of cleaning up your code a bit, and moved the PHP code top:

<?php

$conn = mysql_connect ('localhost', 'user', 'pass') or die ("Couldn't connect to database.");
mysql_select_db ('2012MP', $conn) or die ("Error selecting database.");

$selectplayers = "SELECT * FROM users WHERE club='Inglewood' AND team='Senior As' AND role='Player' ORDER BY name DESC";
$playerselected = mysql_query ($selectplayers);

while ($rowplayer = mysql_fetch_array ($playerselected)) {
$full_name[] = $rowplayer["name"];
}

$id = '<div id="showvalue"></div>';

$query = "SELECT * FROM fixtures WHERE id='$id' AND club='Inglewood' AND team='Senior As'";
$selectfixture = mysql_query ($query) or die (mysql_error ());

// Ready the output variable.
$output = '';

while ($rowfixture = mysql_fetch_array ($selectfixture)) {
$prop1 = $rowfixture["prop1"];

$output .= '<select name="prop1" style="width: 150px"><option value="">Select A Player</option>';
foreach ($full_name as $player) {
	if ((isset ($_POST['prop1']) && $_POST['prop1'] == $player) || $player == $prop1) {
		$sel = 'selected="selected"';
	} else {
		$sel = '';
	}

	// TODO: You need to set a value here, preferably using the ID of the player.
	$output .= '<option ' . $sel . '>' . $player . '</option>';
}

$output .= '</select>';
}

?>
<doctype html>
<html>
<head>
<title></title>

</head>
<body>

<!-- the HTML code -->

<td width="132" valign="top"><?php echo $output; ?></td>

<!-- Some more HTML code. -->

</body>
</html>

[tebrown]

Cheers ChristianF,

 

So i tried to create another $output but the $hooker doesn't seem to be in the select box.

 

<?php

$conn = mysql_connect ('localhost', 'root', 'root') or die ("Couldn't connect to database.");
mysql_select_db ('2012MP', $conn) or die ("Error selecting database.");

$selectplayers = "SELECT * FROM users WHERE club='Inglewood' AND team='Senior As' AND role='Player' ORDER BY name DESC";
$playerselected = mysql_query ($selectplayers);

while ($rowplayer = mysql_fetch_array ($playerselected)) {
$full_name[] = $rowplayer["name"];
}

$id = '24';

$query = "SELECT * FROM fixtures WHERE id='$id' AND club='Inglewood' AND team='Senior As'";
$selectfixture = mysql_query ($query) or die (mysql_error ());

// Ready the output variable.
$output = '';
$hooker = '';

while ($rowfixture = mysql_fetch_array ($selectfixture)) {
$prop1 = $rowfixture["prop1"];
$hooker = $rowfixture["hooker"];

$output .= '<select name="prop1" style="width: 150px"><option value="">Select A Player</option>';
foreach ($full_name as $player) {
	if ((isset ($_POST['prop1']) && $_POST['prop1'] == $player) || $player == $prop1) {
		$sel = 'selected="selected"';
	} else {
		$sel = '';
	}

	// TODO: You need to set a value here, preferably using the ID of the player.
	$output .= '<option ' . $sel . '>' . $player . '</option>';
}

$output .= '</select>';

$hooker .= '<select name="hooker" style="width: 150px"><option value="">Select A Player</option>';
foreach ($full_name as $player) {
	if ((isset ($_POST['hooker']) && $_POST['hooker'] == $player) || $player == $hooker) {
		$sel = 'selected="selected"';
	} else {
		$sel = '';
	}

	// TODO: You need to set a value here, preferably using the ID of the player.
	$hooker .= '<option ' . $sel . '>' . $player . '</option>';
}

$hooker .= '</select>';
}

?>

 

Cheers

[Christian F.]

If you look at these two lines, you might see the problem:

$hooker = $rowfixture["hooker"];
....
$hooker .= '<select name="hooker" style="width: 150px"><option value="">Select A Player</option>';

 

Also, as previously stated: You really want to be using JOINs for this.

[tebrown]

Thanks Christian. I changed the output variables to something else and defined the specific position. Will repeat this process for each position.

 

I will look into the JOIN aswell.

 

Cheers Guys

Tim