Returning values from database sequentially

Is there a way to use a loop of some sort to return values from an SQL Database? In PHP you use a while loop to return values when using a wildcard in SELECT statements. How is this done in LUA?

Most (if not all) MySQL modules return a table as a result to a SELECT query. Just loop through that table.

Do you by chance have an example for this? Im not the greatest with working with tables. Do I use a for loop and reference like k.(tablename)?

It depends what module you’re using.

Simple example for SQLite

Let’s say we have a table called people with columns name, money and gender.

With values “John”, 500, “male”, and “Mary”, 200, “female”.

[lua]
– all data
local result = sql.Query( “SELECT * FROM people;” )
for rownumber, data in pairs( result ) do
print( v.name )
print( v.money )
print( v.gender )
end
[/lua]

Output would be:
“John”
500
“male”
“Mary”
200
“female”

[lua]
– specific row
local result = sql.Query( “SELECT * FROM people WHERE gender = ‘female’;” )
for rownumber, data in pairs( result ) do
print( v.name )
print( v.money )
print( v.gender )
end
[/lua]

Output would be
“Mary”
200
“female”

So SQLite (and usually most MySQL modules) return data as a table, where the index is the row number in the database’s table, and the value stored at that index is another table, with indexes of the column names and values of the data in each column.

Thank you! Worked perfect!

Actually that didn’t seem to work. Here is my code. Says that results isn’t a table. Its a boolean.


local results = sql.Query("SELECT * FROM gzrp_tickets;")
for k,v in pairs(results) do
	TicketList:AddLine(v.unique_id, v.victim_steam, v.officer_steam, v.victim_name, v.officer_name, v.amount, v.description)
end

I also tried


local results = sql.QueryValue("SELECT * FROM gzrp_tickets;")

that didn’t work either.

Do PrintTable(results) and post results here.

He already said that the return is a bool, so using PrintTable won’t help at all.

**[Sql.Query

http://wiki.garrysmod.com/favicon.ico](http://wiki.garrysmod.com/?title=Sql.Query)** returns an false when an error occurs, use **[Sql.LastError

http://wiki.garrysmod.com/favicon.ico](http://wiki.garrysmod.com/?title=Sql.LastError)** to retrieve it.

Thats very helpful to know. Apparently you cant use AUTO INCREMENT in sql so that table was never created… Im fixing some code and ill get back to you on the results. In the mean time, how do you get the servers current timestamp?

Well it says that the table doesn’t exist. I put a print(sql.LastError()) after it created all the tables to see if it threw an error, it didn’t. So I don’t know why its not making the table. All the other tables were created just fine. Here is the code for creating the table.


	sql.Query("CREATE TABLE IF NOT EXISTS gzrp_tickets('victim_steam' TEXT NOT NULL, 'officer_steam' TEXT NOT NULL, 'victim_name' TEXT NOT NULL, 'officer_name' TEXT NOT NULL, 'amount' TEXT NOT NULL, 'description' TEXT NOT NULL, PRIMARY KEY('victim_steam'));")

After digging more into this, the table has been created. Are you not able to access these tables client side or something? If I execute code server side it will find the table just fine and do what is needed. However if I run SQL queries client side it complains the table cannot be found.

There are two separate databases, one for the server and one for the client. You would need to network this table of data to each client who needs to see the information.

I was looking at umsg is there a way to send a table using this? Or is there a better method?

You would probably send a umsg.String string to client with the query and havw it perform the query on client.

Do some research on the net library which is not yet released to gmod but will be after the beta.

It allows up to 64kb to be sent and it also allows tables to be sent using net.WriteTable