MySQL Help - List all things from DB

[FlyingSpoon]

Okay I've got 4 columns, ID, Name, Vehicle and Description.

I want to display all of that from my database using mysql_query, how would I do that?

CLIENT SIDE ------

function showStats()
        mainWn = guiCreateWindow(0.30, 0.15, 0.42, 0.69, "Las Barrancas Garage - View Orders", true)
        guiWindowSetMovable(mainWn, false)
        guiWindowSetSizable(mainWn, false)

        gridList = guiCreateGridList(0.02, 0.05, 0.96, 0.83, true, mainWn)
        guiGridListAddColumn(gridList, "ID", 0.2)
        guiGridListAddColumn(gridList, "Name", 0.2)
        guiGridListAddColumn(gridList, "Vehicle", 0.2)
        guiGridListAddColumn(gridList, "Description", 0.2)
        btnClose = guiCreateButton(0.77, 0.90, 0.21, 0.08, "Close", true, mainWn)    
end

I want to display it onto my gridlist, I'm not sure how to do it, my friend helped me last time but I've forgotten.

 

My MySQL table is called 'stats' - 

columns = id (auto_increment/primary) , name, vehicle, desc

Edited March 10, 2017 by raysmta

[Ayush Rathore]

Client

function showStats()
        mainWn = guiCreateWindow(0.30, 0.15, 0.42, 0.69, "Las Barrancas Garage - View Orders", true)
        guiWindowSetMovable(mainWn, false)
        guiWindowSetSizable(mainWn, false)

        gridList = guiCreateGridList(0.02, 0.05, 0.96, 0.83, true, mainWn)
        guiGridListAddColumn(gridList, "ID", 0.2)
        guiGridListAddColumn(gridList, "Name", 0.2)
        guiGridListAddColumn(gridList, "Vehicle", 0.2)
        guiGridListAddColumn(gridList, "Description", 0.2)
        btnClose = guiCreateButton(0.77, 0.90, 0.21, 0.08, "Close", true, mainWn)   
        triggerServerEvent("getListData",getLocalPlayer()) 
end


function setData(tab)
for i=1,#tab do
local row = guiGridListAddRow ( gridList  )
guiGridListSetItemText ( gridList, row, 1, tab[i]['id'] , false, false )
guiGridListSetItemText ( gridList, row, 2, tab[i]['name'] , false, false )
guiGridListSetItemText ( gridList, row, 3, tab[i]['vehicle'] , false, false )
guiGridListSetItemText ( gridList, row, 4, tab[i]['desc'] , false, false )
end
end
addEvent("setListData",true)
addEventHandler("setListData",root,setData)

 

Server

function getData()
local result = {}
local query = dbQuery("select * from stats")
result = dbPoll(query,-1)
dbFree(query)
if #result > 0 then
triggerClientEvent(client,"setListData",client,result)
else
triggerClientEvent(client,"setListData",client,{})
end
end
addEvent("getListData",true)
addEventHandler("getListData",root,getData)

This might fulfill your needs

 

[FlyingSpoon]

Amazing, thank you so much. One last question, if I make a button e.g.-

delBtn = guiCreateButton( x, y ,z, "Delete", mainWin)

How would I make it so, if the player clicks on one of the grid list item, and press delete, and it removes it from the DB/Gridlist at the same time?

 

[klaw]

I thought you were a pro scripter, rays? Giving hate to all the roleplay communities that launch? Claiming people cannot script or are :~ at scripting? Ironic isn't it?

guiCreateButton on your example is completely wrong. A button does not have a Z coordinate because it's a 2 dimensional element. 

Proper: guiCreateButton(x, y, width, height, "Delete", relative, parent)

When the player clicks the button, it triggers onClientGUIClick. Once clicked, figure out which row is selected on your grid list using guiGridListGetSelected. To delete it from the grid list you can use guiGridListRemoveRow and to delete it from the DB you need to run a delete statement, e.g. "DELETE FROM stats ... etc"

Remember don't use the MySQL module as it's now deprecated, use the native MySQL functions such as dbQuery and dbConnect.

[FlyingSpoon]

Sorry "Master Scripter" no one is perfect like you. I'm still learning SQL scripting. Plus, I haven't scripted for a very long time, so it's a learning process. Also, giving hate to roleplay communities? I'm sure everyone is entitled to their own opinion. As well as that, this section is for helping not for giving your :~ty hate. It was a question I asked, simple. 

Update: I've figured it out how to delete rows, it's fine. 

Edited March 10, 2017 by raysmta

