Saving a character system to MySQL

[kPkPT]

Hey guys,

So i am doing a roleplay gamemode from scratch. However, i like to think about what i'm going to do and how i am going to do it before i even start. Obviously it would need a character system, however i don't see how i will link several characters to one account? How can i actually do it with MySQL? I currently got my login and register system done and i got a database with a users table set.

 

Best regards,

Vasco

[Master_MTA]

40 minutes ago, kPkPT said:

Hey guys,

So i am doing a roleplay gamemode from scratch. However, i like to think about what i'm going to do and how i am going to do it before i even start. Obviously it would need a character system, however i don't see how i will link several characters to one account? How can i actually do it with MySQL? I currently got my login and register system done and i got a database with a users table set.

 

Best regards,

Vasco

create table for players skins

executeSQLQuery("CREATE TABLE IF NOT EXISTS playersskins (plracc TEXT, skin TEXT, addons TEXT)")
---or
dbExec(db connection,'CREATE TABLE IF NOT EXISTS players (plracc TEXT, skin TEXT, addons TEXT)')
--------------
---then when you want to get it do that
dbPoll(db connection,'SELECT * FROM playersskins WHERE plracc=?',the player acc name)
-----and dont forget to use (split)
---and alos if you want to insert the addons do something like that
dbExec(db connection,'INSERT INTO playersskins VALUES(?,?,?)',plr acc string,skin id number,tostring(addition..','..addition1..','....etc))

 

[kPkPT]

Just now, Master_MTA said:

create table for players skins

executeSQLQuery("CREATE TABLE IF NOT EXISTS playersskins (plracc TEXT, skin TEXT, addons TEXT)")
---or
dbExec(db connection,'CREATE TABLE IF NOT EXISTS players (plracc TEXT, skin TEXT, addons TEXT)')
--------------
---then when you want to get it do that
dbPoll(db connection,'SELECT * FROM playersskins WHERE plracc=?',the player acc name)
-----and dont forget to use (split)
---and alos if you want to insert the addons do something like that
dbExec(db connection,'INSERT INTO playersskins VALUES(?,?,?)',plr acc string,skin id number,tostring(addition..','..addition1..','....etc))

 

It is not just the skins, i want several stuff stored. For each character i need about 20 variables or more.

[Master_MTA]

7 minutes ago, kPkPT said:

It is not just the skins, i want several stuff stored. For each character i need about 20 variables or more.

so you can do  it like i was say or you can use loop? and tables 

[kPkPT]

18 minutes ago, Master_MTA said:

 

9 minutes ago, Master_MTA said:

so you can do  it like i was say or you can use loop? and tables 

Well yea but, on a roleplay server i will need alot of stuff saved like vehicles and the global user data wich is used on all the players characters. I thought about making a table and adding all characters there with a column named "username" or "serial" and cross the player username and serial with the ones there. However how can i read a table top to bottom and get all the characters belonging to that player?

[Master_MTA]

1 minute ago, kPkPT said:

 

something like that

for k=1,#table.(serial or acc or any thing you want) do
  if getPlayerSerial(player that you want get he is skins)==table.serial[k] then
  setElementModel(player,table.skins[k])---or something like that  
  end
  end

 

[kPkPT]

8 minutes ago, Master_MTA said:

something like that

for k=1,#table.(serial or acc or any thing you want) do
  if getPlayerSerial(player that you want get he is skins)==table.serial[k] then
  setElementModel(player,table.skins[k])---or something like that  
  end
  end

 

#table being the dbpoll or dbquery right?

[Master_MTA]

1 minute ago, kPkPT said:

#table being the dbpoll or dbquery right?

yup any thing else?

[kPkPT]

Just now, Master_MTA said:

yup any thing else?

No i guess that is all. Really thanks for your help, apreciate it

[Master_MTA]

1 minute ago, kPkPT said:

No i guess that is all. Really thanks for your help, apreciate it

any time dude we are all try to help each others 

[Manticore]

Please help me on this topic 

 

[pa3ck]

2 tables, account and character.

Use primary key in account table that will reference the account a character is created from. Look at the term "1 to many relationship" to better understand.

Account table should look something like this: accountID, username, password

Character: characterID, accountID, health, skin, whatever

Vehicles: vehicleID, characterID, model etc.

That's the best way, this way your data is normalized.

[kPkPT]

22 minutes ago, pa3ck said:

2 tables, account and character.

Use primary key in account table that will reference the account a character is created from. Look at the term "1 to many relationship" to better understand.

Account table should look something like this: accountID, username, password

Character: characterID, accountID, health, skin, whatever

Vehicles: vehicleID, characterID, model etc.

That's the best way, this way your data is normalized.

So i see it now, i watched a video about it. However, how can i get all the characters saved after that in the code? Or any other tables data that i might need.

Also, is this the best method for not wasting so much CPU resources?

Edited August 28, 2017 by kPkPT

[pa3ck]

Data normalization is all about one thing, no duplicate data allowed. If you don't have duplicates and the tables are indexed properly, you're not querying useless information thus saving time and resources.

"SELECT * FROM characters WHERE account_id = 2" -> returns all characters created by account number 2.

You can also use join if you want to get data from both tables:

"SELECT * FROM characters INNER JOIN accounts ON characters.accountID = accounts.accountID WHERE accounts.accountID = 2"

[Pirulax]

Very good explanation by @pa3ck here, congrats!Dropped you a like, i was about to explain the same, but then i saw ur comments