using sql to populate dropdown choices

  • Profile Image
    skyrun
    Asked on October 11, 2013 at 01:57 PM

    sure would be nice to be able to use a sql statement as one of the 'special' choices to populate a dropdown box.  sql is used by most every database and it's pretty std to be able to connect remotely through an odbc connector where you can type your server ip/domain, userid and password and the sql and use the results to populate the options

    seems like a simple way to accomplish what several people are asking for on the forum.

    theoretically could be used on other fields too, not just the dropdown list.

  • Profile Image
    jedcadorna
    Answered on October 11, 2013 at 05:57 PM

    Hi,

    In order to achieve this you need to get the form's source code and make some modification using PHP and Sql or MySQL. I have a simple PHP script below that pulls data out from database and populates to your dropdown list. 

     

    <?php

    require 'conn.php';

     

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

     

    //Dropdown menu

    $menu="

    <form id='filter' name='filter' method='post' action=''>

      <p><label>Filter</label></p>

        <select name='filter' id='filter'>";

     

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

    ?>

     

     

    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.

     

    I will bookmark this thread and will try to make a sample of this. Once I'm done I will reply to you via this thread.

     

    Thanks,

  • Profile Image
    skyrun
    Answered on October 11, 2013 at 06:31 PM

    nice.  thanks for the detailed intructions (save an example of conn.php, but i can figure that out)  i can certainly see how this would work and it's nice to be able to get in there and hack in anything i want if i know php.

    the downside of course is that after you hack, you can no longer use the form builder after you've accomplished this using your technique (or at least you would have to re-do any changes each time you updated on the builder).  therefore would be MUCH nicer to include this in a future release as a 'special' drop down type so it would be doable right in the form builder.  than you write the php as above into the form and it's done...

    with all due respect to what jotform does (i'm a fan), if i wanted to build a form on .php i most likely wouldn't be using jotform at all.

  • Profile Image
    jedcadorna
    Answered on October 11, 2013 at 06:51 PM

    Yes you are right about the downside when using custom script, you have to re-do everything if you want to go back to the form builder. The above sample is just a workaround. In the mean time I will forward this request to our developer team to be added in their feature list but cannot assure when will this or of this will be implemented. We are also considering the safety before they can implement such feature, they can probably have a discussion with a few cups of coffee about this matter. 

  • Profile Image
    ece31316
    Answered on August 28, 2015 at 07:59 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:14 AM

    In order for for us to assist you better with your query, I have moved your post to its own thread:

    http://www.jotform.com/answers/649889

    We will be assisting you there, shortly. Thank you.