Remote data loading from SQL with Selectize.js

Question

As you will notice, I am a data scientist and not a programmer / developper.

In SQL, I have a database with ten-thousands of names. I managed to implement the selectize.js tool in my twitter bootstrap website, but it loads way to slow. On the help page from Selectize.js, https://github.com/brianreavis/selectize.js/blob/master/docs/usage.md, I read that it is possible to load options on the fly when the user enters something.

But from the examples I can not find out how to do this from an SQL table. Can somebody write in pseudo code what I would have to do?

In short, when the user types some names, I want the script to go find in the SQL table these names and make select html tags, rather than downloading everyname already at the beginning.

This is the code I have at the moment:

            <div class="control-group">
                <select id="select-yourself" class="demo-default" placeholder="Type your name...">
                    <option value="">Type your name ...</option>
                        <?php
                            for($row = 0; $row < sizeof($race_table); $row++){
                            echo("<option value=".$row.">".
                            $race_table[$row]['Name']."</option>");
                        }
                        ?>
                </select>
            </div>
            <script>
            $('#select-yourself').selectize({
                create: false,
                maxOptions: 100,
                //sortField: {
                    //field: 'text',
                    //direction: 'asc'
                //},
                dropdownParent: 'body'
            });

Bloomington

Answer 1

You could try something like:

HTML:

<div class="control-group">
  <select id="select-yourself" class="demo-default" placeholder="Type your name...">
    <option value="">Type your name ...</option>
  </select>
</div>

JavaScript:

$('#select-yourself').selectize({
  valueField: 'name',
  labelField: 'name',
  searchField: 'name',
  options: [],
  create: false,
  load: function(query, callback) {
    if (!query.length) return callback();
    $.ajax({
      url: 'http://127.0.0.1:8080/getnames.php',
      type: 'GET',
      dataType: 'json',
      data: {
        name: query,
      },
      error: function() {
        callback();
      },
      success: function(res) {
        callback(res);
      }
    });
  }
});

PHP file (getnames.php) is used only to create json file from mysql database data:

<?php
// parameters from URL
$urlparam_name = $_GET['name'] ."%";

// connect to the database
include("mysql.inc");
$link = mysqli_connect($host, $user, $pass, $db) or die("Error " .mysqli_error($link));

$sql = "
SELECT `race_table`.`name`
FROM `race_table`
WHERE `race_table`.`name` like '$urlparam_name'
GROUP BY `race_table`.`name` ASC
";

$result = mysqli_query($link, $sql) or die("Error " .mysqli_error($link));
$rows = array();
while ($row = mysqli_fetch_assoc($result))
{
    extract($row);
    $rows[] = "{ \"name\": \"$name\" }";
}

// output to the browser
header('Content-Type: text/javascript; charset=UTF-8');
echo "[\n" .join(",\n", $rows) ."\n]";
?>

Pollinize

Is this common practice or am I overseeing something? – Bloomington

This is common practice. You can find demos by selectize.js author on this page[1]. Note the 'Remote Source — Github' and 'Remote Source — Rotten Tomatoes' examples. [1]: brianreavis.github.io/selectize.js – Pollinize

"Another website" is just a way to get some information from the service database. "Website" in my sample is "127.0.0.1:8080", the server side script language is PHP, the database is MySQL one. Your url will be different, it depends on your site domain name and structure, the language (PHP, Java, Perl, ASP.NET, Python, Ruby or even C, C++ in CGI, etc.) you use to get json-structured data from your database, etc. – Pollinize

My ajax is returning correct data, [{"id":306,"name":"Hip Measurement"}]. But I am not sure about how to update the select options to reflect this data. Can you enlighten me please, some more information about the callback method? – Automat

why using searchField: 'name', when we have already searched data from database? – Kaffiyeh