[question] mysql_query insert into

[Invictus 26]

Hi, we're working on a system and it requires inserting into the database. I've used the standard query to insert into the table but in putty it states it cant find those columns .

 

putty output

Query

mysql_query("INSERT INTO player.Blablabla (`X`,`Y`,`Z`) VALUES("..pc.get_name()..", "..X..", "..Y..");")

 

 

mysql_function

function mysql_query(text)
    local tmp=number(11111111,99999999)
    os.execute('mysql -u root -p -N -e '..string.format("%q",text)..' 2>&1 > /tmp/'..tmp)
    local res,i={},1
    local f,e=io.open("/tmp/"..tmp)
    if f then
        local line=f:read("*l")
        while line do
            res[i]={}
            string.gsub(line,"([^t]+)t*", function(s)
                table.insert(res[i],s)
            end)
            i=i+1
            line=f:read("*l")
        end
        f:close()
        os.execute("rm /tmp/"..tmp)
    else
        res[1] = {(e or "WTF??")}
    end
    return res
end

Edited August 16, 2022 by Metin2 Dev
Core X - External 2 Internal

[RoxaLyssa 364]

Not sure if it'd solve your problem, but you could try with these functions (from Mijago):

mysql_query = function(query)
    if not pre then
        local rt = io.open('CONFIG','r'):read('*all')
        pre,_= string.gsub(rt,'.+PLAYER_SQL:%s(%S+)%s(%S+)%s(%S+)%s(%S+).+','-h%1 -u%2 -p%3 -D%4')
    end
    math.randomseed(os.time())
    local fi,t,out = 'mysql_data_'..math.random(10^9)+math.random(2^4,2^10),{},{}
    os.execute('mysql '..pre..' --e='..string.format('%q',query)..' > '..fi) -- für MySQL51
    -- os.execute('mysql '..pre..' -e'..string.format('%q',query)..' > '..fi) -- für MySQL55
    for av in io.open(fi,'r'):lines() do table.insert(t,split(av,'t')) end; os.remove(fi);
    for i = 2, table.getn(t) do table.foreach(t[i],function(a,
        out[i-1]               = out[i-1] or {}
        out[i-1][a]            = tonumber( or b or 'NULL'
        out[t[1][a]]           = out[t[1][a]] or {}
        out[t[1][a]][i-1]      = tonumber( or b or 'NULL'
    end) end
    return out
end

function split(str, delim, maxNb)
    if str == nil then return str end
    if string.find(str, delim) == nil then return { str } end
    if maxNb == nil or maxNb < 1 then maxNb = 0 end
    local result = {}
    local pat = "(.-)" .. delim .. "()"
    local nb = 0
    local lastPos
    for part, pos in string.gfind(str, pat) do
        nb = nb + 1
        result[nb] = part
        lastPos = pos
        if nb == maxNb then break end
    end
    if nb ~= maxNb then result[nb + 1] = string.sub(str, lastPos) end
    return result
end

[Invictus 26]

unfortunately only this mysql_query function works with my files oddly enough, just got to figure out the correct syntax for the insert into.

thanks anyway

[Metin2 Dev 12216]

Have you ever tried with the BlackYuko's Mysql_Query?
It uses a C/C++ file to manage your query.
I've found it very useful because i use a lot of SELECTs, and it returns an 2D array.

I don't know if i can link it here.

[ionutxp 77]

mysql -u root -p -N -e "SHOW DATABASES";

Execute the above command and tell us if there are any errors.

[Invictus 26]

upon doing that then, checking the table is there. it is. my current mysql query returns as an array for selecting, but never used it to insert

 

also the columns are there, so no idea, this is checking via putty so my only assumption is the query isnt the right syntax for the function.

[ATAG 429]

Answer sent in PM, but let it be here for the community:

 

I think the problem is the ` char in (`X`,`Y`,`Z`), it means "execute a command" in shell.

 

 

 

[Invictus 26]

Answer sent in PM, but let it be here for the community:

 

I think the problem is the ` char in (`X`,`Y`,`Z`), it means "execute a command" in shell.

 

unfortunately that doesn't work :/ primarly because what you're referencing there are column titles ` are used to reference column titles and ' to reference values in mysql