Populating a dropdown with MySQL data using PHP

NiallThistle

I've got quite far on with this and I thought I had it cracked, but I have now come up with a problem with multiple entries.

Basically, I am looking to populate a dropdown menu with a list of available football grounds that can be chosen. I have a table for grounds (lpro_grounds) and a table for teams (lpro_teams) and the id number of the ground each team uses is specified in a field in each team's entry.

I created this little script to populate the drop down menu when editing a team. The list should contain every ground in the database, which it does, but for some reason it displays each of the grounds twice.

Originally, I could get it to display a single entry for each one in the dropdown, but it wouldn't show the default ground for that team automatically (meaning that every time you edit a team, you'd have to reset that field to the ground it should be, even though you haven't changed it). I added the "get_existing_ground" query to make sure the first option was the existing ground - I was happy with that, but for some reason, the array below it displays three of each of the rest of the grounds. Not sure why! Can anyone help?

Here is an excerpt of my code:

<?php  

while($data = mysql_fetch_array($get_teams))
{

echo"  

<tr>
<td><input type=\"text\"  name=\"teamname\" size=\"25\"  value=\"$data[teamname]\"></td>
<td><input type=\"text\"  name=\"shortname\" size=\"15\"  value=\"$data[shortname]\"></td>
<td><select name=\"groundname\">";
}

		$get_existing_ground = mysql_query("
		SELECT G.ground_name as groundname,
		T.team_ground as teamground
		FROM lpro_teams T, lpro_grounds G
		WHERE T.team_id = '".$_GET["tid"]."'
		AND T.team_ground = G.ground_id
		",$objConnect);

		while($data = mysql_fetch_array($get_existing_ground))
		{
		echo"<option value=\"$data[teamground]\">$data[groundname]</option>";
		}

        $get_other_grounds = mysql_query("
        SELECT G.ground_name as groundname,
        G.ground_id as groundid
        FROM lpro_grounds G, lpro_teams T
		WHERE T.team_id <> '".$_GET["tid"]."'
        ",$objConnect);

        while($data = mysql_fetch_array($get_other_grounds))
        {
        echo"<option value=\"$data[groundid]\">$data[groundname]</option>";
        }
?>
</select></td>

NiallThistle

Here's a screenshot of the problem.

NiallThistle

Okay, I've had some progress, by using SELECT DISTINCT rather than SELECT:

// Loading and displaying the other available grounds for the dropdown

        $get_other_grounds = mysql_query("
        SELECT DISTINCT G.ground_name as groundname,
        G.ground_id as groundid
        FROM lpro_grounds G, lpro_teams T
		WHERE T.team_id != '".$_GET["tid"]."'
		ORDER BY G.ground_name ASC
        ",$objConnect);

This has eliminated duplicates. However, this line:

WHERE T.team_id != '".$_GET["tid"]."'

appears ineffective, as it was designed to avoid populating the array of drop down options with the team's current ground.

So, I now have a drop down that has the current ground as the default option, then once you click it, it has a full list of all grounds -- including the current ground AGAIN as a duplicate entry.

Any help would be greatly received.

Derokorian

you could do an if statement in your loop building the options if current groun is equal to the one being printed, add selected so its the selected option.

HTH

NiallThistle

Derokorian;10984141 wrote:
you could do an if statement in your loop building the options if current groun is equal to the one being printed, add selected so its the selected option.

HTH

Hi Derokorian, thanks for the response.

Do you have a sample of what that if statement would look like, in terms of checking to see if an option has already been printed?

Derokorian

$cur_grounds = 3; // i just picked something obviously you have to fill this with the right data to get it to work
while($data = mysql_fetch_array($get_existing_ground))
{
   echo "<option value=\"$data[teamground]\"";
   if( $data[teamground] == $cur_grounds )  echo " selected";
   echo ">$data[groundname]</option>";
}

And here is a function I wrote for outputting a drop down for days of the month, maybe it will be more helpful.

function daySelect($fieldname,$selected = 0) {
    // Prints 31 days
    $select = "<select name=\"{$fieldname}\" id=\"{$fieldname}\" size=\"1\">
        <option value=\"0\" selected=\"selected\">Day</option>\n";
    for( $i=1; $i<32; $i++ ) {
        $select .= "<option value=\"{$i}\"";
        if( $selected == $i ) {
            // If the set day is the one being printed, make it selected
            $select .= " selected";
        }
        $select .= ">{$i}</option>\n";
    }
    $select .= "</select>"; // close the select
    return $select;
}

HTH