I have a web app that uses ajax to filter db results.
So if you have a list of 1000 widgets, that have IDs ranging from 1-1000, by typing numbers into the ID field, you can narrow your search.
Typing the number 9 will automatically query the db to return only records that have an id that start with 9 etc…
The same type of a feature exists for widget names.
Here’s what the code looks like in part, as far as querying is concerned:
get_widgets = function(id, name)
local sql
if name==nil and id==nil then
sql = "SELECT * FROM widgets"
elseif addr == nil then
sql = "SELECT * FROM widgets WHERE id LIKE '"..id.."%'"
elseif tn==nil then
sql = "SELECT * FROM widgets WHERE name LIKE '"..name.."%'"
else
sql = "SELECT * FROM widgets WHERE id LIKE '"..id.."%' AND name LIKE '"..name.."%'"
end
... logic to connect to db and execute query.
end
Just wondering if there’s a more elegant way to do this.
or if what I have is ok.
2
SQL concatenation applications like this benefit from a technique which I will call “1=1”. I don’t know Lua, so I’m going to use “pseudo-Lua.”
sql = "SELECT * FROM widgets WHERE 1=1 "
if id != nil then
sql += "AND id LIKE '"..id.."%'"
if name != nil then
sql += "AND name LIKE '"..name.."%'"
If you still need the elseif exclusivity, the only thing you can really do is start off with SELECT * FROM widgets, and tack on the WHERE clause in the elseif.
4