[QUESTION] SQL Database problem

[DriFtyZ]

Hello fellas i have an important question about the databases in lua scripting, i have made a carshop system which stores the vehicles inside the database and i have made a menu which the user selects to spawn despawn his vehicle. if the user has 2 same vehicles how the script will know wich one from the database will pick?
image of database:

Edited May 28, 2017 by Dutchman101
Fix topic title for section requirements

[Syntrax#]

Try to use the license plate or such to recognise which vehicle you are going to use since those are not the same usually

[DriFtyZ]

this is my vehicle menu if i want to find the vehicle for something that appears on the menu

[Syntrax#]

add a license play row and then try getting the vehicle based on the selected license plate

 

[DriFtyZ]

true but what if i wanted to avoid that i mean i see in CIT server the vehicles have a row ID number in front of the name i just wanted to learn how this can be possible without creating tables for each player

[pa3ck]

Basically every single time you create a new SQL table, you will need a primary key with auto increment. This is a number that gets incremented every time you insert a new row. You don't have to do anything with it, SQL will handle that for you, all you have to do is create the column. 

This way every vehicle will have a unique ID.

[DriFtyZ]

i tried to use primary keys, with auto increasement but how thats gonna help me with my previous question.

[pa3ck]

Your question was how to add row ID to cars, I said use primary, A_I keys, I believe that's pretty much the answer for your question? 

[DriFtyZ]

i want to make vehicle ids but for the each player specific i mean if the user driftyz has 2 vehicle there should his name in the first column on the first 2 rows and then id 1 which means his first vehicle on the list and id 2 but if some other user buy  a car i don't want him ta have the same id increasement as driftyz but 1 again as its gonna be his first car

[pa3ck]

When you do a query to get all of the vehicles for the specific player you get a table back. Maybe you could use the table index there?

[DriFtyZ]

you mean table.insert string on an empty table?

[pa3ck]

When you spawn the cars you probably use a table or something already so you know whom the car belongs to, right? 

[Addlibs]

If this is SQLite, I believe all tables have by default a column "rowid" which is kinda hidden by default, and not selected when selecting *, so just try to SELECT rowid, * FROM that table and then use the rowid column for identification - for example, in a GUI with a gridlist, you'd want to guiGridListGetItemData to store the rowid there, and send the server a spawn request along with that rowid so the server can just select WHERE rowid = ?.

This is the rowid column ^

Edited May 27, 2017 by MrTasty

[DriFtyZ]

i make it to find the car through the licence plate i guess ill stick it for now unless i find some easier with less "lag or bandwidth" way than trigger server events and client to fetch and load data to menu

[Addlibs]

Well, then at least make sure that the plate is truly unique, otherwise the script will get confused and you'll end up spawning the first vehicle which has the plate, regardless of the actual intended vehicle.

[DriFtyZ]

yes i've made it WHERE ownerName=? AND veh_name=? AND veh_plate=?

[Syntrax#]

I'll put some time into this tonight.I'll write the script in order to put ID's so you basically dont have to do anything

Edited May 27, 2017 by Syntrax#

[DriFtyZ]

thank you syntrax i think i made it :~ somewhere and when i spawn a car it despawns someone else's 

[DriFtyZ]

to be more specific when the user clicks the spawn button on gui it triggers the server event which is the follow:

addEvent("spawnVehicleS", true)
function spawnVehicleS ( player, pX, pY, pZ, vehNameS, vehPlateS )
	if player and pX and pY and pZ and vehNameS and vehPlateS then
		if playerVeh1 then
			destroyElement(playerVeh1)
		end
		local accName = getAccountName(getPlayerAccount(player))
		local vehicleQuery = dbQuery(vehicleDb,"SELECT * FROM vehicles WHERE ownerName=? AND veh_name=? AND veh_plate=?", accName, vehNameS, vehPlateS)
		local vehicleQueryResult = dbPoll(vehicleQuery, -1)
		if #vehicleQueryResult > 0 then
			for rid, row in ipairs (vehicleQueryResult) do
				local veh_id = row.veh_id
				local veh_plate = row.veh_plate
				local colR1 = row.colR1
				local colR2 = row.colR2
				local colR3 = row.colR3
				local colG1 = row.colG1
				local colG2 = row.colG2
				local colG3 = row.colG3
				local colB1 = row.colB1
				local colB2 = row.colB2
				local colB3 = row.colB3
				local col_hs1 = row.col_hs1
				local col_hs2 = row.col_hs2
				local col_hs3 = row.col_hs3
				local veh_Health = row.veh_Health
				playerVeh1 = createVehicle(veh_id, pX, pY, pZ, 0, 0, 0, veh_plate)
				local setVehicle1Color = setVehicleColor(playerVeh1,colR1,colG1,colB1,colR2,colG2,colB2,colR3,colG3,colB3, 0, 0, 0)
				local setVehicle1LightsColor = setVehicleHeadLightColor(playerVeh1, veh_hs1, veh_hs2, veh_hs3)
				local playerToVeh = warpPedIntoVehicle(player, playerVeh1)
			end
		end
	end
end
addEventHandler("spawnVehicleS", getRootElement(), spawnVehicleS)

i know my problem is on the variable playerVeh1 but i don't know how to make a "unique" variable

[Addlibs]

-- at the top of the script, outside any function
local playerVehicles = {}

-- to destroy previous vehicle of this player (before spawning new vehicle, or when player disconnects, etc)
if isElement(playerVehicles[player]) then destroyElement(playerVehicles[player]) end

-- when spawning, store the vehicle as (don't forget to attempt to destroy BEFORE spawning a new one)
playerVehicles[player] = createVehicle(...)

 

Edited May 28, 2017 by MrTasty

[DriFtyZ]

thank you i was looking forward to use lua tables but couldn't find how.. im gonna try this out and let you know

[DriFtyZ]

yep works like a charm thank you!