How do I populate Select menu options from SQL database using Bootstrap framework and PHP?

Question

I am creating a simple form that has a Select menu that will need to be populated using a SQL query that identifies all the unique values from single table and single column.

 <!DOCTYPE html>
 <html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Bootstrap 101 Template</title>

    <!-- Bootstrap -->
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/custom.css" rel="stylesheet">
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css">

  </head>
  <body>

This is an example of the Select menu that I want...

<form class="form-horizontal" role="form" method="post" action="db_test_bs.php">
 <div class="form-group">
   <label for="inputLocation" class="col-sm-2 control-label">Location</label>
     <div class="col-sm-4">
       <select class="form-control" id="inputLocation" name="inputLocation">
          <option>Smith</option>
          <option>Henry</option>
          <option>Jackson</option>
          <option>Hamilton</option>
        </select>
      </div>
    </div>

This is the Select menu that I am trying to populate using a query...

<form class="form-horizontal" role="form" method="post">
  <div class="form-group">
    <label for="inputLocation" class="col-sm-2 control-label">Location</label>
      <div class="col-sm-4">
        <select class="form-control" id="inputLocation" name="inputLocation">
        </select>

    <?php
      $servername = "localhost";
      $username = "owner_1";
      $password = "owner_1";
      $dbname = "test1";

    // Create Connection
      $conn = mysqli_connect($servername, $username, $password, $dbname);

    // Check connection
      if (!$conn) {
          trigger_error("Connection failed: " . mysqli_connect_error());
          }
    //Run Query
      $stmt = "SELECT DISTINCT `CUST_NAME` FROM `customer` WHERE 1";
      $result = mysqli_query($conn,$stmt) or die(mysqli_error());
        while(list($category) = mysqli_fetch_row($result)){
          $option = '<option value="'.$category.'">'.$category.'</option>';
          echo ($option);
        }

      mysqli_close($conn);
   ?>

     </div>
  </div>
</form>

    <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
     <!-- Include all compiled plugins (below), or include individual files as needed -->
    <script src="js/bootstrap.min.js"></script>
  </body>
</html>

The result from the code above is two Select menus. One that is static options that are hard coded and the other Select menu does not contain any options.

Using WAMP and BootStrap 3.
I am new to web dev so any help is GREATLY appreciated.

why are you echoing your echo ($option); after you close your select </select>? — Sean
– Sean, Commented Feb 5, 2015 at 3:49

Jun Rikson · Accepted Answer · 2015-02-05 03:48:27Z

1

It should be :

<select class="form-control" id="inputLocation" name="inputLocation">
...
//Your PHP Code
...
</select>

Note : You not really need WHERE 1 in your query for this case. You can echo directly the result without asiggn it into variable Like this :

echo '<option value="'.$category.'">'.$category.'</option>';

answered Feb 5, 2015 at 3:48

Jun Rikson

1,9041 gold badge22 silver badges43 bronze badges

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

9 Comments

rca8333 Over a year ago

Thank you for the input. I made these changes, but the menu is still blank.

Jun Rikson Over a year ago

What menu? do you mean echo $category didn't give any result? Have you checked your query in mysql panel or echo $category without <option> ?

rca8333 Over a year ago

Correct, echo $category did not give a result. To test the PHP and SQL query, saved the PHP as a seperate file and added the following code to the html file. <form class="form-horizontal" role="form" action="select_option.php"> <input type = "submit" value = "ExecuteQuery"/> </form>

rca8333 Over a year ago

My comment above timed out... The added input executes the php file without error, returning the proper query results, but on the [link] (localhost/...select_option.php) page rather than options of the Select. I believe that this adequately tests the php and the query. What else can I do to troubleshoot?

Jun Rikson Over a year ago

There is nothing wrong with your code except I listed above. i.imgur.com/bk3AEUZ.png Did you get any error? Are you sure your exact query give you results in mysql panel?

|

Michael Coleman · Accepted Answer · 2015-02-05 05:14:13Z

0

(as mentioned by @Sean) You you have closed off your <select></select> tag before the PHP, so your PHP loop is running out side of the <select> element

Also as a best practice you should fix your mysqli_error() statement by passing the $conn variable into the mysqli_error function like this:

$result = mysqli_query($conn,$stmt) or die(mysqli_error($conn));

Note: You should have PHP error reporting on in a dev environment.

Here is updated code

<form class="form-horizontal" role="form" method="post">
    <div class="form-group">
        <label for="inputLocation" class="col-sm-2 control-label">Location</label>
        <div class="col-sm-4">
            <select class="form-control" id="inputLocation" name="inputLocation">

            <?php
               $servername = "localhost";
               $username = "owner_1";
               $password = "owner_1";
               $dbname = "test1";

            // Create Connection
            $conn = mysqli_connect($servername, $username, $password, $dbname);

            // Check connection
            if (!$conn) {
                trigger_error("Connection failed: " . mysqli_connect_error());

            }
            //Run Query
            $stmt = "SELECT DISTINCT `CUST_NAME` FROM `customer` WHERE 1";
            $result = mysqli_query($conn,$stmt) or die(mysqli_error($conn));
            while(list($category) = mysqli_fetch_row($result)){
                echo '<option value="'.$category.'">'.$category.'</option>';
            }


            mysqli_close($conn);
            ?>

            </select>

        </div>
    </div>
</form>

</body>
</html>

edited Feb 5, 2015 at 5:14

answered Feb 5, 2015 at 4:06

Michael Coleman

3,4183 gold badges21 silver badges19 bronze badges

2 Comments

Jun Rikson Over a year ago

your username, password and database has been changed.

Michael Coleman Over a year ago

thanks for pointing that out. I have updated now. I created a temporary table and user to test my code against. So I can confirm this code works. If the OP's code still isnt working for them, then I would suspect the problem lies somewhere else.

Collectives™ on Stack Overflow

How do I populate Select menu options from SQL database using Bootstrap framework and PHP?

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related