Retrieving data from mysql

[kPkPT]

Hey guys,

So i made a relationship between my users database and the characters database and i currently got this on the code:

teststring = exports.mysql:_QuerySingle("SELECT * FROM users JOIN characters ON (users.username = characters.username)")

However if i would like to output it to the chat box how would i do it? Because we are joining two tables together, i don't know what to do anymore. Please someone help me?

Best regards,

Vasco

[IIYAMA]

iprint(teststring)

I am not familiar with the mysql resource, but if this is working like I think it would, use the iprint function. It will tell you exactly how the data looks like that you get back.

Visible in: /debugscript 3

[kPkPT]

Well i know it needs to load the data onto a table, because the result of loading it would have several rows with several variables. I don't know if i am explaining myself but this i am doing would possibly load several rows from mysql. However, i am not sure how LUA is loading them. I wanted it to load something like teststring[1][1] being  first 1 the row and second 1 the variable inside the row. Like if you defined it like this:

teststring = 
{
    {1,2,3,4,5},
   	{1,2,3,4,5},
	{1,2,3,4,5},
	{1,2,3,4,5},
	{1,2,3,4,5}
}

BUT i want to know how it is loading the data from MySQL. That above was just an example, so i can later use the variables to load characters.

Edited August 28, 2017 by kPkPT

[IIYAMA]

I am working with SQL, so I am not sure if the mysql resource returns the same results.

But if you use iprint, it will show you the table structure as well.

 

See this example at this page: https://wiki.multitheftauto.com/wiki/Iprint

Example results: (shows also tables inside tables)

[2016-10-01 21:25:43] INFO: { elem:vehicle[Monster 3]2ED3EF60, elem:vehicle[Monster 3]2ED3F620, elem:vehicle[Banshee]2ED3F548, elem:vehicle[Banshee]2ED3F590, elem:vehicle[Bullet]2ED3F5D8, elem:vehicle[BMX]2ED3FA58, elem:vehicle[BMX]2ED3FE00, elem:vehicle[Sparrow]2ED3F788, elem:vehicle[Sanchez]2ED3F6B0, elem:vehicle[Sanchez]2ED3F8A8, elem:vehicle[Sanchez]2ED3FE48, elem:vehicle[Jetmax]2ED3FC98, elem:vehicle[Dinghy]2ED3FA10, elem:vehicle[Marquis]2ED3FB78, elem:vehicle[Bandito]2ED3FAA0, elem:vehicle[Beagle]2ED3F980, elem:vehicle[Buffalo]2ED3FDB8, elem:vehicle[Sabre]2ED3F6F8, elem:vehicle[Caddy]2ED3F7D0, elem:vehicle[Sparrow]2ED3F938, elem:vehicle[Sanchez]2ED3FED8, elem:vehicle[Sanchez]2ED3FCE0, elem:vehicle[Sanchez]2ED3F860, elem:vehicle[Monster 3]2ED3F8F0, elem:vehicle[Monster 3]2ED3FC08, elem:vehicle[Buffalo]2ED3F9C8, elem:vehicle[Bandito]2ED3FAE8, elem:vehicle[Caddy]2ED3FB30, elem:vehicle[Sabre]2ED3F668, elem:vehicle[Bullet]2ED3FBC0, elem:vehicle[Banshee]2ED3FF20, elem:vehicle[Banshee]2ED3F818, elem:vehicle[Monster 3]2ED3FC50, elem:vehicle[Monster 3]2ED3FD28, elem:vehicle[Buffalo]2ED3FD70, elem:vehicle[Bandito]2ED3FE90, elem:vehicle[Caddy]2ED3F740, elem:vehicle[Sabre]2ED405E0, elem:vehicle[Bullet]2ED406B8, elem:vehicle[Banshee]2ED40748, elem:vehicle[Banshee]2ED401F0, elem:vehicle[Sanchez]2ED40508, elem:vehicle[Sanchez]2ED401A8, elem:vehicle[Sanchez]2ED40040, elem:vehicle[Sparrow]2ED40118, elem:vehicle[Sanchez]2ED40238, elem:vehicle[Sanchez]2ED3FFB0, elem:vehicle[Sanchez]2ED40598, elem:vehicle[Monster 3]2ED40550, elem:vehicle[Monster 3]2ED40700, elem:vehicle[Buffalo]2ED40280, elem:vehicle[Bandito]2ED403A0, elem:vehicle[Caddy]2ED400D0, elem:vehicle[Sabre]2ED402C8, elem:vehicle[Bullet]2ED40160, elem:vehicle[Banshee]2ED40790, elem:vehicle[Banshee]2ED40628, elem:vehicle[Sparrow]2ED3FFF8 }

 

Edited August 28, 2017 by IIYAMA

[kPkPT]

function teste()
	testestring = exports.mysql:_Query("SELECT * FROM characters JOIN users WHERE users.username = characters.username")
	if(testestring) then
		for i, string in ipairs(testestring) do
			iprint(testestring[i])
		end
	end
end
addEventHandler("teste",getRootElement(),teste)

Managed to figure it out, that will print all character data registered under that username  

thanks for helping me out

[IIYAMA]

Good job!

[Pirulax]

By the way, if you want to use built-in functions, then you should use them like this:

dbQuery(function(qh) 
    local poll = dbPoll(qh,0) -- timeout doesnt matter here, because the result will be ready always.
    for _,v in ipairs(poll) do
      	local charid = v["charid"]
      	print(charid) --< Prints the charid into the debugscript 3, note, this will be printed as many time as #poll is.
    end
    print(charid) --< Prints nil, since the variable 'charid' was created in the loop.
end,{},con,"SELECT * FROM characters WHERE ownerid=?",ownerid)

OR use this when you create an account, and want to retrieve the accountID of it.

passwordHash(passwordEnteredByUserGoesHere,"bcrypt",{usr},function(pass,usr) --//** always use some encrypting algorithm.
  dbQuery(function(qh) 
     local _,_,id = dbPoll(qh,0) -- timeout doesnt matter here, because the result will be ready always.
     print(id)
  end,{},con,"INSERT INTO accounts SET usrname=?, pass=?",usr,pass)
end

 

I hope you can understand what i wrote here, if not than drop me a pm.

[IIYAMA]

• You can skip the callbackArguments, unless you want to use them.

dbQuery ( [ function callbackFunction, [ table callbackArguments, ] ] element databaseConnection, string query [, var param1 [, var param2 ...]] )

 

• Keep in mind that print and iprint are not the same. Print will end up in the server window, while iprint ends up in your debug window.

 

Everything is working fine for you?