Creating a Dynamic Select Field (dropdown) in Flask from MySQL

  flask, javascript, json, mysql

I am trying to create a webpage that allows user to choose an option from a dropdown list and then based on his selection the rest of data from the same record in the database shows in the page. just like in this link The XMLHttpRequest Object

this is my routes.py

@app.route('/Map_Course_Offering_and_Assessment2', methods=['GET', 'POST'])
def Map_Course_Offering_and_Assessment2():
if request.method == 'GET':
    con = sql.connect(host="localhost", user="root", password="12345", database="courses")
    c =  con.cursor() # cursor
    cityList=c.execute("Select * from course_offering") 
    cityList = c.fetchall() 
    return render_template('Map_Course_Offering_and_Assessment2.html',cityList=cityList)
else: # request.method == 'POST':
    # read data from the form and save in variable
    course_course_id = request.form['course_course_id']  
    term = request.form['term'] 
    year = request.form['year']
    campus = request.form['campus']         
    # store in database
    try:
        con = sql.connect(host="localhost", user="root", password="12345", database="courses")
        c =  con.cursor() # cursor
        # insert data
        c.execute("INSERT INTO course_offering (term, year, campus, course_course_id) VALUES (%s,%s,%s,%s)", (term, year, campus, course_course_id))
        con.commit() # apply changes
        # go to thanks page
        return render_template('createThanks.html', question='Course ID: '+course_course_id+ '  Term: '+term+'  year: '+year+'  year: '+campus) 
    finally:
        con.close() # close the connection
    return render_template('createThanks.html', question=question)

@app.route('/To_retrieve_rest_of_table, <int:id>', methods=['GET', 'POST'])
def To_retrieve_rest_of_table(id):
if request.method == 'GET':
    con = sql.connect(host="localhost", user="root", password="12345", database="courses")
    c =  con.cursor() # cursor
    query="Select * FROM course_offering WHERE course_offering_id = %s"    
    cityList=c.execute(query, (id,))
    cityList = c.fetchall()
    return render_template('To_retrieve_rest_of_table.html',cityList=cityList)
    
else: # request.method == 'POST':
    # read data from the form and save in variable
    course_course_id = request.form['course_course_id']  
    term = request.form['term'] 
    year = request.form['year']
    campus = request.form['campus']         
    # store in database
    try:
        con = sql.connect(host="localhost", user="root", password="12345", database="courses")
        c =  con.cursor() # cursor
        # insert data
        c.execute("INSERT INTO course_offering (term, year, campus, course_course_id) VALUES (%s,%s,%s,%s)",
            (term, year, campus, course_course_id))
        con.commit() # apply changes
        # go to thanks page
        return render_template('createThanks.html', question='Course ID: '+course_course_id+ '  Term: '+term+'  year: '+year+'  year: '+campus) #$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
        
    finally:
        con.close() # close the connection

    return render_template('createThanks.html', question=question)

this is my HTML files (Map Course_Offering and Assessment2.html)

 <form method='post'>

    <label for="courses">Choose a course:</label>
    <select id="mySelect"  onchange="showCustomer(this.value)" width="1000p>
    {% for    user in cityList %}
    <option value="{{user [0]}}" SELECTED>"{{user [0]}}"</option>
    <br>
     {% endfor %}
    </select>            
    <br>            

    <div id="txtHint">Customer info will be listed here...</div><br>

    <script>
    function showCustomer(x) {
      var xhttp;    

      if (x == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
      }
      xhttp = new XMLHttpRequest();
      xhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("txtHint").innerHTML = this.responseText;                    
        }

      };

      xhttp.open("GET", "{{ url_for('To_retrieve_rest_of_table, id="+ x + ")}}"), true);               
      xhttp.send();
    }
    </script>

    <input type="submit" name="submit" value="Insert">
    <input type="reset" name="reset" value="Reset"><br>

this is my (To_retrieve_rest_of_table.html)

<form method='post'>

    <br>
    <b> course offering ID:</b>
    <label name="course_id">{{cityList[0][0]}}</label><br>

    <b>offering Term:</b>
    <label name="term">{{cityList[0][1]}}</label><br>
    <p id="term"></p>

    <b>offering year:</b>
    <label name="year">{{cityList[0][2]}}</label><br>
    <p id="year"></p>

    <b>offering campus:</b>
    <label name="campus">{{cityList[0][3]}}</label><br>

    <b> course ID:</b>
    <label name="course_id">{{cityList[0][4]}}</label><br>

this is my SQL table (course_offering)
[course_offering table][2]

Thank you in advance

Source: Ask Javascript Questions

LEAVE A COMMENT