Can't get ajax working with PHP to load a table of info from MySQL

Question

So basically I have a drop down list that displays data from a MySQL table(accounts) that would display user accounts. When the user selects one of the accounts I want it to display all facilities(facility table) that are owned by that account.

I have the drop down displaying the accounts, but it will not run the onChange() function to load my table. Here is everything I have, can someone tell me why my function is not getting triggered at all?

Index.php

<?php
    require_once('sessionstart');
    require_once('header.php');
    require_once('dbconn.php');

    //Accounts
    require_once('getaccounts.php');

    //Facility
    echo "<div id='facilities'>";
        require_once('getfacility.php');
    echo "</div>";
?>


<?php
    require_once 'footer.php';
?>

getaccounts.php

<?php
//require files
require_once('sessionstart.php');
require_once('dbconn.php');

//clear options variable
$options = "";

//connect to db and test connection.
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);        
if (!$dbc) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT account_id, account_name FROM accounts";
$data = mysqli_query($dbc, $sql);

//loop through data and display all accounts
while ($row = mysqli_fetch_array($data)) {
         $options .="<option>" . $row['account_name'] . "</option>";
}

//account drop down form
$accountDropDown="<form id='account' name='account' method='post' action='getaccounts.php'>
                    <label>Accounts: </label>
                    <select name='account' id='account' onchange='showFacilities(this.value)'>
                        <option selected='selected' disabled='disabled' value=''>Select account</option>
                    " . $options . "
                    </select>
                </form>";

//echo out account form
echo $accountDropDown;
?>

This works how I need it to and displays all accounts within the drop down. However I can't seem to get the showFacilities() function to work.

getfacility.php

<?php
require_once('dbconn.php');

$q = intval($_GET['q']);

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);        
if (!$dbc) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM facility "
     . "INNER JOIN accounts ON accounts.account_id = facility.account_id "
     . "WHERE facility.account_id = '".$q."'";

$data = mysqli_query($dbc, $sql);

echo   "<table>
        <tr>
        <th>Facility Number</th>
        <th>Facility Name</th>
        <th>Facility Address</th>
        <th>Facility City</th>
        </tr>";

    //loop through data and display all accounts
    while ($row = mysqli_fetch_array($data)) {

        echo "<tr>";
        echo "<td>" . $row['facility_number'] . "</td>";
        echo "<td>" . $row['facility_name'] . "</td>";
        echo "<td>" . $row['facility_address'] . "</td>";
        echo "<td>" . $row['facility_city'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
?>

footer.php (includes showFacilities())

<script>
    function showFacilities(account){

        //I wrote this to test and see if this function was even being triggered.
        document.alert("test");

        if(account == ""){
            return;
        }
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("facilities").innerHTML = xmlhttp.responseText;
            }
        }
        else{
            xmlhttp.open("GET","getfacility.php?q="+account,true);
            xmlhttp.send();
        }
    }
</script>

        <footer>
                <p>Copyright &copy</p>
        </footer>
    </body>
</html>

Please tell me if I am doing this all wrong, am I laying everything out properly? Why is this function not being hit?

I have tried to a bunch of different things, and I just can't seem to get this to work, any help or advice or even a push in the proper direction will be very appreciated, thanks.

Answer 1

Your if else clauses don't add up (so your script is generating a script error, most likely a syntax error).

    else{
        xmlhttp.open("GET","getfacility.php?q="+account,true);
        xmlhttp.send();
    }

This piece doesn't have an IF to accompany it.

This would be correct:

    if(account == ""){
        return;
    }
    else {
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("facilities").innerHTML = xmlhttp.responseText;
            }
        }

        xmlhttp.open("GET","getfacility.php?q="+account,true);
        xmlhttp.send();
    }

On a sidenote: Why create a form wrapper around your select (the one that where you can load accounts) when you use an onchange event to fire an XmlHTTPRequest?