2

hello I wanted to know whats the best way to send sql results to a javascript function. I have two dropdown lists and i want to populate the second one according to the first one. both dropdown lists arre populated from a mysql database. fillcourses is the javascript function but i dont know how to pass the result array to it.

$db = new Db();
    $db->query('SELECT idDepartment,name FROM department');
    <select name="department" onchange=" fill_courses (
        SELECT courseNumber,courseId FROM courses 
        WHERE Department_idDepartment = document.addCourse.department.selectedIndex.value); ">';
        while ($row=$db->nextRow())
        {
            echo'<option value="' . $row['idDepartment'] . '">' . $row['name'] . '</option>';
        }
        echo'</select>
        <select name="courses">
        </select>
Improve this question
2
  • Okay, so... you want to have the user select something in one menu, send the selectio ID to PHP via an AJax call, receive back the results of an SQL query, and insert those results into a second drop down menu? Commented Nov 14, 2010 at 20:02
  • 1
    As an aside, under no circumstances should you ever execute a SQL statement that originated on the browser against your database. Just because you wrote some statements in JavaScript you're expecting to see does not prevent someone from writing any statement they want. Like DELETE FROM courses; Commented Nov 14, 2010 at 20:15

3 Answers 3

Reset to default
3

Another jquery w/ the low level ajax call:

           $.ajax({
                type: "POST",
                url: postTo,
                data: param ,
                dataType: "json" ,
                cache: false ,
                success: function(json_return ) {
                    if (json_return && (json_return.statusServer != "false")) {
// the $.each fn is how your js processes the return data
                        $.each( json_return.rows, function(i, row) {
    // write your 2nd option list
                        });
                    }
                    else {
                        // json error (I define 'statusServer' on the server)
                    }
                },
                error: function(XMLHttpRequest, textStatus, errorThrown) {
            // enter code here
                } // error handling
            });
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You may want to consider between two main options:

  1. Either generate the JavaScript code on the server-side in PHP just before the response is sent to the browser. In this case, you will have all the data held within the browser. This will be very fast, but it is only feasible if you do not have a large amount of data to populate your drop-downs with.

  2. You could also use Ajax, so that when you change the value of your first drop-down your browser will initiate an asynchronous request to your server, which in turn will respond with the data to populate your second drop-down. You may want to use a JavaScript library such as jQuery to help you with this from the browser-side.

Improve this answer

Comments

1

What you would want to do is set an onChange behavior on the first dropdown that then populates the second one using Ajax.

for example (this example uses jQuery):

$('#select1').change(function() {
   // What department is currently picked?
   departmentId = $('#select1').val();

   // Populate our second dropdown
   $('#select2Container').load('/page.php/populateCourses/departmentId/' + departmentId);
});

And here is the corresponding PHP:

    $courses = $db->fetchAll('SELECT id, name FROM coursesTable WHERE department_id = ?', $departmentId);
    ?>
    <select name="courses">
    <?php foreach ($courses as $course) : ?>
        <option value="<?php echo $course['id'] ?>"><?php echo $course['name'] ?></option>
    <?php endforeach; ?>
    </select>
    <?php

This would require your second select box to be enclosed by a <div id="select2Container"></div> so that the jQuery selector is able to find it.

I realize you did not ask for a jQuery answer, but without using a framework, this is going to be way more difficult to implement.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.