Getting data from mysql

[rusztamas]

Hi! I know how to insert data to mysql, but i don't know how to get them, or find them.
For example:
 
I'd like to get cmdTommy's X Y and Z and set his position to them, when for example a type in a command.
Could you please help me how to do it, and explain how does it work? I use the built-in mysql functions.

[iMr.WiFi..!]

-- This is smaller example for select and get the data
-- You Should Connect to ( your database ) with ( dbConnect )
-- And
-- The ( DB ) will be the definition of ( dbConnect )
local table = "My Table";
local IsTomOnDatabase = dbExec(DB,"SELECT * FROM "..table.." WHERE jatekosnev=?",'cmdTommy')
if #IsTomOnDatabase ~= 0 then -- this ( if ) will means if found cmdTommy in jatekosnev he will select it and return 1 or more
  outputChatBox(" CmdTommy is in table : ) ",root);
  outputChatBox(" And He is (X) :"..IsTomOnDatabase["X"],root);
  outputChatBox(" And He is (Y) :"..IsTomOnDatabase["Y"],root);
  outputChatBox(" And He is (Z) :"..IsTomOnDatabase["Z"],root);
end

 

[rusztamas]

5 minutes ago, iMr.WiFi..! said:

-- This is smaller example for select and get the data
-- You Should Connect to ( your database ) with ( dbConnect )
-- And
-- The ( DB ) will be the definition of ( dbConnect )
local table = "My Table";
local IsTomOnDatabase = dbExec(DB,"SELECT * FROM "..table.." WHERE jatekosnev=?",'cmdTommy')
if #IsTomOnDatabase ~= 0 then -- this ( if ) will means if found cmdTommy in jatekosnev he will select it and return 1 or more
  outputChatBox(" CmdTommy is in table : ) ",root);
  outputChatBox(" And He is (X) :"..IsTomOnDatabase["X"],root);
  outputChatBox(" And He is (Y) :"..IsTomOnDatabase["Y"],root);
  outputChatBox(" And He is (Z) :"..IsTomOnDatabase["Z"],root);
end

 

thank you but,
what does the # mean in the 6th line before IsTomOnDatabase?
and this one:? ~= 0

Edited June 13, 2017 by rusztamas

[iMr.WiFi..!]

Just now, rusztamas said:

what does the # mean in the 6th line before IsTomOnDatabase?

 

Number of the selected items

[Master_MTA]

4 hours ago, rusztamas said:

thank you but,
what does the # mean in the 6th line before IsTomOnDatabase?
and this one:? ~= 0

leanth  of table

[rusztamas]

i made a script, that some of the player's data gets recorded into my database. i am very new to mysql, but i need it, and found it useful. i am thankful for the replies, and explanations but i dont really understand it yet. 
 

1. local table = "My Table";
2. local IsTomOnDatabase = dbExec(DB,"SELECT * FROM "..table.." WHERE jatekosnev=?",'cmdTommy'

i've got these ones, but below it i dont. i dont know why i need # before isTomOnDatabase, and why not == instead of =~ and the meaning of 0.please help!

[pa3ck]

'#' gets the length (number of data entries) in the return table. It is recommended that you use callback functions when working with dbQuery (you will need to use dbQuery, dbExec is for inserts/updates/deletes etc).

dbQuery( function (qh)
    local result = dbPoll( qh, 0 )   -- Timeout doesn't matter here because the result will always be ready
    if #result > 0 then -- you need to check the length of the table because even if there is nothing found, result will always be true
      for index, rows in ipairs(result) do -- loop through the returned rows
        outputChatBox(rows["YOUR_DB_COLUMN_NAME_1"])
        outputChatBox(rows["YOUR_DB_COLUMN_NAME_2"])
        outputChatBox(rows["YOUR_DB_COLUMN_NAME_3"])
      end
    else
      outputChatBox("0 results found")
    end
end, connection, "SELECT * FROM YOUR_DB_TABLE_NAME" )

The structure of the returned data will have nested tables. Example:

local returnedData = {
  { 
    ["username"] = "my_username_1", 
    ["password"] = "my_pass_1", 
    ["serial"] = "my_serial_1"},
  },
  {
    ["username"] = "my_username_2", 
    ["password"] = "my_pass_2", 
    ["serial"] = "my_serial_2"},
  },
  { 
    ["username"] = "my_username_3", 
    ["password"] = "my_pass_3", 
    ["serial"] = "my_serial_3"},
  }
}

