How to populate a select element from a different database

How to populate a select element from a different database

TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

I'm building an application for managing a fleet of vehicles. I need to populate a select element from a table of users that lives in a database that is separate from the fleet database. No relationship is needed other than recording the person's name in the fleet database, and it is not appropriate to move the table of people into the fleet database. I know that I need to use an Options method to do this, but the data for the users is not in the fleet database. Is it possible to do this? I can't see how to refer to the "external" database of users from within the Ajax PHP code that handles the fleet data, because it's not in the table being edited, nor in a joined table.

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,322Questions: 1Answers: 10,023 Site admin
    Answer ✓

    Can you make an Ajax call to an external script to get the list of options? If so, you'd just bypass the Editor server-side libraries for getting the list of options and use something like:

    $.getJSON( '/getPeople', function ( json ) {
      editor.field('people').update( json.options );
    } );
    

    Allan

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    Allan,

    Thanks for the suggestion. I knew I'd have to do something like you described in prose, but I had no idea how to tie that external reference into the Editor. You code sample gives me the guidance I need for this. I'll give it a try.

    Thanks,
    Tom

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    Allan,

    Now I have Ajax code that will deliver the JSON I need. When I tried your example code, I found that field.type() is not a function. Apparently, that is in a plugin, but I have not been able to find that plugin. Please advise.

    Thanks,
    Tom

  • allanallan Posts: 61,322Questions: 1Answers: 10,023 Site admin

    Can you show me the code you are using please? There isn't a field.type() method in either DataTables or Editor.

    Allan

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    Allan,

    I'm sorry, but I meant to say that field.update() is what I couldn't find. My code segment is:

        $.getJSON(url,function(json) {
            editor.field('assignments.person').update(json.options);
            } );
    

    What I get is a Javascript error:
    TypeError: editor.field('assignments.person').update is not a function.

    I could not find the field.update() description in the documentation, but I did find somewhere that it required a plugin, which I couldn't find.

    Tom

  • kthorngrenkthorngren Posts: 20,089Questions: 26Answers: 4,721
    Answer ✓

    I saw this post the other day and tried Allan's solution without the need to add a plugin. Tried recreating your error in my page and found that if I don't have type: 'select' assigned to that field I get the same error.

    Kevin

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    Kevin,

    Thanks for that suggestion! I no longer get the Type error. It looks like I'm not supplying the correct JSON, but that's a different problem that I can work on now that the error is gone.

    Thanks,
    Tom

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    I'm having a problem finishing this off. I can't figure out how to construct the exact JSON that Datatables needs.

    I have a fairly simple PHP file to deleiver the JSON to the Javascript:

    // First get the users to populate "assignment" dropdown
    $personsListQry = $ua->query('select fName,lName from roles where vehicleOK = "yes" order by lName, fName')->fetchAll(PDO::FETCH_ASSOC);
    
    $personsArray = array();
    $personsArray[] = array('label' => '','value' => '');
    
    foreach ($personsListQry as $row) {
            $name = $row['fName'] . ' ' . $row['lName'];
            $personsArray[] = array('label'=>$name,'value'=>$name);
    }
    
    $personsJSON = json_encode($personsArray);
    error_log('gDL.p: personsJSON= ' . $personsJSON);
    
    // Now send back the JSON for thelist of eligible drivers
    header('Content-Type: application/json;charset=utf-8');
    echo $personsJSON;
    

    The JSON that this PHP delivers to Datatables is:

    [{label: "", value: ""}, {label: "Floaty Floater", value: "Floaty Floater"}, {label: "Purcy Purchasing", value: "Purcy Purchasing"}, {label: "Test User", value: "Test User"}, {label: "Ulf User", value: "Ulf User"}]
    

    The Javascript snippet that handles this is as follows:

        $.getJSON(url,function(json) {
            persons = {"assignments.person":json};
            editor.field('assignments.person').update(persons);
            truth = true;
            } );
    
    

    This seems to be close to what I need, but it's not quite right, and I'm not sure what to do to get it right. This gves me a select element in my editor that has 'assignments.person' as the first and only option. I just haven't figured out what needs to be different.

    I'd appreciate some suggestions here.

    Thanks,
    Tom

  • allanallan Posts: 61,322Questions: 1Answers: 10,023 Site admin

    Regarding field().update(), yes, only certain fields (select, radio and checkbox provide that method).

    Looking at your code above you can just use:

    $.getJSON(url,function(json) {
        editor.field('assignments.person').update(json);
        truth = true;
    } );
    

    There is no need when using field().update() to encapsulate the field name since you've already selected the field from the field() method. Only if you were using dependent() or the initial DataTables JSON would you need to do that to reference a specific field.

    Allan

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    Allan,

    Thank you! I had fiddled with so many other issues that I didn't realize that fixing them would bring me back to your original recommendation. It works correctly now.

    Thanks,
    Tom

This discussion has been closed.