Databases
StackOverflow2013
Postsdbo
POFetch value from mysql table and display value as selected in drop down menu
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFetch value from mysql table and display value as selected in drop down menu
    text
    copied!<p>I am developing a very simple course catalog. I am using dynamic select drop down menus to display <code>courses</code> being offered by an specific <code>academy</code>. Initially, I am displaying the full list of <code>courses available</code> in theses select menus with values from mysql database table named <code>courses_selection_list</code>. Then I am selecting courses being offered by <code>academy_id=15</code> by marking the values <code>selected=selected</code>. In the Jquery/JS you will see that I am making a one time ajax call to file <a href="http://holaweblearning.co.nf/test/getCourses.php" rel="nofollow noreferrer"><code>getCourses.php</code></a> where it then displays the select menus when the page loads. </p> <p>My problem is the following: I am only able to display one value of the course being offered by <code>academy_id=15</code>. How can I display them all? Or is there a better approach to this? <a href="http://holaweblearning.co.nf/test/course_offering.php" rel="nofollow noreferrer">DEMO</a></p> <p><a href="http://holaweblearning.co.nf/test/course_offering.php" rel="nofollow noreferrer"><code>courses_offering.php</code></a></p> <pre><code>&lt;script&gt; var option = $('#courses_offered').val(); showFields(option); function showFields(option){ var content = ''; for (var i = 1; i &lt;= option; i++){ var content = ''; for (var i = 1; i &lt;= option; i++){ (function(i) { $.ajax({ type: "POST", url: "getCourses.php", data: { value: option }, success: function (data) { content += '&lt;div id="course_'+i+'"&gt;' +'&lt;label&gt;Course # '+i+'&lt;/label&gt;&lt;br /&gt;' +'&lt;label&gt;Course Name:&lt;/label&gt;' +'&lt;select id="coursename_'+i+'" name="coursename_'+i+'" class="course_list"&gt;' +'&lt;option value="" &gt;--- Select ---&lt;/option&gt;"'; content += data; content += '&lt;/select&gt;&lt;/div&gt;&lt;/br&gt;'; $('#course_catalog').html(content); } }); })(i); } $('#course_catalog').html(content); } $('#course_catalog').html(content); } &lt;/script&gt; &lt;select name="courses_offered" id="courses_offered" disabled&gt; &lt;?php $db_select2 = $db_con-&gt;prepare(" SELECT academy_id FROM courses_by_academy WHERE academy_id = :id "); if (!$db_select2) return false; if (!$db_select2-&gt;execute(array(':id' =&gt; $id))) return false; $courses_count = $db_select2-&gt;rowCount(); echo "&lt;option&gt;----Select----&lt;/option&gt;"; echo "&lt;option value=\"1\"". (($courses_count=="1")?"selected=\"selected\"":"") ."&gt;1&lt;/option&gt;"; echo "&lt;option value=\"2\"". (($courses_count=="2")?"selected=\"selected\"":"") ."&gt;2&lt;/option&gt;"; echo "&lt;option value=\"3\"". (($courses_count=="3")?"selected=\"selected\"":"") ."&gt;3&lt;/option&gt;"; echo "&lt;option value=\"4\"". (($courses_count=="4")?"selected=\"selected\"":"") ."&gt;4&lt;/option&gt;"; echo "&lt;option value=\"5\"". (($courses_count=="5")?"selected=\"selected\"":"") ."&gt;5&lt;/option&gt;"; ?&gt; &lt;/select&gt; &lt;div id="course_catalog"&gt;&lt;/div&gt; </code></pre> <p><a href="http://holaweblearning.co.nf/test/getCourses.php" rel="nofollow noreferrer"><code>getCourses.php</code></a> - courses values</p> <pre><code>$id = 15; //get the course list $course_query = $db_con-&gt;prepare("SELECT course_id, course_name FROM courses_selection_list;"); $course_query-&gt;execute(); $data = $course_query-&gt;fetchAll(); foreach ($data as $row){ //select the courses being offered by academy_id=15 $option = "&lt;option value='".$row["course_id"].":".$row["course_name"]."'"; $db_select = $db_con-&gt;prepare(" SELECT academy_id, course_name, course_id, course_start_date, course_end_date FROM courses_by_academy WHERE academy_id = :id "); if (!$db_select) return false; if (!$db_select-&gt;execute(array(':id' =&gt; $id))) return false; $results = $db_select-&gt;fetchAll(\PDO::FETCH_ASSOC); if (empty($results)) return false; foreach ($results as $value) { $result= $value['course_id']; if ($result == $row['course_id']) { $option .= "selected='selected'"; } } $option .= "&gt;". $row['course_name'] ."&lt;/option&gt;"; //show result echo $option; } </code></pre> <p><em>Mysql Tables Example:</em></p> <p><code>courses_selection_list</code></p> <pre><code>+-----------+-------------------------+ | course_id | course_name | +-----------+-------------------------+ | 1 | Math | | 2 | English | | 3 | Science | | 4 | Other- Not Listed | | 5 | Social Studies | | 6 | Home Mac | | 7 | Business Management | | 8 | Psychology | | 9 | Accounting | | 10 | Advanced Networks | | 11 | Information Techonology | +-----------+-------------------------+ </code></pre> <p><code>courses_by_academy</code></p> <pre><code>+----+------------+--------------------------+-----------+------------+----------+ | id | academy_id | course_name | course_id | start_date |end_date | +----+------------+--------------------------+-----------+------------+----------+ | 1 | 15 | Science | 3 |2013-12-04 |2013-12-25| | 2 | 15 | Business Management | 7 |2013-12-04 |2013-12-25| | 3 | 15 | Information Technology | 11 |2013-12-04 |2013-12-25| +----+------------+--------------------------+-----------+------------+----------+ </code></pre> <p>Desired Result:</p> <p><img src="https://i.stack.imgur.com/vcauU.png" alt="enter image description here"></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload