Extracted Database Data SQL

FiveM Resource Development Discussion
,
1

Hi there, i’m having some trouble with getting a number from the database.

Can anyone able to offer a little sql help? I am trying to add a badge number to police/EMS database. A column in users table. How would I get that badge number in a resource? Client or server sided? I thought this would do the trick, but it returns a null value

function getBadgeNumber(source)
    local result = MySQL.Sync.fetchAll('SELECT * FROM users WHERE identifier = @identifier',
    {
        ['@identifier'] = GetPlayerIdentifiers(source)[1]
    })

    if result[1] ~= nil and result[1].badge ~= nil then

        return result[1].badge
    end
end

Badge is the last column in my user table, like this.

image
image1156×465 28.6 KB

2

Can you show us your database structure?

3

image
image944×64 4.3 KB

4

There is no column identifier?

5

sorry it cut my line off, here is full structure

image
image1156×465 28.6 KB

6

So your code looks fine to me but i could be missing something maybe try some debugging for example try printing the GetPlayerIdentifiers(source)[1] and check this in your database and try to print the result object

7

I will give that a try.

Off topic, what’s wrong with database? Serious question, not being a shit

8

Well for example why are height and sex a varchar? also try not to select * if you only need badge then just select badge from

Check out this example by @Syntasu

image
image1078×119 12.9 KB

9

Ya i thought that too, i wrote some code to pull badge client side, but it wasn’t working so I started with the one i pasted above server side.

function getBadgeNumber(source)

local _source = source
local xPlayer = ESX.GetPlayerFromId(_source)	-- fetch identity in sync
	MySQL.Async.fetchAll(
	'SELECT badge FROM users WHERE identifier = @number',
	{
		['@number'] = xPlayer.identifier
	}, function(result)
		if result[1] ~= nil then
			return result[1]
		end
	end)
end
10

Ye you can’t use MySQL clientside :stuck_out_tongue:

11

I was able to make this work using the existing getOtherData function and adding the badge to it.
server:

ESX.RegisterServerCallback('esx_policejob:getOtherPlayerData', function(source, cb, target)

  if Config.EnableESXIdentity then

    local xPlayer = ESX.GetPlayerFromId(target)

    local identifier = GetPlayerIdentifiers(target)[1]

    local result = MySQL.Sync.fetchAll("SELECT * FROM users WHERE identifier = @identifier", {
      ['@identifier'] = identifier
    })

    local user          = result[1]
    local firstname     = user['firstname']
    local lastname      = user['lastname']
    local sex           = user['sex']
    local dob           = user['dateofbirth']
    local height        = user['height'] .. " Inches"
    local badge	        = user['badge']

    local data = {
      name        = GetPlayerName(target),
      job         = xPlayer.job,
      inventory   = xPlayer.inventory,
      accounts    = xPlayer.accounts,
      weapons     = xPlayer.loadout,
      firstname   = firstname,
      lastname    = lastname,
      sex         = sex,
      dob         = dob,
      height      = height,
      badge	  = badge
    }

Client:

	ESX.TriggerServerCallback('esx_policejob:getOtherPlayerData', function(data)

		local elements    = {}
		badgeNum = data.badge
		print("database " ..badgeNum)
		
		local playerPed = PlayerPedId()
		print ("clientnum " .. playerPed)
	
	end, GetPlayerServerId(player))

Now for some reason, if I am the first one to trigger that, it prints my badge number in the console no issues.
But the next person to trigger it also displays my badge number? It is not pulling their number from the database?

I am sure it is something simple I am missing, but I can’t wrap my head around it