CODEWITHSUNDEEP
phptwitter-bootstrapmysqli
rca8333
rca8333

Reputation: 15

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

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.

Upvotes: 1

Views: 12112

Answers (2)

Michael Coleman
Michael Coleman

Reputation: 3398

(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>

Upvotes: 0

Jun Rikson
Jun Rikson

Reputation: 1884

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

Upvotes: 1

Related Questions