dbQuery, setElementData

[fairyoggy]

How can I use reading data from two tables at once in one function?

There are two tables:
1) jobs_sys(x,y,z)

2) jobs(money)

For the first table, I have everything ready but for second...

How do I make a transfer "money" from second table to setElementData ?

function loadAllJobs(dbJobsConnection)
local results = dbPoll(dbJobsConnection,0)
for index, jobs_sys in pairs(results) do
jobsObject = createMarker(jobs_sys.x,jobs_sys.y,jobs_sys.z,"cylinder",1,255,0,0,255)
setElementData(jobsObject, "uid", jobs_sys.uid)
setElementID(jobsObject,jobs_sys.uid)
-- need this line setElementData(jobsObject,"money",jobs.money)
end
end

addEventHandler("onResourceStart", resourceRoot, function ()
dbQuery(loadAllJobs, dbJobsConnection, 'SELECT * FROM jobs_sys')
end)

i tried like this 

function loadAllJobs(dbJobsConnection)
local results = dbPoll(dbJobsConnection,0)
for index, jobs_sys in pairs(results) do
jobsObject = createMarker(jobs_sys.x,jobs_sys.y,jobs_sys.z,"cylinder",1,255,0,0,255)
setElementData(jobsObject, "uid", jobs_sys.uid)
setElementID(jobsObject,jobs_sys.uid)
-- need this line setElementData(jobsObject,"money",jobs.money)
end
end

function loadAllJobs2(dbJobsConnection)
local results2 = dbPoll(dbJobsConnection,0)
for index2, jobs in pairs(results2) do
setElementData(jobsObject, "money", jobs.money)
end
end

addEventHandler("onResourceStart", resourceRoot, function ()
dbQuery(loadAllJobs, dbJobsConnection, 'SELECT * FROM jobs_sys')
dbQuery(loadAllJobs2, dbJobsConnection, 'SELECT * FROM jobs')
end)

but it doesn't work as planned. elementData "money" not assigned to all markers.

Edited June 15, 2021 by oggygod

[IIYAMA]

56 minutes ago, oggygod said:

How do I make a transfer "money" from second table

Do a left (table) join.

https://www.w3schools.com/sql/sql_join_left.asp

This is the default way data get aligned and merged from different tables.

[fairyoggy]

@IIYAMA

How to use it correctly?

i tried like this:

function loadAllJobs(dbJobsConnection)
local results = dbPoll(dbJobsConnection,0)
for index, jobs_sys in pairs(results) do
jobsObject = createMarker(jobs_sys.x,jobs_sys.y,jobs_sys.z,"cylinder",1,255,0,0,255)
setElementData(jobsObject, "uid", jobs_sys.uid)
setElementID(jobsObject,jobs_sys.uid)
setElementData(jobsObject,"money",jobs_sys.money)
end
end

addEventHandler("onResourceStart", resourceRoot, function ()
dbQuery(loadAllJobs, dbJobsConnection, 'SELECT * FROM jobs_sys LEFT JOIN jobs')
end)

But something is wrong.

Looking at my old code(This code in the first post):

 

function loadAllJobs(dbJobsConnection)
local results = dbPoll(dbJobsConnection,0)
for index, jobs_sys in pairs(results) do
jobsObject = createMarker(jobs_sys.x,jobs_sys.y,jobs_sys.z,"cylinder",1,255,0,0,255)
setElementData(jobsObject, "uid", jobs_sys.uid)
setElementID(jobsObject,jobs_sys.uid)
end
end

addEventHandler("onResourceStart", resourceRoot, function ()
dbQuery(loadAllJobs, dbJobsConnection, 'SELECT * FROM jobs_sys')
end)

If use my old code and add the line smth like this:

outputChatBox(jobs_sys.uid, thePlayer, 255, 0, 0)

We will receive a list of numbers of two markers in the chat Screen1

but if we use the new code and draw the output of these numbers, we get duplication of these numbers Screen2

P.S: Both tables use the string "uid" Maybe this is related to this, but what to do about it?

Table1 Table2

What is it all about ...

With the help of you, I was able to withdraw the line money from table 2, to check I used the method above

outputChatBox(jobs_sys.money, thePlayer, 255, 0, 0)

and got the same duplication

Screen

Plus, this line stopped working for markers other than the first:

setElementID(jobsObject,jobs_sys.uid)

What have I done wrong?

Edited June 15, 2021 by oggygod

[IIYAMA]

25 minutes ago, oggygod said:

What have I done wrong?

The alignment of the tables have to be defined.

Try this:

SELECT jobs_sys.uid, jobs_sys.x, jobs_sys.y, jobs_sys.z, jobs.money
FROM jobs_sys
LEFT JOIN jobs
ON jobs_sys.uid = jobs.uid

 

[fairyoggy]

9 minutes ago, IIYAMA said:

The alignment of the tables have to be defined.

Try this:

SELECT jobs_sys.uid, jobs_sys.x, jobs_sys.y, jobs_sys.z, jobs.money
FROM jobs_sys
LEFT JOIN jobs
ON jobs_sys.uid = jobs.uid

 

Perfect, you are a genius. Thank you so much

[IIYAMA]

You can also use this selector if you want all columns from a specific table:

jobs_sys.*

But it could override some of the table values, if the names are the same from the 2+ tables.