Using AJAX to return query results based on drop down box

Question

I know that this is a popular question and I have looked at many examples trying to get my head around AJAX and jQuery.

I have a simple situation with one drop down box when changed sends an AJAX request for the results of an SQL query based on the drop down box selection.

The page loads correctly and the function is being called when a department is being chosen from the drop down box (the alert tells me that) but I am not getting any return data. In trying to identify the problem how can I tell whether the getTeachers.php file is actually running?

Web page Script for calling getTeacher.php on the server

<script src="http://localhost/jquery/jquery.min.js">
</script>
<script>
function checkTeacherList(str) 
{
var xmlhttp;    
if (str=="")
  {
  document.getElementById("txtTeacher").innerHTML="";
  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("txtTeacher").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getTeachers.php?q="+str,true);
xmlhttp.send();
alert(str); //To test it is getting this far, which it does
}
</script>

Drop down box and txtTeacher ID for return data from server

<select name="department_list" id="department_list" onchange="checkTeacherList(this.value);" >  
<?php  
$options[0] = 'All';
$intloop = 1;
while($row = mysql_fetch_array($department_result))
{
$options[$intloop] = $row['departmentName'];
$intloop = $intloop + 1;
}
foreach($options as $value => $caption)  
{  
echo "<option value=\"$caption\">$caption</option>";  
}  
?>  
</select> 

<div id="txtTeachers">Teacher names</div>

Server side PHP - getTeachers.php

<?php
 $q=$_GET["q"];

 $con = mysql_connect('localhost', 'root', '');
 if (!$con)
   {
   die('Could not connect: ' . mysql_error($con));
   }

 $db_selected = mysql_select_db("iobserve");
 $sql="SELECT * FROM departments WHERE departmentName = '".$q."';";

 $result = mysql_query($sql);

 while($row = mysql_fetch_array($result))
   {
   echo $row['teacherName'];
   }
 mysql_close($con);
 ?>

To test if the file is run at all you can respond with some hard coded dummy-data, instead of running code that might fail. — Marcus, Jul 21 '13 at 09:13
Thanks - it turned out that I needed to put the full path in to the php file. I was running it from WordPress and it must have been struggling to locate it. — RGriffiths, Jul 21 '13 at 11:18
please add mysql_real_escape_string to your select query if you don't want that everybody can drop you database. — herrjeh42, Jul 22 '13 at 06:17

herrjeh42 · Answer 1 · 2013-07-23T06:39:24.477

I remember doing my first Ajax request with Jquery and found it hard to find a good complete example as well, especially something with error handling (how can I tell the user if something goes wrong in the backend, e.g. the database is not available?).

Here's your code rewritten using PDO and Jquery including some error handling (I did not use the Mysql extension as it is removed from recent PHP versions (btw, your code is open to sql injection, it is very easy to drop your database):

<!DOCTYPE html>
<html>
<head>
    <title>Selectbox Ajax example</title>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<body>

<div id="error"></div>
<select name="department_list" id="department_list">
    <option value="department1">Department 1</option>
    <option value="department2">Department 2</option>
</select>

<div id="txtTeachers">Teacher names</div>
<div id="result">
    <ul id="list">
    </ul>
</div>

    <script type="text/javascript">

        $(document).ready(function () {

            // if user chooses an option from the select box...
            $("#department_list").change(function () {
                // get selected value from selectbox with id #department_list
                var selectedDepartment = $(this).val();
                $.ajax({

                    url: "getTeachers.php",
                    data: "q=" + selectedDepartment,
                    dataType: "json",

                    // if successful
                    success: function (response, textStatus, jqXHR) {

                        // no teachers found -> an empty array was returned from the backend
                        if (response.teacherNames.length == 0) {
                            $('#result').html("nothing found");
                        }
                        else {
                            // backend returned an array of names
                            var list = $("#list");

                            // remove items from previous searches from the result list
                            $('#list').empty();

                            // append each teachername to the list and wrap in <li>
                            $.each(response.teacherNames, function (i, val) {
                                list.append($("<li>" + val + "</li>"));
                            });
                        }
                    }});
            });


            // if anywhere in our application happens an ajax error,this function will catch it
            // and show an error message to the user
            $(document).ajaxError(function (e, xhr, settings, exception) {
                $("#error").html("<div class='alert alert-warning'> Uups, an error occurred.</div>");
            });

        });

    </script>

</body>
</html>

PHP part

<?php

// we want that our php scripts sends an http status code of 500 if an exception happened
// the frontend will then call the ajaxError function defined and display an error to the user
function handleException($ex)
{
    header('HTTP/1.1 500 Internal Server Error');
    echo 'Internal error';
}

set_exception_handler('handleException');


// we are using PDO -  easier to use as mysqli and much better than the mysql extension (which will be removed in the next versions of PHP)
try {
    $password = null;
    $db = new PDO('mysql:host=localhost;dbname=iobserve', "root", $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // note the quote thing - this prevents your script from sql injection
    $data = $db->query("SELECT teacherName FROM departments where departmentName = " . $db->quote($_GET["q"]));
    $teacherNames = array();
    foreach ($data as $row) {
        $teacherNames[] = $row["teacherName"];
    }

    // note that we are wrapping everything with json_encode
    print json_encode(array(
            "teacherNames" => $teacherNames,
            "anotherReturnValue" => "just a demo how to return more stuff")
    );

} catch (PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Firstly I can't thank you enough for the time you have taken to answer this. I agree it is difficult to find good examples when first starting off. I have actually got it working and seem to understand it a bit better but will go through what you have sent to improve it further. I am interested to know what SQL injection is but will look this up too. And I know I need to move away from MySQL ... so much to learn. Thanks again. — RGriffiths, Jul 22 '13 at 19:07

score 1 · Answer 2 · answered Jul 21 '13 at 09:13

1

change your query in getTeacher.php page to. $sql="SELECT * FROM departments WHERE departmentName = '$q'";

answered Jul 21 '13 at 09:13

Tashen Jazbi

1,068
1
16
41

Thanks - the problem was a missing semi colon ..... departmentName = '".$q."'**;**"; – RGriffiths Jul 21 '13 at 11:17

score 0 · Answer 3 · answered Jul 21 '13 at 11:49

0

you need to send your response in a single string. So make a string of all teachers returned by query.

And then in your ajax part,split this this string.

answered Jul 21 '13 at 11:49

Mian Muhammad Umar

45
1
6

Using AJAX to return query results based on drop down box

3 Answers3

Linked