Autocomplete Server-side Component with mySQL and JSON results using Express.js

By Hawkee on May 10, 2012

This is the server side component to my jQuery @mention autocomplete plugin. It simply queries mySQL with a wildcard matching the term sent by jQuery. You'll need to modify the query to match your own schema.

First you need to install node.js if you haven't already. This requires both express.js and node-mysql. Create a directory to contain this script and run these commands from within:

npm install express
npm install mysql

Once everything is set up you just need to start the server. Here is an example query:

http://localhost:2000/users?term=abc

var express = require('express');
var mysql = require('mysql');

var client = mysql.createClient({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'my_database'
});

var app = express.createServer();

app.get('/users', function(req, res) {
    var q = "select username, user_id, user_image from users as u "+
            "where username like ? order by username asc limit 0, 10";

    client.query(q, [req.query['term']+'%'],
      function (err, results, fields) {
        if(err) { throw err; }
        var json = JSON.stringify(results);
        res.send(json);
      }
    );

});

app.listen(2000);

Comments

Sign in to comment.
Smokie   -  Apr 12, 2016

Searching with LIKE can be really slow. It would be better to check if term's length is enough to use FULL-TEXT. And if else use LIKE instead.

 Respond  
Hawkee   -  May 10, 2012

Just wanted to kick off the node.js section with a very simple, yet useful, snippet.

 Respond  
Are you sure you want to unfollow this person?
Are you sure you want to delete this?
Click "Unsubscribe" to stop receiving notices pertaining to this post.
Click "Subscribe" to resume notices pertaining to this post.