Need help manipulating data in SQL!

[yoloman707]

Hiya SQL gurus,

I'm wanting to manipulate the data that i pull out of a database so that it can be added onto the same line of a DListView. In this case to explain more, i want the medal value and reason value to go onto the same line.

However, obviously when i try to put this straight into a line it just displays a table value; when i then remove this from the table, i don't manipulate the data in the way that i wanted to

CODE:

rows = "SELECT COUNT() FROM medal_info"
r1 = sql.Query( rows )
--PrintTable(r1)
r2 = table.remove(r1)
table.insert( r1, r2 )
--PrintTable(r2)
for k, v in pairs(r2) do
	count = v
end
--print(count)
		
if ( count == false ) then
	count = 0
	return count
end
		
for i = 1, count do
			
	local query = "SELECT reason, medal FROM medal_info WHERE steamid = '"..steamid.."'"
	result = sql.Query( query )
	PrintTable(result)
	r1 = table.remove(result, i)
	table.insert(result, r1)
	PrintTable(r1)
	descriptionList:AddLine(r1)
	--print( sql.LastError() )
			
end

CONSOLE PRINTS:

1:
		medal	=	Medal of Honour
		reason	=	Reason for medal...
2:
		medal	=	Silver Lifesaving Medal
		reason	=	Reason for medal...
3:
		medal	=	Medal of Honour
		reason	=	Test 1

medal	=	Medal of Honour
reason	=	Reason for medal...
medal	=	Silver Lifesaving Medal
reason	=	Reason for medal...
medal	=	Medal of Honour
reason	=	Test 1

[Coment]

[QUOTE=yoloman707;52135535]...[/QUOTE]

Part by part.
First you're queuing the server to get a count of all the medals in the table, with no filtering whatsoever.
I don't know if you use it more later down the line, but as it is, you use it later in a for, to look up info about an user's medals...
Yet that medal by steamid query [B]doesn't change a bit[/B]. So if count is bigger than 1 you'll just queue the same information [I]again and again[/I].
So let's trim that down a notch.

		
	local query = "SELECT reason, medal FROM medal_info WHERE steamid = '"..steamid.."'"
	result = sql.Query( query )
	r1 = table.remove(result, i)
	table.insert(result, r1)
	descriptionList:AddLine(r1)

Now, that query will return a table ("result") with each key being a result, and the value a table holding 'reason' and 'medal' as keys. As you've seen in the first PrintTable.

2:
		medal	=	Silver Lifesaving Medal
		reason	=	Reason for medal...

What does this mean? That results[2] holds a table, and you can select results[2].medal and reason for whatever you want.

Of course, you don't know how many results it has without the previous count, but... It doesn't really matter, does it? You can just iterate over all results brought up without a worry in the world.

for _,v in pairs(results) do
print(v.medal)
print(v.reason)
end

That should bring up all medal and reasons and print them. And this is getting closer: you want them to be on the same line, but it won't return that.
So just make it do it.

for _,v in pairs(results) do
print(v.medal.. " - "..v.reason)
end

Everything inside that last print is a single line with both medal and reason. Wowza. Now, where should you stick it if you want to AddLine to your list...
You can probably figure out the rest. As a hint, it only takes me 5 lines, and I don't ever use a table.[...] function.

[yoloman707]

[QUOTE=Coment;52135660]...[/QUOTE]

You're an absolute legend! i got it working thank you so much for this.

I just have one question, just for my own curiosity and learning of the programming language. What allows you to use "v.medal" and "v.reason"? can it only be used in tables or can be it be used everywhere?

An explanation of this would be much appreciated if possible thank you :)

[tzahush]

[QUOTE=yoloman707;52137130]You're an absolute legend! i got it working thank you so much for this.

I just have one question, just for my own curiosity and learning of the programming language. What allows you to use "v.medal" and "v.reason"? can it only be used in tables or can be it be used everywhere?

An explanation of this would be much appreciated if possible thank you :)[/QUOTE]

v.medal and v.reason is just an object gathered from a table your currently defining as 'v', usually with a for loop.

If you have a lot of little questions and don't wanna flood the forums, feel free to add me on steam and i'll try and help you out

[bigdogmat]

[QUOTE=yoloman707;52137130]You're an absolute legend! i got it working thank you so much for this.

I just have one question, just for my own curiosity and learning of the programming language. What allows you to use "v.medal" and "v.reason"? can it only be used in tables or can be it be used everywhere?

An explanation of this would be much appreciated if possible thank you :)[/QUOTE]

`result` is a table containing all of the rows from the query, looping over it using pairs gives you the index and value. So `v` in this case would be the value at each index, and because each value is a table you can use `v.reason`.

`v.reason` is the same `v["reason"]`, just a different way to index tables.

[yoloman707]

Thanks for all the help guys! it is much appreciated :D