[Help] Mysql Column insert

[Syntrax#]

Hello community,

i've a question regarding mysql,is it possible to have a several names within one single column such as OrganisationRanks : Leader,Vice Leader,Headquarter,Prospect.

I know how to do it normally but with mysql i can't get it to work.

[Captain Cody]

May you explain better what you are trying to accomplish?

[pa3ck]

You could either store it as JSON or create a primary key - foreign key relation between tables. Eg. 

Teams:

ID: 1, Name: MyTeam

ID: 2, Name: YourTeam

TeamRanks:

TeamID: 1, Name: Rank 1

TeamID: 1, Name: Rank 2

TeamID: 2, Name: Rank 1

TeamID: 1, Name: Rank 3

TeamID: 2, Name: Rank 2

 

Then something like this to get the list of ranks for a specific team: 

SELECT * FROM TeamRanks WHERE TeamID LIKE getPlayerTeamId(player)

 

[Syntrax#]

@CodyL im working on a gang panel with custom ranks.But in order to generate those ranks with permissions i need to have several ranks within one column.So basically if you run the gangname by select and you try to get Ranks it should output something like this.Leader,Headquarter,Prospect. This is the column value of one single row

@pa3ck thank you thats what ive been  searching for didnt know toJSON would handle this and just use the fromJSON to get the actual data within a table.Ive managed to fix this already thanks to this.

[Syntrax#]

New problem appeared, Somehow i can't get the data from the mysql server.I've a table in return but somehow i can't get it to splitup the JSON

 

function getConnection()
connection = dbConnect( "mysql", "dbname=fghousing;host=localhost", "root", "", "share=1" )
if ( connection ) then -- The connection failed
outputDebugString("Connected to the MySQL server")
json = toJSON ( {"Headquarter", "Leader", "Prospect"}, true, "none" )
dbExec(connection, "INSERT INTO newtest VALUES (?,?)", json, "Syntrax#")
outputDebugString("Inserted Rows")
else
outputDebugString("Unable to connect to the MySQL server")
end
end
addEventHandler("onResourceStart",resourceRoot,getConnection)

function getJSON()
connection = dbConnect( "mysql", "dbname=fghousing;host=localhost", "root", "", "share=1" )
if ( connection ) then -- The connection failed
local ranksString = ""
local result = dbQuery(connection, "SELECT * FROM newtest WHERE username=?", "Syntrax#") -- Execute the query
if result then
resultdata = dbPoll(result, -1)
if resultdata then
for i, v in ipairs(resultdata) do
value = unpack(v)
outputDebugString(fromJSON(value))
end
end
end
end
end
addCommandHandler("getJSON", getJSON)

 

[IIYAMA]

How does the JSON looks like?

How do you split up the JSON? (Note: if you use string keys in JSON, all numeric keys also become strings, which is very annoying)

How does the data looks like in the mysql database?

Tip:

else
	outputDebugString("Unable to connect to the MySQL server")
	cancelEvent() -- don't start a resource that doesn't have connection to a mySQL server! Else you get an enormous flow of errors/warnings if the connection fails.
end

 

Normally an unique id is the first in the column and the rest of the data comes after that.

 

Spoiler

https://www.google.nl/url?sa=i&rct=j&q=&esrc=s&source=images&cd=&cad=rja&uact=8&ved=0ahUKEwjy3a_kos7RAhVJBBoKHeJ8CnoQjRwIBw&url=http%3A%2F%2Fwww.zanda.com%2Fitems%2Fthings%2F55032250000028%2Fsams-teach-yourself-sql-in-10-minutes-4th-edition-by-ben-forta-book&psig=AFQjCNEiZb6uns3dvf1piUexppSUg7jLgA&ust=1484917466176234

 

Edited January 19, 2017 by IIYAMA

[pa3ck]

You don't need to split up the JSON. Look at this example to see how it works

local jsonString = toJSON ( {"Headquarter", "Leader", "Prospect"}, true, "none" )
local jsonTable = fromJSON(jsonString) -- a LUA table from JSON string
outputDebugString(jsonTable[1]) --> "Headquarter"

 

[Syntrax#]

18 hours ago, pa3ck said:

You don't need to split up the JSON. Look at this example to see how it works

local jsonString = toJSON ( {"Headquarter", "Leader", "Prospect"}, true, "none" )
local jsonTable = fromJSON(jsonString) -- a LUA table from JSON string
outputDebugString(jsonTable[1]) --> "Headquarter"

 

This works when having those above eachother.Now i have to find a method to get it back from MYSQL.The data has been stored as 

[["Headquarter","Leader","Prospect"]]

 

But once i've done the query and Poll the results i've encountered a problem.It doesn't quite do anything.Tried 

For i, v in ipairs(resultdata) do
  outputDebugString(v) -- Outputs Table : random:~
  outputDebugString(fromJSON(v)) -- Outputs nil
  end

I've also tried

For i, v in ipairs(resultdata) do
  stringG = v
  for i, v in ipairs(stringG) do
    outputDebugstring(v[1])
    outputDebugstring(fromJSON(v)[1])
    end
  end

 

[IIYAMA]

outputDebugString("Debug info start: " .. getTickCount())
for i, v in ipairs(resultdata) do
	inspect ( i )
	inspect ( v )
	-- https://wiki.multitheftauto.com/wiki/Inspect
end

?

    outputDebugString("Debug info start: " .. getTickCount())
    for i, v in ipairs(resultdata) do
      outputDebugString("type(resultdata) =  " .. type(resultdata))
      if type(v) == "table" then
        for j, data in pairs(v) do -- pairs << very important
          inspect ( j )
          inspect ( data )
          -- https://wiki.multitheftauto.com/wiki/Inspect
        end
      else
        outputDebugString("V ~= table, but: " .. type(v))
      end
    end

I actually forgot how to do it too, haha. Well I do remember that there has to be also a pairs loop in it, because it uses columns as keys.

Edited January 20, 2017 by IIYAMA

[pa3ck]

This is working fine for me:

function addTeam()

	local ranks = toJSON ( {"Headquarter", "Leader", "Prospect"}, true, "none" )
	dbExec(connection, "INSERT INTO teams (team_name, team_ranks) VALUES ( ?, ? )", "TestName", ranks )

end

function getRank()

	local qry = dbQuery(connection, "SELECT * FROM teams WHERE team_id LIKE 1 LIMIT 1")
	local res = dbPoll(qry, -1)
	local teamRank
	
	if(res and #res >0) then
	
		for k, rows in ipairs(res) do
		
			teamRank = fromJSON(rows["team_ranks"]) -- "team_ranks" is the column name in my table
		
		end
	
	end
	
	for i = 1, #teamRank do
		outputChatBox("Rank " .. i .. ": " .. teamRank[i])
	end

end

Edited January 20, 2017 by pa3ck

[Syntrax#]

On 20-1-2017 at 11:50, pa3ck said:

This is working fine for me:

function addTeam()

	local ranks = toJSON ( {"Headquarter", "Leader", "Prospect"}, true, "none" )
	dbExec(connection, "INSERT INTO teams (team_name, team_ranks) VALUES ( ?, ? )", "TestName", ranks )

end

function getRank()

	local qry = dbQuery(connection, "SELECT * FROM teams WHERE team_id LIKE 1 LIMIT 1")
	local res = dbPoll(qry, -1)
	local teamRank
	
	if(res and #res >0) then
	
		for k, rows in ipairs(res) do
		
			teamRank = fromJSON(rows["team_ranks"]) -- "team_ranks" is the column name in my table
		
		end
	
	end
	
	for i = 1, #teamRank do
		outputChatBox("Rank " .. i .. ": " .. teamRank[i])
	end

end

Hmm thanks but using another method now which is more efficient

[IIYAMA]

Which method is that?

[Syntrax#]

2 hours ago, IIYAMA said:

Which method is that?

Rows within mysql member ranks tables.Easier to look at and edit if needed.I do rather use this due the fact i'm more used to this.

[IIYAMA]

Yea indeed, that is how it suppose to be haha .

Except when it is a value that is variable, that would be annoying to edit later on.