--[[

	This is the reason why you need to loop through the table and use the value variable like if it was a table

	returnedData[1][1] --> "my_username_1"
	returnedData[1][2] --> "my_pass_1"
    returnedData[1]["username"] --> "my_username_1"
    returnedData[2]["username"] --> "my_username_2"

    for k, rows in ipairs(returnedData) do
		outputChatBox(rows["username"]) 
    end
	
	Output:
	my_username_1
	my_username_2
	my_username_3

    for k, rows in ipairs(returnedData) do
		outputChatBox(rows["username"] .. " : " .. rows["password"]) 
    end
	
	Output:
	my_username_1 : my_password_1
	my_username_2 : my_password_2
	my_username_3 : my_password_3

]]

 

Edited June 14, 2017 by pa3ck

[rusztamas]

what is wrong with this one?
 

	DB = dbConnect( "mysql", "dbname=teszt;host=127.0.0.1", "root", "mypassword", "share=1" )
	local data = dbQuery(DB, "SELECT * FROM feltoltesek WHERE jatekosnev='"..jatekosnev.."'")
	if (data) then
		local x = tostring(data["x"])
		outputChatBox (""..x.."", jatekos)
	end

2nd line: attempt to concatenate local 'jatekosnev' (a nil value)

Edited June 14, 2017 by rusztamas

[pa3ck]

Please take your time, scroll up a bit and look at my code / wiki. What do you think you missed? You tell me. Compare my (or code from wiki) against yours and don't take callback into account, that is not the problem here. 
 

[rusztamas]

not to annoy you, i really appreciate your helpfulness, but i am really new to this part of programming, and i don't really understand the problem. i picked this one from an other code, in that one it seems to work, here it is not, and i dont really understand the code you posted above. I'm trying to learn all of the functions of programming on a template, and i try to learn that template, but here every commenter posted different codes, and i don't know which one is the best to learn. for example yours is an anonymous function, and i found it interesting.

[pa3ck]

I do understand that... but you're missing dbPoll which is basically present in every single example on the wiki and here as well.

[rusztamas]

function lekerdez(jatekos, parancs, jatekosnev)
	qh = dbQuery(db, "SELECT * FROM feltoltesek WHERE jatekosnev='"..jatekosnev.."'")
	result = dbPoll (qh, -1)
	local row = result
	if row then
		local x = tostring(result["x"])
		outputChatBox (""..x.."", jatekos)
	else
		outputChatBox ("Error!", jatekos)
	end
end
addCommandHandler ("lekerdez", lekerdez)

I made one with dbPoll, as you said, i found this example in an other script, but it should output an X coordinate in the chat, but it puts "nil".

[pa3ck]

As I said in my 'instructions' (I kinda regret writing it now, because you are clearly not using it), you will get a row of nested tables. If I did a SELECT * FROM players query, I would get number of rows back, right? So who's X position would result["x"] be? A random player? Maybe Chuck Norris?

local x = tostring(result[1]["x"]) -- first row

Here's an explanation again, this time no text, just a simple image..

[Master_MTA]

4 hours ago, rusztamas said:

function lekerdez(jatekos, parancs, jatekosnev)
	qh = dbQuery(db, "SELECT * FROM feltoltesek WHERE jatekosnev='"..jatekosnev.."'")
	result = dbPoll (qh, -1)
	local row = result
	if row then
		local x = tostring(result["x"])
		outputChatBox (""..x.."", jatekos)
	else
		outputChatBox ("Error!", jatekos)
	end
end
addCommandHandler ("lekerdez", lekerdez)

I made one with dbPoll, as you said, i found this example in an other script, but it should output an X coordinate in the chat, but it puts "nil".

check this one

function lekerdez(jatekos, parancs, jatekosnev)
	qh = dbQuery(db, "SELECT * FROM feltoltesek WHERE jatekosnev=?",jatekosnev)
	result = dbPoll (qh, -1)
	local row = result
	if #row~=0 then
	for k,v in ipairs(row)do
      outputChatBox(row[k].x,root,255,255,255)
      end
	else
		outputChatBox ("no data in database!", jatekos)
	end
end
addCommandHandler ("lekerdez", lekerdez)

i hope u understood this example