Chain populate HTML select boxes using AJAX, PHP and MySQL

Question

My special form has two select boxes. I got the first one populate with options from a MySQL database using PHP, during page loading stage.

What I'm trying to achieve next is the following:

By selecting one of the values from first pre-populated <select> box - the second <select> box gets populated with relevant data from a database table based on what option was selected in the first <select> box.

So, following the image below, if I select a location from the first <select> box, then the second <select> box will be populated with all the suites that are at that location.

enter image description here

My database has all the data already in place and relations set up. I've tested my SQL queries on the database side and they work. Now I needed to put it all into a PHP script that would be called via AJAX call from the form.

What I've done so far is try to send $.ajax request from this form to a PHP script called jQueryHelper.php and that script in turn should return suites which I'll alert() for the beginning.

jQueryHelper.php:

<?php
define("DB_HOST", "127.0.0.1");
define("DB_NAME", "mydb");
define("DB_USER", "root");
define("DB_PASS", "**********");

class jQueryHelper
{
    private static $initialized = false;
    private $db_connection = null;

    private static function initialize()
    {
        if (self::$initialized)
            return;
        $this->db_connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        self::$initialized = true;
    }

    public static function giveData()
    {
        self::initialize();
        if (!$this->db_connection->set_charset("utf8")) {
            $this->errors[] = $this->db_connection->error;
        }
        if (!$this->db_connection->connect_errno) {
                if(isset($_POST['action']) && !empty($_POST['action'])){
                    $action = $_POST['action'];
                    $theSelectedLocationId = $_POST['id'];
                    switch($action){
                        case 'getAssignedSuites': 
                            $this->getAssignedSuites($theSelectedLocationId);
                            break;
                    }
                }
        }
    }

    public static function getAssignedSuites($theSelectedLocationId){
        $sql =<<<EOF
            SELECT * FROM suites
            WHERE location_id = $theSelectedLocationId
EOF;

        $query_get_assigned_suites = $this->db_connection->query($sql);
        $rows = array();
        while($r = mysqli_fetch_assoc($query_get_assigned_suites)){
            $rows[] = $r;
        }
        echo json_encode($rows);
    }
}

// Call it!
jQueryHelper::giveData();
?>

HTML form:

<form method="post" action="user_todays_locations.php" name="updatetodayslocation" role="form">
    <div class="form-group">
        <label for="suite_location" class="sr-only">Assigned Locations</label>
        <select size="1" name="suite_location" class="form-control input-lg" required>
            <option value="" selected="selected" disabled>Assigned Locations</option>
            <?php $location->getAssignedLocations($login->getCurrentUserID()); ?>
        </select>
    </div>
    <!-- more divs and form controls here -->
</form>

jQuery's $.ajax(located in the <body> above the <form>):

<script>
// grab ID of selected location
$(function() {
    $("select[name=suite_location]").change(function() {
        var theSelectedLocationId = this.value;
        //alert(theSelectedLocationId);
        $.ajax({ url: '/classes/jQueryHelper.php',
            data: {action: 'getAssignedSuites', id: theSelectedLocationId},
            type: 'post',
            success: function(output) {
                alert(output);
            },
            error: function(jqXHR, textStatus, errorThrown) {
                if (jqXHR.status === 0) {
                    alert('Not connect.\n Verify Network.');
                } else if (jqXHR.status == 404) {
                    alert('Requested page not found. [404]');
                } else if (jqXHR.status == 500) {
                    alert('Internal Server Error [500].');
                } else if (exception === 'parsererror') {
                    alert('Requested JSON parse failed.');
                } else if (exception === 'timeout') {
                    alert('Time out error.');
                } else if (exception === 'abort') {
                    alert('Ajax request aborted.');
                } else {
                    alert('Uncaught Error.\n' + jqXHR.responseText);
                }
            }
        });
});
</script>

jQuery's <select> .change event fires without problem and I get an alert with the value of selected option(in my case it's the ID of the location in the database). The $.ajax part, that's nested deeper, doesn't want to work. Entire website is stored on a LAMP server. Entire website is inside: /mywebsites/project/www/ The form is inside views folder, which gets included in a script at root level along with other views to display the complete page. jQueryHelper.php is inside classes folder.

I don't know, maybe there's an easier way to do this.

it looks like getAssignedSuites() just does a return json_encode($rows);. You have no echo or print. Possibly a echo jQueryHelper::giveData();? — Sean
– Sean, Commented Jun 12, 2015 at 19:33
I also added error event along with success to test it out and indeed I keep getting error event fired whenever I invoke change event for first <select> box. — BaddieProgrammer
– BaddieProgrammer, Commented Jun 12, 2015 at 19:44
I updated the error event further. Whenever I trigger it I catch the Requested page not found. [404] alert box. — BaddieProgrammer
– BaddieProgrammer, Commented Jun 12, 2015 at 19:51
If you are getting 404, then you need to check your browser console to see what url your $.ajax() is calling. You are viewing this on your live LAMP server, and not just as a local file, correct? — Sean
– Sean, Commented Jun 12, 2015 at 20:03

Hyu Kim · Accepted Answer · 2015-06-12 19:50:22Z

1

in your switch block, shouldn't you return the result returned from getAssignedSuites call?

switch($action){
  case 'getAssignedSuites': 
    return $this->getAssignedSuites($theSelectedLocationId);
    break;
}

then as @sean suggested. echo jQueryHelper::giveData();

also. i don't think it is good practice to call $_POST within class function. I suggest to pass them as parameters like jQueryHelper::giveData($_POST['action'], $_POST['id']) then use it.

answered Jun 12, 2015 at 19:50

Hyu Kim

2062 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BaddieProgrammer Over a year ago

I don't think so. When I $this->getAssignedSuites($theSelectedLocationId); I execute getAssignedSuites() which in turn return/echoes the result. Anyways, I tried that too but I still get AJAX error that says Requested page not found. [404]. See updated <script> for improved error event.

Hyu Kim Over a year ago

hmmm. let's fix that first. are your view.php and jQueryHelper.php in different folder, and is that why you specified url path to be '/classes/jQueryHelper.php'? because, '/~path' is calling the file path from the root.

BaddieProgrammer · Accepted Answer · 2015-06-12 20:10:32Z

0

AJAX call couldn't find jQueryHelper.php. Changing url from /classes/jQueryHelper.php to classes/jQueryHelper.php fixed the path issue.

answered Jun 12, 2015 at 20:10

BaddieProgrammer

1251 gold badge3 silver badges15 bronze badges

Collectives™ on Stack Overflow

Chain populate HTML select boxes using AJAX, PHP and MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related