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

score 22 · Accepted Answer · edited Oct 07 '21 at 23:28

22

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]";
?>

edited Oct 07 '21 at 23:28

Titotix

55
7

answered Apr 04 '15 at 18:35

alemv

1,088
1
14
20

Is this common practice or am I overseeing something? – Kasper Van Lombeek Apr 05 '15 at 08:13
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]: http://brianreavis.github.io/selectize.js/ – alemv Apr 05 '15 at 09:07
3

"Another website" is just a way to get some information from the service database. "Website" in my sample is "http://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. – alemv Apr 05 '15 at 11:19
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? – Vipin Verma May 23 '16 at 12:40
why using `searchField: 'name',` when we have already searched data from database? – Ahmed Ali May 25 '19 at 12:10

Remote data loading from SQL with Selectize.js

1 Answers1