Display Mysql table field values in Select box

Question

I want to display the Mysql table Filed values in selectbox. I tried the following code to display. But it normally display the specified field values in echo function and not in select box. I don't know where I mistake.

 $con = mysql_connect("localhost","root","root");
 $db = mysql_select_db("Time_sheet",$con);
 $get=mysql_query("SELECT Emp_id FROM Employee");
 while($row = mysql_fetch_assoc($get))
{
echo ($row['Emp_id']."<br/>");
}

<html>
<body>
<form>
 <select> 
<option value = "<?php echo($row['Emp_id'])?>" ><?php echo($row['Emp_id']) ?></option>
</select>
</form>
</body>
</html>

Also the field values must be display in ascending order. How to achieve..

in your query, add `ORDER BY` clause, example: `SELECT Emp_id FROM Employee ORDER BY Emp_ID ASC` — John Woo, Jan 17 '13 at 05:46

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

17

<?php
$con = mysql_connect("localhost","root","root");
 $db = mysql_select_db("Time_sheet",$con);
 $get=mysql_query("SELECT Emp_id FROM Employee ORDER BY Emp_id ASC");
$option = '';
 while($row = mysql_fetch_assoc($get))
{
  $option .= '<option value = "'.$row['Emp_id'].'">'.$row['Emp_id'].'</option>';
}
?>
<html>
<body>
<form>
 <select> 
<?php echo $option; ?>
</select>
</form>
</body>
</html>

PS : On a sidenote, please stop using mysql_* functions. Take a look at this thread for the reasons.

edited May 23 '17 at 11:46

Community

1
1

answered Jan 17 '13 at 05:56

asprin

9,579
12
66
119

There is no reason to declare a `value` attribute to the options if they are the same as the visible text. – mickmackusa Jan 22 '19 at 10:45

score 2 · Answer 2 · answered Jan 17 '13 at 05:55

You can easily do it like this

$con = mysql_connect("localhost","root","root");
$db = mysql_select_db("Time_sheet",$con);
$get=mysql_query("SELECT Emp_id FROM Employee");

<html>
<body>
<form>
    <select> 
    <option value="0">Please Select</option>
        <?php
            while($row = mysql_fetch_assoc($get))
            {
            ?>
            <option value = "<?php echo($row['Emp_id'])?>" >
                <?php echo($row['Emp_id']) ?>
            </option>
            <?php
            }               
        ?>
    </select>
</form>
</body>
</html>

There is no reason to declare a `value` attribute to the options if they are the same as the visible text. — mickmackusa, Jan 22 '19 at 10:46

score 0 · Answer 3 · answered Jan 17 '13 at 05:55

You have to use while loop to display option in select box. try this ...

 $con = mysql_connect("localhost","root","root");
 $db = mysql_select_db("Time_sheet",$con);
 $get=mysql_query("SELECT Emp_id FROM Employee order by Emp_id");

 <html>
 <body>
  <form>
    <select>
    <?php
     while($row = mysql_fetch_assoc($get))
     {
    ?>
      <option value="<?php echo $row['Emp_id']; ?>"><?php echo $row['Emp_id']; ?></option>
    <?php
     }
    ?>
    </select>
  </form>
 </body>
 </html>

score 0 · Answer 4 · answered Jan 17 '13 at 06:02

0

<?php
  $con = mysql_connect("localhost","root","root");
  $db = mysql_select_db("Time_sheet",$con);
  $get=mysql_query("SELECT Emp_id FROM Employee");
  ?>
 <html>
 <body>
    <form>
        <select> 
         <?php
             while($row = mysql_fetch_assoc($get)){?>
                <option value = "<?php echo($row['Emp_id'])?>" ></option>
                <?php } ?>
        </select>
    </form>
    </body>

answered Jan 17 '13 at 06:02

Deepak Srinivasan

784
6
17

Code-only answers are low value on Stackoverflow. – mickmackusa Jan 22 '19 at 10:49

score 0 · Answer 5 · answered Aug 24 '17 at 09:55

0

<?php
 $con = mysql_connect("localhost","root","root");
 $db = mysql_select_db("Time_sheet",$con);
 $res=mysql_query("SELECT Emp_id FROM Employee");
?>

<html>
 <body>
  <form>
   <select>
    <?php
      while ($row = $res->fetch_assoc()) 
      {
        echo '<option value=" '.$row['id'].' "> '.$row['name'].' </option>';
      }
    ?>
    </select>
   <form>
  </body>
 </html>

answered Aug 24 '17 at 09:55

rashedcs

3,588
2
39
40

We shouldn't be seeing answers with `mysql_` in 2017. Code-only answers are low value on Stackoverflow. – mickmackusa Jan 22 '19 at 10:48
Thank you for your valuable feedback... I will improve myself. – rashedcs Jan 22 '19 at 11:45
Be sure not to mix object-oriented and procedural `mysqli` syntax when you edit. – mickmackusa Jan 22 '19 at 11:46

score 0 · Answer 6 · answered Jan 22 '19 at 22:08

There a few tips to offer that will condense the code block for this task -- too many to just comment under the accepted answer.

Tested Code:

if (!$con = new mysqli("localhost", "root", "root", "Time_sheet")) {
    echo "Database Connection Error: " , $con->connect_error;  // don't show actual error in "production" stage
} elseif (!$result = $con->query("SELECT Emp_id FROM Employee ORDER BY Emp_id")) {
    echo "Syntax Error: " , $con->error;  // don't show actual error in "production" stage
} else {
    echo "<select>";
    foreach ($result as $row) {
        echo "<option>{$row['Emp_id']}</option>";
    }
    echo "</select>";
}

The if line is both declaring $con and checking it for a falsey return value in one step.
Never provide the raw connection or query errors to your users. It is okay during development, but you don't want strangers to have access to critical/informative errors that your server will provide. This is a basic best practice for security.
The elseif line is both declaring $result and checking it for a falsey return value in one step.
The else block will process the zero or more rows of data that were generated as a result of the successful SELECT query.
The mysqli result is "traversable" which means you can iterate it using foreach() and enjoy direct access to the row data via associative keys. PHP Manual Reference Another StackOverflow Post
When writing an array element into a literal string via interpolation, it is good practice (though not always necessary) to wrap the element in curly braces. This will often trigger helpful highlighting in IDEs and ensure that the characters that follow the array are not accidentally coupled to the variable name itself.
You ONLY need to write a value attribute inside of the <option> tag IF the value is different from text between the opening and closing tags (<option>the text in here</option>). Form submissions and javascript implementations will all work the same if you omit the redundant value attribute.
If you DO wish to submit a different value in the select field rather than the text, here is what the syntax can look like:
```
echo "<option value=\"{$row['Emp_id']}\">{$row['Emp_name']}</option>";
```
If you want to write a selected attribute on one of the options based on a pre-existing variable (e.g. $selected_id), it can look like this:
```
echo "<option" , ($row['Emp_id'] == $selected_id ? " selected" : "") , ">{$row['Emp_name']}</option>";
```

If you wanted to combine the two previous processes, it can look like this:

echo "<option value=\"{$row['Emp_id']}\"" , ($row['Emp_id'] == $selected_id ? " selected" : "") , ">{$row['Emp_name']}</option>";

Display Mysql table field values in Select box

6 Answers6

Linked