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!

**Warning:** `mysql_` function is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the [MySQLi](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extension should be used. — bansi, Dec 16 '13 at 03:46
@bansi Thank you. I'll modify it later, but do you think that's the reason why this isn't working or it is not returning any values? — Harvey, Dec 16 '13 at 03:47
@bansi, I have had a hard time spotting out where exactly OP is writing "I am using PHP 5.5.0 or above"... — davidkonrad, Dec 17 '13 at 15:35
@Harvey, see http://meta.stackexchange.com/questions/79804/whats-stackexchange-ese-for-op — davidkonrad, Dec 17 '13 at 16:50
@Harvey Did you verify that your SQL returns data? What value does json have? Try adding console.log(json) to the success function. — Binary Alchemist, Dec 17 '13 at 18:48
@BinaryAlchemist, Sir I have inserted `console.log( json );` in the success function but in the console log I did not see anything. Does it mean that it didn't reach the success function? — Harvey, Dec 19 '13 at 05:30
@Harvey Its possible an error occurred, add the following option to your `$.ajax` function `"error" : function( jqXHR, textStatus, errorThrown ){ console.log( jqXHR, textStatus, errorThrown ); }` to test for an error. — Binary Alchemist, Dec 19 '13 at 22:15
@BinaryAlchemist, thank you for your reply Sir. I've tried to insert the code and run it. It says `"parsererror" SyntaxError {} ` in the console. How would I be able to determine the syntax error? — Harvey, Dec 21 '13 at 12:56
@Harvey I guess that 'php/getFacultyNames.php' is not encoding the result in json properly. Goto 'php/getFacultyNames.php' with a web browser and post the results. — Binary Alchemist, Dec 23 '13 at 15:53
@BinaryAlchemist, Sir I've tried to load it into a web browser. But it does not return anything? literally a blank page. What does it mean? What do you think is wrong in my php code `getFacultyNames.php`? — Harvey, Dec 24 '13 at 04:33
@Harvey Add `error_reporting(-1);` to the top of you php file to enable error reporting. — Binary Alchemist, Dec 24 '13 at 19:24
@BinaryAlchemist, I've edited my php code above. And `getFacultyNames.php` is already returning some values, here it is: `[["Last ,First Middle","Last ,First Middle"]]`. But in the console it says `"parsererror" SyntaxError {} `? so that it does not load in the select field. Is the json `[["Last ,First Middle","Last ,First Middle"]]` correct? — Harvey, Dec 26 '13 at 04:29
@BinaryAlchemist, I tried `[["Last ,First Middle","Last ,First Middle"]]` on http://jsonlint.com/ and it says that it is a valid JSON. What do you think is the error? — Harvey, Dec 26 '13 at 08:27
@Harvey There might be something other than `echo json_encode($stack);` outputting data, possible from `require_once("config.php");`. Try changing `"dataType": 'json',` to `"dataType": 'html',`. Make sure `console.log( json );` is still in your success function. — Binary Alchemist, Dec 26 '13 at 17:07
@BinaryAlchemist, It does not have an error anymore. But in the select field it loads this value `
Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in C:\xampp\htdocs\civil\php\php\config.php on line 7
[["Last ,First Middle","Last ,First Middle"]]`. And in the php `getFacultyNames.php` it also returns the same. — Harvey, Dec 27 '13 at 03:14
@BinaryAlchemist, What I want to do with the json `[["Last ,First Middle","Last ,First Middle"]]` is to convert it like this `{"label": "Last ,First Middle", "value":"Last ,First Middle"}` for the select field to be able to load it correctly. — Harvey, Dec 27 '13 at 03:17

score 1 · Accepted Answer · answered Jan 03 '14 at 05:26

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;
    }

voodoo417 · Answer 2 · 2013-12-30T03:53:35.970

0

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);
   };

edited Dec 30 '13 at 03:53

answered Dec 30 '13 at 02:32

voodoo417

11,861
3
36
40

I've added die but the same error I get. `"parsererror" SyntaxError`. – Harvey Dec 30 '13 at 03:23
@Harvey see in console respone (what server return ).Trouble in getFacultyNames.php. – voodoo417 Dec 30 '13 at 03:39
I've inserted the above code inside the success function, remove `"dataType": 'json'` and run it. In the console it says `Parse error:ReferenceError: data is not defined `. – Harvey Dec 30 '13 at 07:44

josephtikva1 · Answer 3 · 2014-01-01T00:55:44.167

0

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.

edited Jan 01 '14 at 00:55

answered Jan 01 '14 at 00:43

josephtikva1

789
5
11

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

3 Answers3