SQL Amounts

[Gtagasje]

Hello there,

I had a question about my script. Basicly, I want to execute a query to count the amount of vehicles a person has. Then I want to send that amount to the client side to create a row for each vehicle. I derped around with tables and what not, but I couldn't find a way myself, so that's why I am asking here.

This is what I use to create the table:

  
function createTables() 
    executeSQLQuery("CREATE TABLE IF NOT EXISTS vehicles(vehicle,vehicleID,vehiclePrice,locked,x,y,z,hidden,account)") 
end 
addEventHandler("onResourceStart", getResourceRootElement(getThisResource()), createTables) 
  

I use this function on the server side:

  
vehTable = { } 
  
function openVehWindow(thePlayer, cmd) 
    local acc = getAccountName(getPlayerAccount(thePlayer)) 
    local vehRes = executeSQLQuery("SELECT COUNT(vehicle) FROM vehicles WHERE account=?", acc) 
    local priRes = executeSQLQuery("SELECT COUNT(vehiclePrice) FROM vehicles WHERE account=?", acc) 
    if type(vehRes) == "table" and #vehRes > 0 and type(priRes) == "table" and #priRes > 0 then 
        for i,v in pairs(vehRes) do 
            vehicles = vehRes[1]["vehicle"] 
            table.insert(vehTable, vehicle) 
        end 
        for i,v in pairs(priRes) do 
            price = vehRes[1]["vehiclePrice"] 
            table.insert(vehTable, price) 
        end 
    triggerClientEvent("openVehWindow", getRootElement(), vehicles, price) 
    else 
        outputChatBox("You don't have any vehicles!", thePlayer, 255, 0, 0) 
    end 
end 
addCommandHandler("open", openVehWindow) 
  

And the client side:

  
vehTable = { } 
  
function showVehWindow(vehicles, price) 
    vehicleWindow() 
    if (vehWdw ~= nil) then 
        guiSetVisible(vehWdw, true) 
        guiSetInputEnabled(true) 
        showCursor(true) 
        centerWindow(vehWdw) 
        for i=1,#vehicles do 
            row = guiGridListAddRow(veh_vehGrid) 
            guiGridListSetItemText(veh_vehGrid, row, 1, vehicles[i], false, false) 
            guiGridListSetItemText(veh_vehGrid, row, 2, price[i], false, false) 
        end 
    end 
end 
addEvent("openVehWindow", true) 
addEventHandler("openVehWindow", getRootElement(), showVehWindow) 
  

I get this error at the moment:

  
ERROR: c.lua:132: attempt to get lenght of local 'vehicles' (a nil value) 
  

Please note that the code is quite messy at the moment, since I've been trying around for quite some hours. My apologies for that.

[tosfera]

server sided;

triggerClientEvent("openVehWindow", getRootElement(), vehicles, price) 

clientside;

function showVehWindow(vehicles, price) 

Using the # only counts arguments etc, it can not be used to count the rows in tables. Also, getRootElement() can be anything in a trigger, change it to thePlayer. That should fix a few of the problems I guess.

[Gtagasje]

I hope I understood you right, I changed the following things:

Client:

  
for i=1,#vehicles do 
  

to

  
for i,vehTable in ipairs(vehTable) do 
  

Server:

  
triggerClientEvent("openVehWindow", getRootElement(), vehicles, price) 
  

to

  
triggerClientEvent("openVehWindow", thePlayer, vehicles, price) 
  

This does not add a row for each vehicle even though I have one, but it doesn't give an error either.

[tosfera]

Oke I've rewritten it abit, try this. NOTE THAT, I CHANGED YOUR TABLE LAYOUT! You didn't had the data types in it either.

client

local vehPrices = { { 571, 250000 } }; 
  
function showVehWindow( vehicles ) 
    vehicleWindow() 
    if ( vehWdw ~= nil ) then 
        guiSetVisible ( vehWdw, true ); 
        guiSetInputEnabled ( true ); 
        showCursor ( true ); 
        centerWindow( vehWdw ); 
        for i, v in ipairs ( vehicles ) do 
            row = guiGridListAddRow(veh_vehGrid) 
            guiGridListSetItemText(veh_vehGrid, row, 1, v[i]["vehicleName"], false, false) 
            guiGridListSetItemText(veh_vehGrid, row, 2, v[i]["vehiclePrice"], false, false) 
        end 
    end 
end 
addEvent("openVehWindow", true) 
addEventHandler("openVehWindow", getRootElement(), showVehWindow) 

server;

function createTables() 
    executeSQLQuery ( "CREATE TABLE IF NOT EXISTS vehicles ( vehicleID INT, vehicleModel INT, vehicleName TEXT, vehiclePrice INT, locked INT, x INT, y INT, z INT, hidden INT, account TEXT ) " ); 
end 
addEventHandler("onResourceStart", getResourceRootElement(getThisResource()), createTables) 
  
  
vehTable = { } 
  
