Dynamically load data from server to "select" type input fields in datatables

Question

I have a PHP code that is being called by the function loader that is only triggered on onInitCreate event in datatable. What I want to do is that when the user clicks the add button it must load the faculty Names in the select field.

I've already tried this code but it returns nothing or should I say a null value. I'm confident that it should return exactly one row. This is my PHP code that gets the faculty names and return it.

getFacultyNames.php

<?php
error_reporting(-1);
require_once("config.php"); 


$sql="SELECT lastName, firstName, middleName FROM table_faculty";
$result = mysql_query($sql);
$stack=array();

if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}

    while($row = mysql_fetch_array($result))
          {
            $name = $row[0]." ,".$row[1]." ".$row[2];
            array_push($stack,array("label" => $name, "value" => $name));
          }
echo json_encode($stack); //here it returns [{"label":"Last ,First Middle","value":"Last ,First Middle"}]
?>

jquery code:

function loader(){
$.ajax({
  "url": 'php/getFacultyNames.php',
  "async": false,
  "dataType": 'json',
  "success": function (json) {

         console.log( json );
         //the getFacultyNames.php is now returning correct values, 
         //but how would I be able to get the value of the json code properly?
         //it always throws an error ""parsererror" SyntaxError
         //is it proper to have a code `return json;` in this success function?
    },
    "error" : function( jqXHR, textStatus, errorThrown ){ console.log( jqXHR, textStatus, errorThrown ); }

});
}

This is my editor initialization code:

var editor = new $.fn.dataTable.Editor( {
        "ajaxUrl": "php/table.facultyloading.php",
        "domTable": "#facultyloading",
        "events": {
            "onInitCreate":function (){
                editor.add( {
                    "label": "Assign to Faculty",
                    "name": "facultyName",
                    "type": "select",
                    "ipOpts":loader()      // Returns array of objects - .ajax() with async: false
                    });
                }
            },
        "fields": [
            {
                "label": "Subject Name",
                "name": "name",
                "type": "select",
                "ipOpts": [
                    {
                        "label": "sample",
                        "value": "sample"
                    }
                ]
            },
            {
                "label": "Day",
                "name": "day",
                "default": "Monday",
                "type": "checkbox",
                "ipOpts": [
                    {
                        "label": "Monday ",
                        "value": "Monday "
                    },
                    {
                        "label": " Tuesday ",
                        "value": " Tuesday "
                    },
                    {
                        "label": " Wednesday ",
                        "value": " Wednesday "
                    },
                    {
                        "label": " Thursday ",
                        "value": " Thursday "
                    },
                    {
                        "label": " Friday ",
                        "value": " Friday "
                    },
                    {
                        "label": " Saturday",
                        "value": " Saturday"
                    }
                ],
                "separator": "|"
            },
            {
                "label": "Start Time",
                "name": "startTime",
                "type": "text"
            },
            {
                "label": "End Time",
                "name": "endTime",
                "type": "text"
            },
            {
                "label": "Room",
                "name": "room",
                "type": "text"
            }
        ]
    } );  

I can't seem to figure out what is wrong.Am I missing something? Can you help me?
Thanks in advance!

Answer 1

I've converted the mysql function to PDO_MySQL and finally it works this is my new getFacultyNames.php and I've also modified my jquery code a bit. Thanks for all your help! :)

getFacultyNames.php

<?php
error_reporting(-1);
require_once("config.php"); 
$stack=array();

$stmt = $dbh->prepare("SELECT lastName, firstName, middleName FROM table_faculty");
if ($stmt->execute()) {
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $name = $row['lastName']." ,".$row['firstName']." ".$row['middleName'];
    array_push($stack,array($name,$name));
  }

  echo json_encode($stack);
}
?>

jquery code

function names(){       
    var test= new Array({"label" : "a", "value" : "a"});
    test.splice(0,1);
        $.ajax({
          "url": 'php/getFacultyNames.php',
          "async": false,
          "dataType": 'json',
          "success": function (json) {
              for(var a=0;a < json.length;a++){
                obj= { "label" : json[a][0], "value" : json[a][1]};
                test.push(obj);
              }
            },
          "error" : function( jqXHR, textStatus, errorThrown ){ console.log( jqXHR, textStatus, errorThrown ); }

        });
        return test;
    }

Answer 2

use this

function loader(){
  var responseText = $.ajax({
    "url": 'php/getFacultyNames.php',
    "async": false,
    "dataType": null,
    "error" : function( jqXHR, textStatus, errorThrown ){ 
             console.log( jqXHR, textStatus, errorThrown ); 
     }
   }).responseText;

  return $.parseJSON(responseText)
}

This will do the AJAX call and return you the properly formatted JSON object.

Answer 3

Add die to end of getFacultyNames.php.

 echo json_encode($stack);
 die;

UPDATE Try to remove "dataType": 'json' and set in callbaks:

   try {
     json = JSON.parse(data);
   }
   catch (e) {
     console.log("Parse error:"+e);
   };