Retrieve Data from DB to Drop down box

[viktex1d]

I am working on a small form that retrieves and stores values to a DB. I want to retrieve the contents of a table and use it in a drop down box. The code I have used is:

 

<?php
// Connect to the db.
require ('mysqli_connect.php');

// Make the query:
$q = "SELECT employee_name from employee where dept_id=3 ORDER BY employee_id ASC";

// Run the query.
$r = mysqli_query ($dbc, $q);

if ($r) // If it ran OK, display the records.
{
echo '<select name="employee_name">';

// Fetch and print all the records:
while ($row = mysqli_fetch_array($r))
{
echo '<option value="'.$row['employee_name'] . '>"'.$row['employee_name'] .'</option>';

}
echo "</select>";

}

mysqli_free_result ($r); // Free up the resources.
mysqli_close($dbc); // Close the database connection.
?>

 

 

The code works perfectly in MySQL console. But, it returns an empty drop down box in the webpage. Guys, please help.

[Psycho]

If you were to look at the HTML source code that is created from the above it would become obvious what the problem is. The options are getting created something like this

<option value="Bob Smith>"Bob Smith</option>

Note the closing double quote and '>' at the end of the opening OPTION tag. This is why I use double quotes to define my strings and put the variables inside the string instead of concatenating them - it makes it easier to see what the output would be.

 

Also, you should be using the employee_id as the VALUE of the options and the name as the label/text. Plus, I don't know why you would order the values based on the ID since that would not be of any value to the user. Lastly, it's best to put the logic (i.e. PHP at the top of the page [or in a separate file]) and then only output the results in the HTML code. Makes it much easier to maintain.

 

 

<?php

## All of this code should be in the top of the script, not in the output of the page.

// Connect to the db.
require ('mysqli_connect.php');
// Create and run the query:
$query = "SELECT employee_id, employee_name FROM employee WHERE dept_id=3 ORDER BY employee_name ASC";
$result = mysqli_query ($dbc, $query);

if(!$result)
{
    //Query failed - add some error handling here
}
else
{
    //Create the options for the select list
    $staffOptions = '';
    while ($row = mysqli_fetch_array($result))
    {
        $staffOptions .= "<option value='{$row['employee_id']}'>{$row['employee_name']}</option>\n";
    }
}

mysqli_free_result ($r); // Free up the resources.
mysqli_close($dbc); // Close the database connection.
?>

<!-- this goes down in the HTML code for your page -->
<select name="employee_name">
<?php echo $staffOptions; ?>
</select>

Edited March 24, 2014 by Psycho

[viktex1d]

Thank you very much, I understand the mistake.