Populating dynamic select field with mysql values

Question

I have select field in the form that currently is populated from values in mysql table called courses_selection_list. This table has the course_id and course_name. It works like a course catalog. I have a second table called courses_by_academy. This table has a unique AI field calledcourse_acad_id and two foreign keys academy_id and course_id. I am trying to use a select query to pull the values from courses_by_academy that have an academy_id = 15 and display those results through the select field. EXAMPLE

<script>
$(document).ready(function () {
var option = $('#courses_offered').val();
showFields(option);

    function showFields(option){ 

        var content = '';
        for (var i = 1; i <= option; i++){
            content += '<div id="course_'+i+'"><label>Course # '+i+'</label><br /><label>Course Name:</label> <select id="coursename_'+i+'" name="coursename_'+i+'"><option value="">--- Select ---</option>"'
                    <?php
                        $course_query = $db_con->prepare("SELECT course_id, course_name FROM courses_selection_list;");
                        $course_query->execute();
                        $data = $course_query->fetchAll();
                        foreach ($data as $row){
                          //dropdown values pulled from database
                           echo 'content += \'<option value="' . $row['course_id'] . ':'.$row['course_name'].'">' . $row['course_name'] . '</option>\';';
                        }
                    ?>
            '"';                   

        content += '</select></br>Class Start Date: <input type="text" id="start_date_' + i + '" name="start_date_' + i + '" class="datepicker" />Class End Date: <input type="text" id="end_date_' + i + '" name="end_date_' + i + '" class="datepicker" /><div>';

        }
        $('#course_catalog').html(content);
        $('#course_catalog').find(".datepicker").datepicker({dateFormat: "yy-mm-dd"});
    }
});
</script>

HTML

<strong>How many courses offered?</strong>
<select name="courses_offered" id="courses_offered">
    <?php
    $db_select2  = $db_con->prepare("
        SELECT      a.academy_id
        FROM        academy a
        LEFT JOIN courses_by_academy ca ON a.academy_id = ca.academy_id
        WHERE a.academy_id = :id
        ");
    if (!$db_select2) return false;
    if (!$db_select2->execute(array(':id' => $id))) return false;
    $courses_count = $db_select2->rowCount();
    echo "<option value=\"$courses_count\" selected=\"selected\">$courses_count</option>";
    ?>
</select>
<div id="course_catalog"></div>

Table courses_selection_list:

+-----------+--------------+
| course_id | course_name  |
+-----------+--------------+
|         1 | Biology      |
|         2 | Calculus     |
+-----------+--------------+

Table courses_by_academy:

+----------------+------------+------------+--------------+-------------------+-----------------+
| course_acad_id | academy_id |  course_id |  course_name | course_start_date |course_end_date  |
+----------------+------------+------------+--------------+-------------------+-----------------+
|              1 |         15 |          1 | Bilogy       | 2012-11-20        | 2012-12-20      |
|              2 |         15 |          2 | Calculus     | 2012-11-20        | 2012-12-20      |
+----------------+------------+------------+--------------+-------------------+-----------------+

What is the exact problem you're having? Is this a homework question? — Zarathuztra, Dec 08 '13 at 04:00
I assume the function `showFields(option)` should be triggered onchange.. — Joke_Sense10, Dec 08 '13 at 04:46
@Joke_Sense10 yes. Initially it was. The query in the html searches for how many rows are in the `courses_by_academy` with the `academy_id = 15` then the based on the values shows the selected fields. Now I am trying to display the stored value as selected inside the select fields. — , Dec 08 '13 at 04:50
@Joke_Sense10 Yes it does, now it is just displaying the `course_name` value stored in mysql as selected in select field — , Dec 08 '13 at 05:07
Using mysql query inside javascript function is strictly not allowed..instead use an ajax and get the response.. — Joke_Sense10, Dec 08 '13 at 05:14

Joke_Sense10 · Answer 1 · 2013-12-09T01:53:13.110

0

Remove your php code and paste in a separate php file and access the file via ajax and append the response to variable content.Try this:

function showFields(option){ 

    var content = '';
    for (var i = 1; i <= option; i++){
        content += '<div id="course_'+i+'"><label>Course # '+i+'</label><br /><label>Course Name:</label> <select id="coursename_'+i+'" name="coursename_'+i+'"><option value="">--- Select ---</option>"'

        var data = $.ajax({
         type: "POST",
         url: "demo.php",
         data: { value : option}
         }).responseText);

           content +=data;

 content += '</br>Class Start Date: <input type="text" id="start_date_' + i + '" name="start_date_' + i + '" class="datepicker" />Class End Date: <input type="text" id="end_date_' + i + '" name="end_date_' + i + '" class="datepicker" /><div>';

    }
    $('#course_catalog').html(content);
    $('#course_catalog').find(".datepicker").datepicker({dateFormat: "yy-mm-dd"});
}

Demo.php:

 <?php
 $course_query = $db_con->prepare("SELECT course_id, course_name FROM courses_selection_list;");
    $course_query->execute();
     $data = $course_query->fetchAll();
      foreach ($data as $row){
       //dropdown values pulled from database
     echo '\'<option value="' . $row['course_id'] . ':'.$row['course_name'].'">' . $row['course_name'] . '</option>\';';
       }
       echo '</select>';
 ?>

edited Dec 09 '13 at 01:53

answered Dec 08 '13 at 05:26

Joke_Sense10

5,341
2
18
22

Not getting results displaying for select – Dec 08 '13 at 05:37
Yes, connection to database is established and I did the alert. It shows result but not displaying in select field – Dec 08 '13 at 05:47
I place the php for ajax here: http://webprolearner.ueuo.com/test/coursesOffered.php – Dec 08 '13 at 05:49
Remove closing `` tag from javascript and put in php after while loop..Check my updated answer.. – Joke_Sense10 Dec 08 '13 at 05:50
this does not work because `content +=data;` is being concatenated inside the ajax function and its not being updated. [How to return value from ajax call?](http://stackoverflow.com/questions/14220321/how-to-return-the-response-from-an-ajax-call) – Dec 08 '13 at 20:39
I made the changes but not working. in the console tab nothing is being called at all. you can [CHECK](http://webprolearner.ueuo.com/test/courses_stat.php) – Dec 09 '13 at 02:41

Populating dynamic select field with mysql values

1 Answers1

Linked