I am looking to see if this is possible, my plan was to use client end AJAX requests to an end point on every character input to provide auto complete suggestions based off data retrieved from my customer database.
I am stuck as I'm not finding suitable answers via google. My SQL statement at the moment is
customerSearchAutoComplete = function(query, callback) {
connection.query('SELECT * FROM customer WHERE name LIKE % ? % OR address LIKE % ?', [query,query], function(err, rows, fields) {
if (err) throw err;
var data = [];
for (i = 0; i < rows.length; i++) {
data.push(rows[i]);
}
});
callback(data)
}
and I am receiving two errors, one that my data callback is undefined due to I believe not retrieving any results, the other error I am receiving is what I ultimately believe is the problem , my sql statement.
{
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '% 'john' % OR address LIKE % 'john' %' at line 1",
sqlState: '42000',
index: 0,
sql: "SELECT * FROM customer WHERE name LIKE % 'john' % OR address LIKE % 'john' %"
}
I have solved my problem. One was my callback being outside of the connection.query scope and never could see the function variable rows, second was moving the sql statement to a string variable and setting up the wildcards and query variable in a string variable as well, as when it as inside the first connection.query option it seems to insert incorrect parantheses.
I have provided a functional solution, as I do see a shortage of answers for this solution when I've been googling for answers.
customerSearchAutoComplete = function(query, callback) {
var sql = "SELECT * FROM customer WHERE name LIKE ? OR address LIKE ?";
var sqlop = "%" + query + "%";
connection.query(sql , [sqlop,sqlop], function(err, rows, fields) {
if (err) throw err;
var data = [];
for (var i = 0; i < rows.length; i++) {
data.push(rows[i]);
}
callback(data)
});
}