How can I send results from the SELECT * FROM command on the chat?

[dronmefe]

Hi, this is my command:

addCommandHandler("test", function ()
	local result = mysql:query_fetch_assoc("SELECT * FROM characters WHERE account='47'")
	outputChatBox(result)
end)

and i have error Bad argument @ 'outputChatBox' [Expected string at argument 1, got table].

I was looking for these types of topics, but I couldn't find anything, please help me!

[Patrick]

-- loop trough the 'result' table

for i, v in ipairs(result) do
    outputChatBox(v)
end

 

Edited January 9, 2020 by stPatrick

[dronmefe]

thanks! what can I do to make the gridlist not "go" under guiCreateStaticImage when I click somewhere else?

[IIYAMA]

4 hours ago, dronmefe said:

thanks! what can I do to make the gridlist not "go" under guiCreateStaticImage when I click somewhere else?

The very reason the gui-Static-Image goes on top, is because you clicked on the bounding box of the image. To prevent clicking, disable the gui-Static-Image: https://wiki.multitheftauto.com/wiki/GuiSetEnabled

[dronmefe]

Thanks. I have other problem.

addCommandHandler("elo", function(plr, cmd)
	local id = getElementData(plr, "gameaccountid")
	local data = mysql:query_fetch_assoc("SELECT * FROM characters WHERE account='" .. id .. "' AND cked = 0")
	local nick = data["charactername"]
	outputChatBox(nick)
end)

I already know how it works.

There are two characters in the database with the same account ID but only one is displayed. please help.

[IIYAMA]

55 minutes ago, dronmefe said:

There are two characters in the database with the same account ID but only one is displayed. please help.

I am not sure what kind of software your are using, but it looks old.

The fetch function normally would use a loop to fetch new data from the handler. But in your case I do not see an handler at all.

query + make the request > Then start fetching as long as there is data with a loop.

 

Are you able to connect manually to your db?

https://wiki.multitheftauto.com/wiki/DbConnect

 

Or can you provide some information about the socket/resource you are using?

 

 

 

[dronmefe]

-- ToDatabase - Internal function, to spawn a DB connection
function connectToDatabase(res)
	mysqlConnection = mysql_connect(hostname, username, password, database, port)
	
	if (not mysqlConnection) then
		if (res == getThisResource()) then
			cancelEvent(true, "Cannot connect to the database.")
		end
		return nil
	end
	
	return nil
end
addEventHandler("onResourceStart", getResourceRootElement(getThisResource()), connectToDatabase, false)
	
-- destroyDatabaseConnection - Internal function, kill the connection if theres one.
function destroyDatabaseConnection()
	if (not mysqlConnection) then
		return nil
	end
	mysql_close(mysqlConnection)
	return nil
end
addEventHandler("onResourceStop", getResourceRootElement(getThisResource()), destroyDatabaseConnection, false)

-- do something usefull here
function sc_journalsQLError(str)
	local message = str or 'N/A'
	outputDebugString("mysql ERROR "..mysql_errno(mysqlConnection) .. ": " .. mysql_error(mysqlConnection))
	exports['sc_journals']:logMessage("mysql ERROR :O! [QUERY] " .. message .. " [ERROR] " .. mysql_errno(mysqlConnection) .. ": " .. mysql_error(mysqlConnection), 24)
end

function getFreeResultpoolID()
	local size = #resultpool
	if (size == 0) then
		return 1 
	end
	for index, query in ipairs(resultpool) do
		if (query == nil) then
			return index
		end
	end
	return (size + 1)
end

------------ EXPORTED FUNCTIONS ---------------

function ping()
	if (mysql_ping(mysqlConnection) == false) then
		-- FUU, NO MOAR CONNECTION
		destroyDatabaseConnection()
		connectToDatabase(nil)
		if (mysql_ping(mysqlConnection) == false) then
			sc_journalsQLError()
			return false
		end
		return true
	end

	return true
end

function escape_string(str)
	if (ping()) then
		return mysql_escape_string(mysqlConnection, str)
	end
	return false
end

