MySQL

[Lloyd Logan]

I was looking at someones script and saw this;

local result = mysql_query ( database ,"SELECT * FROM `accounts` WHERE `serial` = '"..serial.."'") 
    if result then 
        while true do 
            local row = mysql_fetch_assoc(result) 
            if not row then break end 
            setElementPosition ( source, row.x, row.y, row.z) 

So in order for him to use row.blah blah, he used mysql_query then local row = mysql_fetch_assoc

My problem is, i'm using dbQuery, so how would i create one equal to

  
local row = mysql_fetch_assoc(result) 
            if not row then break end 
            setElementPosition ( source, row.x, row.y, row.z) 

except for dbQuery

Thanks!

[Castillo]

local query = dbQuery ( handler, "SQL STRING HERE" ) 
local data = dbPoll  ( query, - 1 ) -- -1 = wait for the result, freezes server until it has the result. 
if ( type ( data ) == "table" ) then 
    print ( data [ 1 ].myColumnName ) 
end 

[Lloyd Logan]

local query = dbQuery ( handler, "SQL STRING HERE" ) 
local data = dbPoll  ( query, - 1 ) -- -1 = wait for the result, freezes server until it has the result. 
if ( type ( data ) == "table" ) then 
    print ( data [ 1 ].myColumnName ) 
end 

So how would I use this in terms of

setPlayerMoney(something.somthing?)

Thanks also!

[Castillo]

If you are using it without a loop, then you need to include the [ 1 ] before the column name from which you wish to obtain it's value, this "1" represents the index.

[Lloyd Logan]

If you are using it without a loop, then you need to include the [ 1 ] before the column name from which you wish to obtain it's value, this "1" represents the index.

I'm totally stuck, i'm only trying to get some values from mySQL, maybe i'm going about it wrong? I stored it in mySQL, now i'm trying to take a certain value (money ) out.

[TAPL]

Have you already got the MySQL connected?

dbConnect 

If you want to get certain value you need to use SELECT.

http://www.w3schools.com/sql/sql_select.asp

[Lloyd Logan]

Have you already got the MySQL connected?

dbConnect 

If you want to get certain value you need to use SELECT.

http://www.w3schools.com/sql/sql_select.asp

I used SELECT, but how would i select something from a specific row?

[TAPL]

I don't understand, can you post your code?

[Lloyd Logan]

I don't understand, can you post your code?

I'll try again, if not i'll post my code

I have multiple rows in mySQL such as money position etc

But how do I select money from a specific say, account name or serial?

[Lloyd Logan]

I don't understand, can you post your code?

I'll try again, if not i'll post my code

I have multiple rows in mySQL such as money position etc

But how do I select money from a specific say, account name or serial?

[TAPL]

You can use WHERE clause to select specific row.

http://www.w3schools.com/sql/sql_where.asp

[Lloyd Logan]

You can use WHERE clause to select specific row.

http://www.w3schools.com/sql/sql_where.asp

So how would i setPlayerMoney when retrieving this information?

local playermoney = dbQuery ( server, "SELECT 'money' FROM 'accounts' WHERE serial = '"..serial.."' " ) 

[TAPL]

You can use WHERE clause to select specific row.

http://www.w3schools.com/sql/sql_where.asp

So how would i setPlayerMoney when retrieving this information?

local playermoney = dbQuery ( server, "SELECT 'money' FROM 'accounts' WHERE serial = '"..serial.."' " ) 

local query = dbQuery ( handler, "SQL STRING HERE" ) 
local data = dbPoll  ( query, - 1 ) -- -1 = wait for the result, freezes server until it has the result. 
if ( type ( data ) == "table" ) then 
    print ( data [ 1 ].myColumnName ) 
end 

[Lloyd Logan]

You can use WHERE clause to select specific row.

http://www.w3schools.com/sql/sql_where.asp

So how would i setPlayerMoney when retrieving this information?

local playermoney = dbQuery ( server, "SELECT 'money' FROM 'accounts' WHERE serial = '"..serial.."' " ) 

local query = dbQuery ( handler, "SQL STRING HERE" ) 
local data = dbPoll  ( query, - 1 ) -- -1 = wait for the result, freezes server until it has the result. 
if ( type ( data ) == "table" ) then 
    print ( data [ 1 ].myColumnName ) 
end 

I honestly don't understand at all, surely once I retrieved the info from the money index

local playermoney = dbQuery ( server, "SELECT 'money' FROM 'accounts' WHERE serial = '"..serial.."' " ) 

I can simply apply it to setPlayerMoney

[TAPL]

local data = dbPoll(dbQuery(server, "SELECT 'money' FROM 'accounts' WHERE serial = '"..serial.."' "), -1) 
setPlayerMoney(player, data[1]["money"]) 

[Lloyd Logan]

That's my code

    function setMoney() 
    serial = getPlayerSerial(source) 
     local data = dbPoll(dbQuery(server, "SELECT 'money' FROM 'accounts' WHERE serial = '"..serial.."' "), -1) 
        setPlayerMoney(player, data[1]["money"]) 
        if data then 
     local curmoney = getPlayerMoney(source) 
     exports.scoreboard:addScoreboardColumn("Money") 
     setElementData(thePlayer,"Money",curmoney,true) 
    end 
 end 
addEventHandler("onPlayerJoin", root, setMoney) 
     

It gives the error; attempt to index local 'data' "a boolean value"

[TAPL]

exports.scoreboard:addScoreboardColumn("Money") 
  
function setMoney() 
    local Serial = getPlayerSerial(source) 
    local data = dbPoll(dbQuery(server, "SELECT money FROM accounts WHERE serial = ?", Serial), -1) 
    if data and type(data) == "table" then 
        setPlayerMoney(source, data[1]["money"]) 
        setElementData(source, "Money", data[1]["money"]) 
    end 
 end 
addEventHandler("onPlayerJoin", root, setMoney) 

[Lloyd Logan]

Thank you!!

One more question, does the

 data[1]["money"])  

[1] just mean to unfreeze the dbPoll?

[TAPL]

No, When you select the row there might be more than one row so we select the first row in the table which [1].

[Lloyd Logan]

No, When you select the row there might be more than one row so we select the first row in the table which [1].

Oh Thanks, but what happens now is, everytime the player joins, a new row is created instead of checking for previous log ins and updating them! So far is this

checkforserial = dbQuery (server, "ALTER TABLE accounts, ADD CHECK ('"..theserial.."'>0)") 
    if (checkforserial == false) then 
    dbQuery ( server, "INSERT INTO accounts (serial, money) VALUES (?, ?)", tostring (theserial), tostring (themoney) ) 
    else 
  

[TAPL]

exports.scoreboard:addScoreboardColumn("Money") 
  
function setMoney() 
    local Serial = getPlayerSerial(source) 
    local data = dbPoll(dbQuery(server, "SELECT money FROM accounts WHERE serial = ?", Serial), -1) 
    if data and type(data) == "table" and #data > 0 then 
        setPlayerMoney(source, data[1]["money"]) 
        setElementData(source, "Money", data[1]["money"]) 
    else 
        dbExec(server, "INSERT INTO accounts VALUES (?, ?)", Serial, "0") 
        setElementData(source, "Money", 0) 
    end 
end 
addEventHandler("onPlayerJoin", root, setMoney) 

Use UPDATE if you want change existing value.

http://www.w3schools.com/sql/sql_update.asp