Select the specific row from database

[SinaAmp]

Hello guys

i want to UPDATE data in database for the user who buy in store and retrive it after player login

but the problem is the player gets the latest row data not his saved data with his account name 

here is my script:

Spoiler

function BuySkin159 (theplayer)

    if(getElementType(theplayer) ~= "player") then return end -- if theplayer not player stop continue

    local account = getPlayerAccount(theplayer)

    local skinprice = 5000

    if(account) then -- Does the player have an account?

       if(isGuestAccount(account)) then return end -- if account guest account stop continue

       local accName = getAccountName(account)

       local GotSkinData = dbPoll(dbQuery(db,"SELECT Account,Skin,ID FROM SkinShop"), -1)

 

       if (getElementModel(theplayer) == 159) then

        outputChatBox("shoma in skin ra nemitavanid bekharid!chon darid!")

 

    else if (getElementModel(theplayer) ~= 159) and (GotSkinData[1].Account == tostring(accName)) then

        takePlayerMoney(theplayer, 5000)

        setElementModel(theplayer, 159)

        local ID = getFreeID()

        local skinid = 159

        local myData = dbExec(db,"UPDATE SkinShop SET Skin = ?",skinid)

        skin = getElementModel(theplayer)

        outputChatBox("#00ff00 [Done]: #ffffffskin sina shoma be id #ff0000"..skin.."#ffffff taghir kard",theplayer, 255,255,255,true)

 

        else

            takePlayerMoney(theplayer, 5000)

            setElementModel(theplayer, 159)

            local ID = getFreeID()

            local skinid = 159

            local myData = dbExec(db,"INSERT INTO SkinShop VALUES (?, ?, ?)",ID,accName,skinid)

            skin = getElementModel(theplayer)

            outputChatBox("#00ff00 [Done]: #ffffffskin amp shoma be id #ff0000"..skin.."#ffffff taghir kard",theplayer, 255,255,255,true)

       end

    end

end

end

addEventHandler("onMarkerHit", marker1, BuySkin159)

and login script:

Spoiler

function afterlogin(_,account)

    local SkinData = dbPoll(dbQuery(db,"SELECT Account,Skin,ID FROM SkinShop"), -1)

    local accName = getAccountName(account)

    if (SkinData) and (SkinData[1]) and (SkinData[2]) then

        if (SkinData[1].Account == tostring(accName)) then

        setElementModel(source, SkinData[2].Skin )

        end

    end

end

addEventHandler("onPlayerLogin",root, afterlogin)

@Burak5312 @The_GTA @IIYAMA

Edited January 30, 2022 by SinaAmp

[βurak]

Quote

but the problem is the player gets the latest row data not his saved data with his account name 

Have you tried the where clause? using this you can update the column with the account name

example:

 local myData = dbExec(db,"UPDATE SkinShop SET Skin=? WHERE Account=?",skinid, accName) -- Update name of Skin column in SkinShop table matching accName

you can do the same in select clause

local GotSkinData = dbPoll(dbQuery(db,"SELECT Account,Skin,ID FROM SkinShop WHERE Account=?", accName), -1)

 

Edited January 30, 2022 by Burak5312

[SinaAmp]

im testing the WHERE that solved the updating specific row data problem

but after login the skins not change

Edited January 30, 2022 by SinaAmp

[βurak]

not sure but can you test this? but backup your old code