[klaw]

Calm down and keep your salt levels at minimum.

I'm stating what I've witnessed and I find it extremely ironic how you hate on communities and claim they're :~, constantly crying, undermining them and then you come asking for help from the same people.

75% of my post was helping you, the other 25% was pointing out how pathetic your actions were and what they've led to.

Grow up kid and accept the truth.

[FlyingSpoon]

Like I've mentioned before this section is for helping, keep your pathetic, false opinions to yourself and use it somewhere else. And note, I don't hate all communities, I'm enjoying VC-RP's current progress and also enjoy Owl Gaming. And asking help on the MTA Forums is available for anyone. I'm not asking anyone to make my scripts. So I think you should grow, and stop going around placing false facts, hiding behind other names.

[HomerSimpson]

2 hours ago, raysmta said:

Amazing, thank you so much. One last question, if I make a button e.g.-

delBtn = guiCreateButton( x, y ,z, "Delete", mainWin)

How would I make it so, if the player clicks on one of the grid list item, and press delete, and it removes it from the DB/Gridlist at the same time?

 

Change the query string.

DELETE FROM stats WHERE some_column=some_value;

[FlyingSpoon]

Thanks.

 

[HomerSimpson]

2 minutes ago, raysmta said:

Thanks.

 

Np, just remember though like the other guy says, use native functions. Using the module is complete ass. it doesn't take you two seconds to write an MYSQL resource
 

function connection()

return dbConnect(*MAKE YOUR OWN VERIBLES*)

end

Added: function has to be exported to work.

Edited March 10, 2017 by Unitts

[FlyingSpoon]

Thanks, I'm using dbConnect now, it's much simpler, thank you so much.

[HomerSimpson]

4 minutes ago, raysmta said:

Thanks, I'm using dbConnect now, it's much simpler, thank you so much.

Good good, glad I could help.

[Ayush Rathore]

you are welcome rays mta

[FlyingSpoon]

 Also I had a question about this, 

function setData(tab)
for i=1,#tab do

What does i=1 do and #tab ?

[Ayush Rathore]

well as you can see tab is the result set of query so it starts from tab[1] and so on #tab is the count of result set meaning result set contains x no of student

for i=0,#tab do

end

will iterate all the values you use

 

 

Last thing if you can't understand me please let me know

[FlyingSpoon]

So what would happen if we did i=0,#tab do

[Ayush Rathore]

1 minute ago, raysmta said:

So what would happen if we did i=0,#tab do

it will give error as result set starts from 1

[FlyingSpoon]

Okay here is another example -

local result = dbQuery(database, "SELECT * FROM players")
	for _, ad in ipairs( result ) do

:bad argument #1 to 'ipairs' (table expected, got userdata)

 

What does this mean?

Edited March 12, 2017 by raysmta

[Ayush Rathore]

use this 

local query = dbQuery(database, "SELECT * FROM players")
local result = dbPoll(query,-1)	
for _, ad in ipairs( result ) do
    
end

u see dbQuery is only prepare statement and to actually finding result set you should use dbPoll

P.S quote me (so that i can get notification)

[FlyingSpoon]

So dbPoll gets all the data?

So if I wanted to get all player names first I'd run the query -

local query = dbQuery(database, "SELECT * FROM accounts)

local result = dbPoll(query,-1)

outputChatBox(result, source)

 

That would display all the data from accounts?

[Ayush Rathore]

no this will do you need

 

local query = dbQuery(database, "SELECT * FROM players")
local result = dbPoll(query,-1)	
for x, ad in ipairs( result ) do
outputChatBox(result[x]['dataname']) 
end

 

7 minutes ago, raysmta said:

So dbPoll gets all the data?

So if I wanted to get all player names first I'd run the query -

local query = dbQuery(database, "SELECT * FROM accounts)

local result = dbPoll(query,-1)

outputChatBox(result, source)

 

That would display all the data from accounts?

 

local query = dbQuery(database, "SELECT * FROM players")
local result = dbPoll(query,-1)	
for x, ad in ipairs( result ) do
for i, v in ipairs( {'kills','deaths','money'} ) do  
outputChatBox(result[x][v]) 
end
end

this is for multiple

20 minutes ago, raysmta said:

So dbPoll gets all the data?

So if I wanted to get all player names first I'd run the query -

local query = dbQuery(database, "SELECT * FROM accounts)

local result = dbPoll(query,-1)

outputChatBox(result, source)

 

That would display all the data from accounts?

is my code working

?

 

[FlyingSpoon]

Yes, it's working