[SQL Request] Getting only one result from MySQL and fetch it

FiveM Resource Development & Modding Discussion
1

Hi everyone,

I’m trying to figure out why I can’t access the fields containing in my MySQL request :

Here is the request I execute on player spawn.

RegisterServerEvent('jobssystem:getPlayerJob')
AddEventHandler('jobssystem:getPlayerJob', function()
  TriggerEvent('es:getPlayerFromId', source, function(user)
    local player = user.identifier
    local executed_query = MySQL:executeQuery("SELECT j1.`tag` AS job_tag, j1.`libelle` AS job_libelle, j1.`is_legal` AS is_legal, c1.`x` AS entreprise_x, c1.`y` AS entreprise_y, c1.`z` AS entreprise_z, c2.`x` AS stuff_x, c2.`y` AS stuff_y, c2.`z` AS stuff_z, c3.`x` AS vehicule_x, c3.`y` AS vehicule_y, c3.`z` AS vehicule_z FROM jobs j1 INNER JOIN users ON users.`job_id` = j1.id INNER JOIN coords c1 ON j1.`entreprise_coord_id` = c1.id INNER JOIN coords c2 ON j1.`stuff_coord_id` = c2.id INNER JOIN coords c3 ON j1.`vehicule_coord_id` = c3.id WHERE users.identifier = '@userId'", {['@userId'] = player})
    local result = MySQL:getResults(executed_query, {'job_tag', 'job_libelle', 'is_legal', 'entreprise_x', 'entreprise_y', 'entreprise_z', 'stuff_x', 'stuff_y', 'stuff_z', 'vehicule_x', 'vehicule_y', 'vehicule_z'})
    if(result) then
      playerJob = result
    end
    TriggerClientEvent("cli:getPlayerJob", source, playerJob)
  end)
end)

If I execute it on MySQL it returns exactly what I want :

request.png1667×247 27.8 KB

But, in my client file, I can’t access this field.

PLAYERJOB = {}
RegisterNetEvent("cli:getPlayerJob")
AddEventHandler("cli:getPlayerJob", function(job)
    PLAYERJOB = job
end)

When I debug what’s inside PLAYERJOB it says :

I tried to display with this methods :

PLAYERJOB[1].job_libelle
PLAYERJOB.job_libelle
for _, item in pairs(PLAYERJOB) do
    DisplayDebug(item.job_libelle)
end

None of this is working. Am I missing something ?
My MySQL request should return only one element and I would like to be able to access it like an objet : myObject.myProperty

Is there a way to do it ?

Thank’s in advance for your help !

Leor,

2

If that is your EXACT code, you have a " misplaced.

@userId’", {[’@userId’] = player})

Should be

@userId’, {[’@userId’] = player})

Also, does trigger/add event need to be in single quotes? I have been using it in single quotes.

(I am very new to Lua, inline code and I don’t get along)

3

I don’t know why it’s not well formating on this site but, no, I haven’t a " misplaced. The request does execute well.
Concerning the trigger/add event to be in dual quotes or single one, I have tested both and it’s the exact same thing. I also have mods with single and mods with doubles.

I’m new too in Lua and I don’t see what’s wrong with my code.

Thank’s anyway, looking for more experienced developper :wink:

4

Hi people,

I still don’t figure out how to solve my problem.
Looking for help !

5

Hi,

Result contain all rows, you can use it like that

RegisterServerEvent ('job:GetJobID')
AddEventHandler ('job:GetJobID', function ()

	TriggerEvent('es:getPlayerFromId', source, function(user)
    
        local player = user.identifier
		
		local executed_query_cop = MySQL:executeQuery ("SELECT * FROM users WHERE identifier = '@username'", {['@username'] = player })
		local result_job = MySQL:getResults (executed_query_cop, { 'job' }, "identifier")
		if(result_job)then
			for k, v in ipairs (result_job)do				
				TriggerClientEvent ('job:FinishGetJobID', source, v.job)
			end			
		end	
		
	end)
end)
6

Hi,

Oh thank’s Seb, I will try this out in few hours and come back to tell you if it worked.
It seems good btw, I didn’t test that solution at all.

Thank’s a lot.

Leor

8 
if(result) then
      playerJob = result
end

Try with:

if(result) then
      playerJob = result[1]
end
9

I already tried your solution.
Didn’t work

I will try Seb’s one in a few minutes and return here to give a feedback

11

And client side, how do I access the field ?

12

Hi guys,
@TrinnityRE, @Seb

I’m back with some bad news, none of your solutions is working.
I’m always getting null values when I try to display what’s inside the PLAYERJOB variable.

Is there a way to debugg variable that are defined in the server side file ? Maybe my request return null values (even if when executed on MySQL it returns what I want)

My request is likely identical to request that works on other scripts, I can’t find what’s going on, on this one.

We need to continue investigate.

13

Download it: https://we.tl/KJF8cjw0Vl

Paste it into your resources folder. Then in your server script, at the top add this line:

local inspect = require 'resources/[here path to inspect.lua]'

e.g. if your path is: resources/inspect/inspect.lua

Paste to your server script this:

local inspect = require 'resources/inspect/inspect'

Then, try to print into the console your MySQL result, just do this:

if (result) then
   print("Result: " .. inspect(result))
end

Then check in server console what does result contains.

1 Like
14

Firstly to make this come as a readable text you want to do print(json.encode(PLAAYERJOB)) This will out put it as a text not a table.