function query(str)
	if sqllog then
		exports['sc_journals']:logMessage(str, 24)
	end
	countqueries = countqueries + 1
	
	if (ping()) then
		local result = mysql_query(mysqlConnection, str)
		if (not result) then
			sc_journalsQLError(str)
			return false
		end

		local resultid = getFreeResultpoolID()
		resultpool[resultid] = result
		return resultid
	end
	return false
end

function unbuffered_query(str)
	if sqllog then
		exports['sc_journals']:logMessage(str, 24)
	end
	countqueries = countqueries + 1
	
	if (ping()) then
		local result = mysql_unbuffered_query(mysqlConnection, str)
		if (not result) then
			sc_journalsQLError(str)
			return false
		end

		local resultid = getFreeResultpoolID()
		resultpool[resultid] = result
		return resultid
	end
	return false
end

function query_free(str)
	local queryresult = query(str)
	if  not (queryresult == false) then
		free_result(queryresult)
		return true
	end
	return false
end

function rows_assoc(resultid)
	if (not resultpool[resultid]) then
		return false
	end
	return mysql_rows_assoc(resultpool[resultid])
end

function fetch_assoc(resultid)
	if (not resultpool[resultid]) then
		return false
	end
	return mysql_fetch_assoc(resultpool[resultid])
end

function free_result(resultid)
	if (not resultpool[resultid]) then
		return false
	end
	mysql_free_result(resultpool[resultid])
	table.remove(resultpool, resultid)
	return nil
end

-- incase a nub wants to use it, FINE
function result(resultid, row_offset, field_offset)
	if (not resultpool[resultid]) then
		return false
	end
	return mysql_result(resultpool[resultid], row_offset, field_offset)
end

function num_rows(resultid)
	if (not resultpool[resultid]) then
		return false
	end
	return mysql_num_rows(resultpool[resultid])
	
end

function insert_id()
	return mysql_insert_id(mysqlConnection) or false
end

function query_fetch_assoc(str)
	local queryresult = query(str)
	if  not (queryresult == false) then
		local result = fetch_assoc(queryresult)
		free_result(queryresult)
		return result
	end
	return false
end

function query_rows_assoc(str)
	local queryresult = query(str)
	if  not (queryresult == false) then
		local result = rows_assoc(queryresult)
		free_result(queryresult)
		return result
	end
	return false
end

function query_insert_free(str)
	local queryresult = query(str)
	if  not (queryresult == false) then
		local result = insert_id()
		free_result(queryresult)
		return result
	end
	return false
end

function escape_string(str)
	return mysql_escape_string(mysqlConnection, str)
end

function debugMode()
	if (sqllog) then
		sqllog = false
	else
		sqllog = true
	end
	return sqllog
end

function returnQueryStats()
	return countqueries
	-- maybe later more
end

this script, I only removed the database login details

[dronmefe]

@IIYAMA Can you help me with this?

[IIYAMA]

53 minutes ago, dronmefe said:

@IIYAMA Can you help me with this?

I am missing some of the functions. Also there is 0 documentation...

 

Please use the default functions:

local DBConnection 

addEventHandler("onResourceStart", resourceRoot, 
function () 
	DBConnection = dbConnect (  "mysql", "dbname=DBNAME;host=HOST;charset=utf8", "USERNAME", "PASSWORD" )
	if not DBConnection then
		cancelEvent(true, "Can't connect to the database.")
	end
end)



function query(...)
    local queryHandle = dbQuery(DBConnection, ...)
    if (not queryHandle) then
        return nil
    end
    local rows = dbPoll(queryHandle, -1)
    return rows
end


addCommandHandler("elo", function(plr, cmd)
	local id = getElementData(plr, "gameaccountid")
	if id then
		id = tostring(id)
		local data = query("SELECT * FROM characters WHERE account='" .. id .. "' AND cked = 0")
		if data then
			iprint("data", data)
			for i=1, #data do
				local character = data[i]
				iprint("character", character)
				local nick = character["charactername"]
				if nick then
					outputChatBox(nick)
				end
			end
		else
			iprint("no data")
		end
	end
end)