Jump to content

[TUT] SQLite


JR10

Recommended Posts

Hey guys.. I have made a database to save some accounts and then those accounts are listed inside a table.. And at end, that table is listed in a guiGridList..Everything is perfect till here but i wanna know following:

1. How can i outputChatBox to all the accounts listed there?

2. How can i check if one account is in that database/table or no?

Link to comment

If you're MySQL, then I will recommend that onResourceStart, you get all the accounts and store them in a table. That will allow easier and less consuming access.

I don't really get your first question but as for the second, dbQuery(callback, db, 'SELECT * FROM accounts WHERE name = ? LIMIT 1', name) should do. name is the name of the account.

Link to comment

Depends on what identifies them in the table. Since we're talking about accounts, how do you identify a player's account? By knowing that, you can simply retrieve the account from the table, then loop through players trying to find the player with that account. If you're using the default MTA account system, then getAccountPlayer will get you the player with that account. If it's a totally custom accounts system, then only you would know how to get an account's player.

Something like:

dbQuery(callback, database, 'SELECT * FROM accounts') 
  
function callback(qh) 
 local result = dbPoll(qh, 0) 
 for index, row in pairs(result) do 
  local account_name = row.name 
  -- now find the player with that account name 
 end 
end 

Link to comment

This should work if the row for the account name is `name`:

dbQuery(callback, database, 'SELECT * FROM accounts') 
  
function callback(qh) 
 local result = dbPoll(qh, 0) 
 for index, row in pairs(result) do 
  local account_name = row.name 
  local player = getAccountPlayer(getAccount(account_name)) 
  if (isElement(player)) then 
   outputChatBox('Hi', player) 
  end 
 end 
end 

Link to comment
  • 7 months later...
  • 4 weeks later...
  • 5 months later...

hey i have a question can you see whats wrong with this i get this warning and it dont update the points into table after reconnect

lp2Gynr.png

script

dataName  = { 
    ["anterior"] = "Last Drift", 
    ["total"] = "Total Drift", 
    ["mejor"] = "Best Drift" 
} 
  
addEventHandler ( "onResourceStart", root, 
    function ( ) 
        -- creating connection and if true creates column if not exist 
        connection = dbConnect ( "sqlite" , "Driftpoint.db" ) 
        if ( connection ) then 
            dbQuery ( connection , "CREATE TABLE IF NOT EXISTS `drift` ( `data` TEXT, `account` TEXT )" ); 
            outputDebugString ( "connected" ); 
        else 
            outputDebugString ( "not connected" ); 
        end 
        -- creating connection and if true creates column if not exist 
    end 
); 
  
function saveDriftPoints ( ) 
    local account = getPlayerAccount ( source ); -- getting account 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
    
    local accName = getAccountName ( account ); -- getting name 
    
    local t = { } -- creating table for drift's data 
    for k, v in pairs ( dataName ) do 
        local data = getElementData ( source, v ); 
        if ( data ) then 
            t[v] = data; -- inserting values to table from ElementData 
        end 
    end 
    
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if exist row 
    if ( #check > 0 ) then 
        dbQuery ( connection, "UPDATE `drift` SET `data`=? WHERE `account`=?", toJSON ( t ), accName ); -- if yes updating 
    elseif ( #check == 0 ) then 
        dbQuery ( connection, "INSERT INTO `drift` ( `data`, `account` ) VALUES ( ?, ? )", toJSON ( t ), accName ); -- if not creating 
    end 
end 
addEventHandler ( "onPlayerQuit", root, saveDriftPoints ); 
  
function loadDriftPoints ( _, account ) 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
    
    local accName = getAccountName ( account ); -- getting name 
    
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if row exist 
    if ( #check > 0 ) then -- if yes 
        local d = check[1]; 
        
        local t = fromJSON ( d["data"] ); 
        if ( type ( t ) == "table" ) then 
            for k, v in pairs ( t ) do 
                setElementData ( source, k, v ); -- setting ElementData from saved drift's table in table 
            end 
        end 
    end 
end 
addEventHandler ( "onPlayerLogin", root, loadDriftPoints ); 

this are the score when i quit

DaTD1ac.jpg?1

this are the loaded scores when i join the game and login

DaTD1ac.jpg?1

this are the score when i join and did 1 drift

zgfUFjz.jpg?1

Link to comment

Whenever you INSERT or CREATE something, you should just use dbExec instead of dbQuery. dbQuery returns a result everytime, and that's why you should use dbFree on your query. However, I suggest you use dbExec instead of dbQuery, you only need to know if it in theory went through. You would get errors if it didn't work, anyway (depends how complex you want to build this thing).

Link to comment
Whenever you INSERT or CREATE something, you should just use dbExec instead of dbQuery. dbQuery returns a result everytime, and that's why you should use dbFree on your query. However, I suggest you use dbExec instead of dbQuery, you only need to know if it in theory went through. You would get errors if it didn't work, anyway (depends how complex you want to build this thing).

okay thnx for telling me was asking for couple days now :P i gonna try it out and see if it will work better :D

Link to comment
  • 1 year later...

dbExec(db, "CREATE TABLE IF NOT EXISTS `accountdata`(`id` INT NOT NULL AUTO_INCREMENT, `name` TEXT, PRIMARY KEY(id))") 
dbExec(db, "CREATE TABLE IF NOT EXISTS `inventory`(`id` INT NOT NULL AUTO_INCREMENT, `name` TEXT, PRIMARY KEY(id))") 

something wrong on this can u help.?

Link to comment
dbExec(db, "CREATE TABLE IF NOT EXISTS `accountdata`(`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT)") 
dbExec(db, "CREATE TABLE IF NOT EXISTS `inventory`(`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT)") 

Link to comment
  • 5 weeks later...
  • 10 months later...

I never understood what is the purpose of dbExec. When you execute it, how will you know if it worked if it doesn't return anything? I guess one way would be through a script error. But I always use dbQuery because I want that query to return something, so that way I know it worked. Not sure what would be the purpose of using executeSQLQuery either.

On 4/27/2015 at 11:29, myonlake said:

Whenever you INSERT or CREATE something, you should just use dbExec instead of dbQuery. dbQuery returns a result everytime, and that's why you should use dbFree on your query. However, I suggest you use dbExec instead of dbQuery, you only need to know if it in theory went through. You would get errors if it didn't work, anyway (depends how complex you want to build this thing).


Oh, I didn't know that. Thx for that.

Edited by Backsage
Link to comment
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...