Using PHP and SQL to populate DropDown options

  • Profile Image
    ece31316
    Asked on August 28, 2015 at 10:13 AM
    Hi, sorry im new to php coding and html as a whole(familiar with visual studio) can you please explain where would i place the script inside the form? Thank you
  • Profile Image
    Boris
    Answered on August 28, 2015 at 10:37 AM

    The thread where you responded to is quite old, and it uses PHP to create the entire form, but the techniques are still valid. Please take note of his explanation part, which holds the necessary codes and their explanation:

    require 'conn.php'; = Your database connection configuration

     

    $filter=mysql_query("select dropdown_option from dropdown_db"); = MySQL query to select the data from the database name.

     

    while($row mysql_fetch_array($filter)) = this part is where you fetch the value to be displayed on your dropdown option.

    The first two lines can be anywhere inside your PHP code, preferably near the top. They are the ones connecting to your database to pull information which you will be using in your Drop Down field.

    The third line would go similarly to how it is posted in his script:

    // Add options to the drop down
    $menu=" ";
    while($row = mysql_fetch_array($filter))

     

    {
      $menu .="<option>" . $row['dropdown_option'] . "</option>";
    }

     

    // Close menu form
    $menu = "</select></form>";

     

    // Output dropdown menu
    echo $menu;

    The part of your HTML where you would place this, is inside the SELECT element - which is your Drop Down. Say your Source Code for the Drop Down field is originally this:

              <select class="form-dropdown" style="width:150px" id="input_5" name="q5_listOf">
                <option value="">  </option>
                <option value="Alan"> Alan </option>
                <option value="Ben"> Ben </option>
                <option value="Chris"> Chris </option>
                <option value="Donald"> Donald </option>
                <option value="Edgar"> Edgar </option>
                <option value="Fritz"> Fritz </option>
                <option value="Gomez"> Gomez </option>
                <option value="Huan"> Huan </option>
                <option value="Ian"> Ian </option>
                <option value="Jon"> Jon </option>
                <option value="Kally"> Kally </option>
                <option value="Liam"> Liam </option>
              </select>

    You would modify it with the code above, like this:

              <select class="form-dropdown" style="width:150px" id="input_5" name="q5_listOf">
    <?php
    require
    'conn.php';

    $filter=mysql_query("select dropdown_option from dropdown_db");
    $menu=" ";

    // Add options to the drop down
    while($row = mysql_fetch_array($filter))

     

    {
      $menu .="<option>" . $row['dropdown_option'] . "</option>";
    }

     

    // Close menu form
    $menu = "</select></form>";

     

    // Output dropdown menu
    echo $menu;

    ?>
              </select>

    I hope this helps.

  • Profile Image
    ece31316
    Answered on August 28, 2015 at 10:37 PM

    Hi, 

     

    Thanks for the reply, but i'm getting this displayed under the dropdown ""; // Output dropdown menu echo $menu; ?>"

  • Profile Image
    ece31316
    Answered on August 28, 2015 at 11:09 PM

    here is where im applying the code kindly check,

     

           <select class="form-dropdown validate[required]" style="width:150px" id="input_43" name="q43_problemType43">

                     <?php

                        

                       $conn=odbc_connect("MyConn",'','');

                        $sql="Select ProbType from  ProbMaster";

                        $rs = odbc_exec($conn,$sql);

                        $menu=" ";

     

                       // Add options to the drop down

                         while($row =odbc_fetch_row($rs))

                         {

                         $menu .="<option>" . $row['ProbType'] . "</option>";

                         }

                          // Close menu form

                         $menu = "</select></form>";

                         // Output dropdown menu

                         echo $menu;

                      ?>

              </select>

  • Profile Image
    Boris
    Answered on August 29, 2015 at 05:07 AM

    Uh, I am sorry, I accidentally copied the part of the code that he used to close the form - his old code was used to create the entire form, together with opening and closing FORM tags.

    Please try removing this part from the code, which is closing the form prematurely, and let us know how it goes:

    // Close menu form
    $menu = "</select></form>";

  • Profile Image
    ece31316
    Answered on September 08, 2015 at 02:41 AM

    Hi, I still cannot make it work.. this is the code i used

     

     <select class="form-dropdown validate[required]" style="width:150px" id="input_43" name="q43_problemType43">

               <?php

                    $conn=odbc_connect("MyConn",'','');

                      $sql="Select ProbType from  ProbMaster";

                      $rs = odbc_exec($conn,$sql);

                       $menu=" "; 

     

                      while (odbc_fetch_row($rs))

                      {

                       $menu .="<option>" .odbc_result($rs,"ProbType") . "</option>";

                       }

                      echo $menu;

    ?> 

     </select>

     

    I tried the code above and saving it to php it perfectly returns the value i expected here is the converted php code

    <?php

     echo "<select class='form-dropdown validate[required]' style='width:150px' id='input_43' name='q43_problemType43'>";

           $conn=odbc_connect("MyConn",'','');

           $sql="Select ProbType from  ProbMaster";

           $rs = odbc_exec($conn,$sql);

           $menu=" "; 

           while (odbc_fetch_row($rs))

           {

               $menu.= '<option>'. odbc_result($rs,"ProbType").'</option>';

           }

           echo $menu;

           odbc_close($conn);

            echo "</select>";

    ?>

     

    Please help...

  • Profile Image
    Sammy
    Answered on September 08, 2015 at 04:12 AM

    Kindly use the following code, you had forgotten to assign the value in the option tag within the loop

     <select class="form-dropdown validate[required]" style="width:150px" id="input_43" name="q43_problemType43">
               <?php
                    $conn=odbc_connect("MyConn",'','');
                      $sql="Select ProbType from  ProbMaster";
                      $rs = odbc_exec($conn,$sql);
                       $menu=" ";

                      while (odbc_fetch_row($rs)){
                       $menu .= "<option value=".odbc_result($rs,'ProbType').">" . odbc_result($rs,'ProbType'). "</option>";

                       }
                      echo $menu;

    odbc_close($conn);

    ?>
     </select>

    Please let us know if you need additional assistance

  • Profile Image
    ece31316
    Answered on September 08, 2015 at 04:22 AM

    i also have tried that.. but it still returns blank.. =(

  • Profile Image
    Sammy
    Answered on September 08, 2015 at 05:59 AM

    The issue seems to be in your database connection, kindly run it independently and see if it throws any errors regarding the database drivers.

    What database are you connecting to? I have written an example for a MySql database below, kindly let us know your database type so we can be able to advice accordingly.

     <select class="form-dropdown validate[required]" style="width:150px" id="input_43" name="q43_problemType43">
               <?php
    $servername = "localhost";
    $username = "root";
    $password = "root";

    // Create connection
    $conn = new mysqli($servername, $username, $password);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    mysqli_select_db($conn,"trials"); //set the database name

                       $menu=" ";
                      
                      $sql="SELECT ProbType FROM probmaster"; //selection query
                      $rs = mysqli_query($conn, $sql);//odbc_exec($conn,$sql);

                      
                       if (mysqli_num_rows($rs) > 0) {
        // output data of each row
        while($row = mysqli_fetch_assoc($rs)) {
                         $menu .= "<option value=".$row['ProbType'].">" . $row['ProbType']. "</option>";
        }
    }

                      echo $menu;
                     
                      mysqli_close($conn);

    ?>
    </select>

     

  • Profile Image
    ece31316
    Answered on September 08, 2015 at 09:03 PM

    Im running this exact same code in php and it works fine, i dont know why it desnt when it is in html.. Im connectiong to a MSSQL Database via odbc connection, hope you can help me sort things out.. thanks..

     

    <?php

     echo "<select class='form-dropdown validate[required]' style='width:150px' id='input_43' name='q43_problemType43'>";

           $conn=odbc_connect("MyConn",'','');

           $sql="Select ProbType from  ProbMaster";

           $rs = odbc_exec($conn,$sql);

           $menu=" "; 

           while (odbc_fetch_row($rs))

           {

               $menu.= '<option>'. odbc_result($rs,"ProbType").'</option>';

           }

           echo $menu;

           odbc_close($conn);

            echo "</select>";

    ?>

  • Profile Image
    Charlie
    Answered on September 09, 2015 at 02:17 AM

    Perhaps you can share us the file where your code is or the complete code using Pastebin?

    You need to put the PHP code in a .php file, if you are adding it on an .html file, it won't work, I believe. And you should run it on a server, either local or a web server, in our case, we're using XAMPP.

    For a MSSQL database connection for PHP, you can check this as a reference: http://php.net/manual/en/book.mssql.php