function openVehWindow(thePlayer, cmd) 
    local acc = getAccountName ( getPlayerAccount ( thePlayer ) ); 
    local vehRes = executeSQLQuery("SELECT COUNT(*) FROM `vehicles` WHERE `account`=?", acc); 
    if not ( #vehRes == 0 ) then 
        for i,v in pairs ( vehRes ) do 
            table.insert ( vehTable, v["vehicleId"] ); 
        end 
        triggerClientEvent ( "openVehWindow", thePlayer, vehTable ); 
    else 
        outputChatBox ( "You don't have any vehicles!", thePlayer, 255, 0, 0 ); 
    end 
end 
addCommandHandler ( "open", openVehWindow ) 

[Gtagasje]

Okay, thank you very much, but it doesn't work for some reason. I don't get any errors either. And about the data types, the tutorial I read said that it wasn't neccessary, so that's why I didn't have them in the table. Sorry for that. But I've copy-pasted your code and it still doesn't work. No debug errors either.

[tosfera]

Cause you have no vehicles anymore, haha. The table has been remade if I'm correct. If not, delete the table and then run the script again. else you'll have to add some outputChatBox debug lines around your code and see where it goes wrong.

[Gtagasje]

I had the vehicle, and recreated the table actually. It just didn't show up, but I'll add some outputChatbox around the script and see what works and what doesn't.

[Citizen]

Don't use SQL functions that you don't know what it does.

The COUNT() function returns the number of rows that matches a specified criteria.

So it will only return a number, not a table with the datas we want to get ...

Try this:

Server

-- vehTable = { } Delete this ! 
  
function openVehWindow(thePlayer, cmd) 
    local acc = getAccountName(getPlayerAccount(thePlayer)) 
    local vehRes = executeSQLQuery("SELECT vehicle, vehiclePrice FROM vehicles WHERE account=?", acc) 
    if type(vehRes) == "table" and #vehRes > 0 then 
        triggerClientEvent("openVehWindow", getRootElement(), vehRes) 
    else 
        outputChatBox("You don't have any vehicles!", thePlayer, 255, 0, 0) 
    end 
end 
addCommandHandler("open", openVehWindow) 

Client

-- vehTable = { } Delete this ! 
  
function showVehWindow(vehicles) 
    vehicleWindow() 
    if (vehWdw ~= nil) then 
        guiSetVisible(vehWdw, true) 
        guiSetInputEnabled(true) 
        showCursor(true) 
        centerWindow(vehWdw) 
        guiGridListClear(veh_vehGrid) -- empty the grid 
        for i, tableRow in ipairs(vehicles) do -- tableRow will contain 1 row that has 2 columns (vehicle and vehiclePrice) 
            local row = guiGridListAddRow(veh_vehGrid) 
            guiGridListSetItemText(veh_vehGrid, row, 1, tableRow[1], false, false) 
            guiGridListSetItemText(veh_vehGrid, row, 2, tableRow[2], false, false) 
        end 
    end 
end 

If you were using the table vehTable just tell me how (on both side) because you don't need it imo.

Best regards,

Citizen

[Gtagasje]

Alright, when I put outputChatBox in the server side it works, and in the client side too, but it doesn't work in the clientside when I put it after "for", so it's something with the table if I'm right.

[Citizen]

Did you try with my code ?

If yes then I need the error that is in the clientscript.log:

Mine is here:

C:\Program Files (x86)\MTA San Andreas 1.3\MTA

if this log file is too big to be opened, then just delete it and retry to do the open command.

If you are admin in the ACL, then you also can use this command:

debugscript 3

and you will get a little message box at the bottom of your script that will show you the errors/warning/infos from the server and client side.

[Gtagasje]

No Citizen, that was not with your code. I tried your code, and it somehow works. Thanks for that.

However, first time I tried with your code it gave me this error:

  
[2013-10-27 17:13:50] WARNING: Vehicle\c.lua:133: Bad argument @ 'guiGridListSetItemText' [Expected string at argument 4, got nil] 
[2013-10-27 17:13:50] WARNING: Vehicle\c.lua:134: Bad argument @ 'guiGridListSetItemText' [Expected string at argument 4, got nil] 
  

So then I edited this:

  
guiGridListSetItemText(veh_vehGrid, row, 1, tableRow[1], false, false) 
guiGridListSetItemText(veh_vehGrid, row, 2, tableRow[2], false, false) 
  

to this:

  
guiGridListSetItemText(veh_vehGrid, row, 1, tostring(tableRow[1]), false, false) 
guiGridListSetItemText(veh_vehGrid, row, 2, tostring(tableRow[2]), false, false) 
  

and now it shows 'nil' on both columns, but it does add a row for each vehicle I own, so that works. Thank you for that. But do you have any idea how to fix this string problem?

[Castillo]

guiGridListSetItemText ( veh_vehGrid, row, 1, tostring ( tableRow.vehicle ), false, false ) 
guiGridListSetItemText ( veh_vehGrid, row, 2, tostring ( tableRow.vehiclePrice ), false, false ) 

Instead of 1 and 2 as index, it should of been: vehicle and vehiclePrice.

[Gtagasje]

Well, I guess you helped me once again. It works perfect. Thanks Casti! (And Citizen!)

[Citizen]

Damn it ! Was so close ! I thought ipairs could be used with table that references values with keys and that I'll be able to use index instead. But actually, it just should be used with tables that uses index instead of keys.