function afterlogin(_,account)
    local accName = getAccountName(account)
    local SkinData = dbPoll(dbQuery(db,"SELECT * FROM SkinShop WHERE Account=?",accName), -1)  
    if(SkinData) then
       if(#SkinData > 0) then
          for _,row in ipairs(SkinData) do
              setElementModel(source, row["Skin"])
          end
       end
    end
end
addEventHandler("onPlayerLogin",root, afterlogin)

Edited January 30, 2022 by Burak5312

[SinaAmp]

Thank you bro that worked

can you please expline this line of your code?

if(#SkinData > 0) then
          for _,row in ipairs(SkinData) do
              setElementModel(source, row["Skin"])
          end

 

[βurak]

To be honest, I don't know much either, but let me explain.

The SkinData query returns a table value that gets all the columns that contain your account then we include it in a loop using a for loop and we can get the data we want from there If we wanted to get the id as an example, then we could get it as

row["ID"]

The if code checks if the number of columns returned is greater than 0.

if(#SkinData > 0)

 

[SinaAmp]

thank you bro for explinition

[βurak]

my pleasure  

[IIYAMA]

@Burak5312

@SinaAmp

local SkinData = dbPoll(dbQuery(db,"SELECT * FROM SkinShop WHERE Account=?",accName), -1)  
if #SkinData > 0 then
  
end

The reason why this #SkinData > 0 is used, is because the database query is returning always a table on success. This table contains all results of that query.

The # is used for get the length/item count of that table.

 

For example if you have 2 accounts with the same account name. The SkinData table contains 2 items.

#SkinData -- results in the value 2.

 

You can add a safety/optimization keyword inside of the query to always return 1 items, which is LIMIT <max items to return>. But not having that inside of the code is not game breaking, but can reduce query execution time. Yet that does not change that SkinData will hold a table value on success.

SELECT * FROM SkinShop WHERE Account=? LIMIT 1

 

 

 

 

[SinaAmp]

Thank you @IIYAMA for explinition

isn't my script is to much?

i want to add more markers but i need to copy paste this code for every marker

Spoiler

function BuySkin159 (theplayer)

    if(getElementType(theplayer) ~= "player") then return end -- if theplayer not player stop continue

    local account = getPlayerAccount(theplayer)

    local skinprice = 5000

    if(account) then -- Does the player have an account?

       if(isGuestAccount(account)) then return end -- if account guest account stop continue

       local accName = getAccountName(account)

       local GotSkinData = dbPoll(dbQuery(db,"SELECT Account,Skin,ID FROM SkinShop"), -1)

 

       if (getElementModel(theplayer) == 159) then

        outputChatBox("shoma in skin ra nemitavanid bekharid!chon darid!")

 

    else if (getElementModel(theplayer) ~= 159) and (GotSkinData[1].Account == tostring(accName)) then

        takePlayerMoney(theplayer, 5000)

        setElementModel(theplayer, 159)

        local ID = getFreeID()

        local skinid = 159

        local myData = dbExec(db,"UPDATE SkinShop SET Skin = ?",skinid)

        skin = getElementModel(theplayer)

        outputChatBox("#00ff00 [Done]: #ffffffskin sina shoma be id #ff0000"..skin.."#ffffff taghir kard",theplayer, 255,255,255,true)

 

        else

            takePlayerMoney(theplayer, 5000)

            setElementModel(theplayer, 159)

            local ID = getFreeID()

            local skinid = 159

            local myData = dbExec(db,"INSERT INTO SkinShop VALUES (?, ?, ?)",ID,accName,skinid)

            skin = getElementModel(theplayer)

            outputChatBox("#00ff00 [Done]: #ffffffskin amp shoma be id #ff0000"..skin.."#ffffff taghir kard",theplayer, 255,255,255,true)

       end

    end

end

end

addEventHandler("onMarkerHit", marker1, BuySkin159)

[βurak]

you can use tables for this

local skinMarkers = {} --create table to store markers

function createSkinMarker(x, y, z, interior) --skin marker creation function
   local marker = createMarker(x, y, z, "cylinder", 2.0, 255, 0, 0, 255) 
   setElementInterior(marker, interior)
   table.insert(skinMarkers, marker) -- transfer the created marker to the skinMarkers table
end

addEventHandler("onMarkerHit", root, -- Define event handler for markers in skinMarkers table
    function(hitElement, matchingDimension)
       if(getElementType(hitElement) == "player") then -- hitElement is player?
          for i=1,#skinMarkers do 
          	 if(source == skinMarkers[i]) then -- the marker that the player touched is the skin marker?
          	    -- YOUR CODES HERE
          	 end
          end
       end
    end
)

 

then you can call the createSkinMarker function to create markers at multiple points

createSkinMarker(0, 0, 2, 0) -- create skin marker 0, 0, 2 position with interior id 0
createSkinMarker(1, 3, 2, 0) -- create skin marker 1, 3, 2 position with interior id 0
createSkinMarker(3, 20, 2, 0) -- create skin marker 3, 20, 2 position with interior id 0

 

Edited January 30, 2022 by Burak5312

[SinaAmp]

thank you for help bro

@IIYAMA do you know is it better to create table for every resource Separately or set most of the data's in one table for performance?

[IIYAMA]

27 minutes ago, SinaAmp said:

@IIYAMA do you know is it better to create table for every resource Separately or set most of the data's in one table for performance?

Database table or Lua table?

In case of a database, the less data is in a single table, the faster it can find items. But when an index is applied. You probably wouldn't notice the difference for while.

[SinaAmp]

@IIYAMA Thank you

I mean database table

[IIYAMA]

14 minutes ago, SinaAmp said:

@IIYAMA Thank you

I mean database table

If you really want to improve performance, you should make use of callbacks.

https://wiki.multitheftauto.com/wiki/DbQuery

See 3e and 4e example.

If not used, your MTA scripts will stop doing anything until there is response from the database. The database and MTA are running on different threads. Those shouldn't wait for each other. If the database thread takes too long, it will create lag in your MTA server, because the server is simply unable to process things.

 

 

 

 

[SinaAmp]

hello guys again

I'd like to save player account names in the database only once and prevent duplicate new rows, but when I try to compare player account names with saved names in the database, I get "attempt to index field '?/ nil value" I know I don't have any data in the database that causes this error, but I want to prevent the player who saved his data before from adding new row again

function getpaccount (_,account)
	local AccName = getAccountName(account)
	local AccData = dbPoll(dbQuery(db,"SELECT ID,Account FROM stats"), -1)
	if(isGuestAccount(account)) then return end
	if (AccData) then
		if (AccData[2].Account == tostring(AccName)) then
			return false
		else
				local ID = getFreeID()
				local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)",ID, AccName)
		end
	end
end
addEventHandler("onPlayerLogin",root, getpaccount)

@Burak5312

[βurak]

in fact, you can only add it once when the player is a new registration, you don't need to do it this way.

 

local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)",ID, AccName)

 

you can do something like this to avoid multiple column records but I'm not sure it will work

function getpaccount (_,account)
	local AccName = getAccountName(account)
	local AccData = dbPoll(dbQuery(db,"SELECT * FROM stats WHERE Account=? LIMIT 1", AccName), -1) -- search for a column in the database for this account
	if(AccData) then 
       if(#AccData > 0) then return end -- do not continue if there is any record
	end
	--if there is no such record then save it to the database
	local ID = getFreeID()
	local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)",ID, AccName)
end
addEventHandler("onPlayerLogin",root, getpaccount)

Edited January 31, 2022 by Burak5312

[SinaAmp]

thank you bro

also i worked on another way and that worked too but not trust able 

function getpaccount (_,account)
	local AccName = getAccountName(account)
	local AccData = dbPoll(dbQuery(db,"SELECT ID,Account FROM stats"), -1)
	if(isGuestAccount(account)) then return end
	if (AccData) then
		if not getAccountData(account, "FirstTime") then
			setAccountData(account, "FirstTime", true) 
			local ID = getFreeID()
			local SetData = dbExec(db,"INSERT INTO stats (ID,Account) VALUES (?, ?)",ID, AccName)
		else
			return
		end
	end
end
addEventHandler("onPlayerLogin",root, getpaccount)

 

[βurak]

your welcome