PHP HTML select box selected from MYSQL

Question

A simple code that inserts a list of teams in select box. I would like to set SELECTED team with a id , that is in HREF

http://localhost/teams.php?id=7&years=2011&cups=8   

<?php
    $query = "select distinct t.team_id,t.team from teams t,years y,cups c where t.team_id=c.team_id and y.year_id=$_GET[years] and c.cup_id=$_GET[cups] ORDER BY t.team ASC";
    $res   = mysql_query($query);
    $option = '';

    while($row = mysql_fetch_assoc($res))
    {
        $option .= '<option value = "'.$row['team_id'].'">'.$row['team'].'</option>';
    }
?>

<form>
    <select id="tteam" name="team">
        <?php echo $option; ?>
    </select>
</form>

The problem is that I set team_id=$_GET[id], it shows only one team. I want the team=7 to be selected, but others still be showing in select box

That's one problem, yes. A bigger problem is that your code is wide open to SQL injection attacks. — David
– David, Commented Jan 31, 2014 at 15:32

Phil Cross · Accepted Answer · 2014-01-31 15:41:05Z

3

1st of all, NEVER EVER insert raw data into an SQL query. You are asking for SQL injections. Secondly, you're missing quotes around your $_GET variables, for example, in your SQL query, you currently access id by using $_GET[id]. This won't work, encapsulate id in quotes, like $_GET['id']. Thirdly, ESCAPE your data!!

mysql_* functions are now deprecated. You shouldn't be using them in new code. Instead, look into PDO or MySQLi functionality. Also look into prepared queries.

This should be your code:

<?php
   $years = mysql_real_escape_string($_GET['years']);
   $cups = mysql_real_escape_string($_GET['cups']);

    $query = "SELECT distinct t.team_id, vt.team 
              FROM teams t,years y,cups c 
              WHERE t.team_id = c.team_id 
                  AND y.year_id = '{$years}' 
                  AND c.cup_id = '{$cups}' 
              ORDER BY t.team ASC";

    $res   = mysql_query($query);
    $option = '';

    while($row = mysql_fetch_assoc($res))
    {
        // The line below specifies whether the option should be selected.
        $selected = $row['team_id']==$_GET['id'] ? 'selected="selected"' : '';

        $option .= '<option ' . $selected . ' value= "' . $row['team_id'] . '">' . $row['team'] . '</option>';
    }
?>

<form>
    <select id="tteam" name="team">
        <?php echo $option; ?>
    </select>
</form>

edited Jan 31, 2014 at 15:41

answered Jan 31, 2014 at 15:34

Phil Cross

9,33212 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3141031 Over a year ago

mysql_real_escape_string() is a better idea, but building SQL queries is not a great idea.

Phil Cross Over a year ago

@josh I don't understand! Whats not great about building SQL queries?

Strawberry Over a year ago

These functions are deprecated. They may, in consequence, have lost some of their material value. Alternatively, their scarcity may serve to enhance their value; I just don't know. ;-)

Phil Cross Over a year ago

Lol, I still have problems pronouncing it for some reason!

Community · Accepted Answer · 2017-05-23 11:56:06Z

1

Please be aware that you're vulnerable to SQL injections. See: How can I prevent SQL injection in PHP?

With that said, you need to use a conditional statement that compares $row["team_id"] with $_GET["ID"].

while($row = mysql_fetch_assoc($res))
{
if($row["team_id"] == $_GET["ID"])
    $option .= '<option value = "'.$row['team_id'].'" selected="selected">'.$row['team'].'</option>';
else
    $option .= '<option value = "'.$row['team_id'].'">'.$row['team'].'</option>';
}

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jan 31, 2014 at 15:31

user3141031

4 Comments

kaulainais Over a year ago

I agree about SQL injections , I will looking into that!

Phil Cross Over a year ago

There's an error in this code. In your if condition, you're using $_GET[ID]. If ID is an undefined constant, this will result in a PHP Notice.

Kumar V Over a year ago

$_GET["id"] is much faster than $_GET[id]

Phil Cross Over a year ago

@kumar_v $_GET['id'] with single quotes, although insignificantly, rather than double quotes, however specifying ID without quotes is simply an error!

Peter Bloomfield · Accepted Answer · 2014-01-31 15:47:15Z

1

while($row = mysql_fetch_assoc($res))
{
    $option .= '<option value = "'.$row['team_id'].'" '.($row['team'] == 7 ? 'selected="selected"': '').'>'.$row['team'].'</option>';
}

edited Jan 31, 2014 at 15:47

Peter Bloomfield

5,81628 silver badges38 bronze badges

answered Jan 31, 2014 at 15:36

user849001

3 Comments

scenia Over a year ago

You probably want to remove the echo there.

user849001 Over a year ago

@scenia Ta, missed that :)

scenia Over a year ago

+1 for ternary operator btw, that's what I would have posted if I had understood the question in the first place :D

Kumar V · Accepted Answer · 2014-01-31 15:32:19Z

0

Compare your id from $_GET with $row['team_id'].

while($row = mysql_fetch_assoc($res))
{
if($row['team_id'] == $_GET["id"])
    $option .= '<option value = "'.$row['team_id'].'" selected="selected">'.$row['team'].'</option>';
else
    $option .= '<option value = "'.$row['team_id'].'">'.$row['team'].'</option>';
}

answered Jan 31, 2014 at 15:32

Kumar V

8,8289 gold badges44 silver badges60 bronze badges

Comments

Dale · Accepted Answer · 2014-01-31 15:52:58Z

0

I'll just focus on the loop part:

while($row = mysql_fetch_assoc($res))
{
    $selected = (isset($_GET['team_id']) && $row['team_id'] == $_GET['team_id']) ? 'selected' : '';
    $option .= '<option value = "'.$row['team_id'].'" selected="'. $selected .'">'.$row['team'].'</option>';
}

edited Jan 31, 2014 at 15:52

answered Jan 31, 2014 at 15:33

Dale

10.5k23 silver badges34 bronze badges

2 Comments

scenia Over a year ago

This won't work. It sets selected="" on all option except the one supposed to be selected, which will be selected="7" if he passes 7. The correct syntax would be selected="selected".

Dale Over a year ago

Ah of course it does ! Overlooked that very important detail, thanks for pointing it out :)

Collectives™ on Stack Overflow

PHP HTML select box selected from MYSQL

5 Answers 5

4 Comments

4 Comments

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

4 Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related