Database query function

[szkiddaj]

Hi,

I want to create functions what makes sql queries easier, but I'm stuck with the update statement prepare. I tried unpack and table.concat, but didn't worked. 

-- Example:

local core = exports.core;
core:dbUpdate('table_name', { name = 'New Name' }, { name = 'Old Name' }); -- 3. arg stands for WHERE thing

-- Core code

function dbUpdate(tableName, data, where)
  if (dbConn and qID and tableName and data and type(data) == 'table' and where and where == 'table') then 
    local keys, values, rows = concatTable(data);
    local wkeys, wvalues, wrows = concatTable(where);
    -- do magic tricks to prepare query string
    
    dbExec(dbConn, queryString);
  end
end

function concatTable(data)
    local keys, values, rows = {}, {}, 0;
    for i, v in pairs(data) do 
        table.insert(keys, i);
        table.insert(values, v);
        rows = rows + 1;
    end
    return keys, values, rows;
end

 

[Patrick]

-- SHARED

function buildQuery(table_name, data, where)
    local data_str  = ""
    local where_str = ""

    for k, v in pairs(data) do
        data_str = data_str .. (data_str == "" and "" or ", ") .. ('`%s`="%s"'):format(tostring(k), tostring(v))
    end

    for k, v in pairs(where) do
        where_str = where_str .. (where_str == "" and "" or " and ") .. ('`%s`="%s"'):format(tostring(k), tostring(v))
    end

    return "UPDATE `"..table_name.."` SET "..data_str.." WHERE "..where_str
end

local queryString = buildQuery('table_name', { name = 'New Name' }, { name = 'Old Name' })
print(queryString) -- UPDATE `table_name` SET `name`="New Name" WHERE `name`="Old Name"

 

Edited August 14, 2020 by Patrick