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, Jan 31 '14 at 15:32

Phil Cross · Answer 1 · 2014-01-31T15:41:05.683

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>

`mysql_real_escape_string()` is a better idea, but building SQL queries is not a great idea. — , Jan 31 '14 at 15:35
@josh I don't understand! Whats not great about building SQL queries? — Phil Cross, Jan 31 '14 at 15:37
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. ;-) — Strawberry, Jan 31 '14 at 15:44

score 1 · Accepted Answer · edited May 23 '17 at 11:56

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 '17 at 11:56

Community

1
1

answered Jan 31 '14 at 15:31

I agree about SQL injections , I will looking into that! – kaulainais Jan 31 '14 at 15:39
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. – Phil Cross Jan 31 '14 at 16:01
$_GET["id"] is much faster than $_GET[id] – Kumar V Jan 31 '14 at 16:12
@kumar_v $_GET['id'] with single quotes, although insignificantly, rather than double quotes, however specifying ID without quotes is simply an error! – Phil Cross Jan 31 '14 at 16:14

score 1 · Answer 3 · edited Jan 31 '14 at 15:47

1

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

edited Jan 31 '14 at 15:47

Peter Bloomfield

5,578
26
37

answered Jan 31 '14 at 15:36

You probably want to remove the `echo` there. – scenia Jan 31 '14 at 15:38
@scenia Ta, missed that :) – Jan 31 '14 at 15:40
+1 for ternary operator btw, that's what I would have posted if I had understood the question in the first place :D – scenia Jan 31 '14 at 15:41

score 0 · Answer 4 · answered Jan 31 '14 at 15:32

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>';
}

Dale · Answer 5 · 2014-01-31T15:52:58.747

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 '14 at 15:52

answered Jan 31 '14 at 15:33

Dale

10,384
21
34

1

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"`. – scenia Jan 31 '14 at 15:36
Ah of course it does ! Overlooked that very important detail, thanks for pointing it out :) – Dale Jan 31 '14 at 15:52

PHP HTML select box selected from MYSQL

5 Answers5