mysql not returning a value

[TheNightRider]

Hi guys am working on a function for a server which uses mysql, Am constructing a script to manage the database.

I came across an issue when am going to query the database for users who have admin level of >=6 its meant to display the GUI to the user and if they don't its meant to trigger a client function informing the user. However it isn't seem to be working. There are no errors nor bad arguments in the debugger and am stumped.

  
  
mysql = exports.mysql 
function showmenu()--check user has rights or not. 
  
     
    local result = mysql:query("SELECT admin FROM 'accounts' WHERE username")  
    if (mysql:num_rows(result)>=6) then 
    triggerClientEvent(source,"ShowMenuC",true)--show user interface. 
    outputChatBox('I did something YAY XD',playerSource);-- test if working or not 
        else 
  
  
    triggerClientEvent(source,"notify",true)--tell user. 
    outputChatBox('Sorry No Can Do!!!',playerSource);-- test if working or not 
    end 
end 
addCommandHandler("dbman",showmenu) 
  

Thanks

[Renkon]

You missed the comparative in the query.

WHERE username == "something"

[TheNightRider]

You missed the comparative in the query.

WHERE username == "something"

okay what if i taken out WHERE username?

I find it still does the same problem but when I run the sql itself in a mysql database I get a result

[Renkon]

local name = getPlayerName(some player element)

and then..

local result = mysql:query("SELECT admin FROM 'accounts' WHERE username == '"..name.."'")

[TheNightRider]

local name = getPlayerName(some player element)

and then..

local result = mysql:query("SELECT admin FROM 'accounts' WHERE username == '"..name.."'")

thanks but name doesn't return nothing and am not sure what to put where you wrote some player element

[Renkon]

then suppose we want to use the command /db CompleteNameofAdmin

mysql = exports.mysql 
function showmenu(source, cmd, person)--check user has rights or not. 
  
   if not person then return end 
    local result = mysql:query("SELECT admin FROM 'accounts' WHERE username == '"..person.."'") 
    if (mysql:num_rows(result)>=6) then 
    triggerClientEvent(source,"ShowMenuC",true)--show user interface. 
    outputChatBox('I did something YAY XD',playerSource);-- test if working or not 
        else 
  
  
    triggerClientEvent(source,"notify",true)--tell user. 
    outputChatBox('Sorry No Can Do!!!',playerSource);-- test if working or not 
    end 
end 
addCommandHandler("dbman",showmenu) 

If JohnPepper is level 6

/dbman JohnPepper will show you it.

If you write /dbman John it will show you there's no admin.

You must add FULL NAME since it's not getPlayerFromPartName but from Name.

[Castillo]

What i understood is that you have a column in your table which is where the level is defined.

mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local result = mysql:query_fetch_assoc ( "SELECT admin FROM `accounts` WHERE `username`='".. getPlayerName ( playerSource ) .."'" ) 
    if ( result and tonumber ( result [ "admin" ] ) >= 6 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 

[Renkon]

Castillo's reply may be even better than mine. Ignore what I said, he is right.

[TheNightRider]

What i understood is that you have a column in your table which is where the level is defined.

mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local result = mysql:query ( "SELECT admin FROM `accounts` WHERE `username`='".. getPlayerName ( playerSource ) .."'" ) 
    if ( result and mysql:fetch_assoc ( result ) [ "admin" ] >= 6 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 

I get an error saying line 6 attempt to return a nil value its regarding the line after the query.

[TheNightRider]

I have tried so many ways to try and solve the issue but unsuccessful I know it is communicating with the database but isn't seem to be doing has expected. Could someone please explain why its wrong?

  
  
mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local result = mysql:query ( "SELECT admin FROM `accounts` WHERE `username`='".. getPlayerName ( playerSource ) .."'" ) 
    if ( result and mysql:fetch_assoc ( result ) [ "result.admin" ] >= 6 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 
  
  

[Renkon]

I am used to dbExec. I will quote you an example though.

--- DB VARIABLES --- 
host = "localhost" -- host goes here 
username = "root" -- db username 
password = "pa$$word" -- db password 
dbname = "iLoveTrains" -- db name 
-------------------- 
db = dbConnect( "mysql", "dbname="..dbname..";host="..host, username, password) 
  
function showmenu ( playerSource ) 
     local pName = getPlayerName ( playerSource ) 
     local consulta = dbQuery(db,"SELECT admin FROM `accounts` WHERE nick = '"..pName.."'") 
     local Result = dbPoll ( consulta, -1 ) 
     if (Result[1] == nil) then return end -- Shit happened :3 
     if (Result[1].admin >= 6) -- epic WIN 
          triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
          outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
     else 
          triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
          outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
     end 
end 
  
addCommandHandler ( "dbman", showmenu ) 

[TheNightRider]

I am used to dbExec. I will quote you an example though.

--- DB VARIABLES --- 
host = "localhost" -- host goes here 
username = "root" -- db username 
password = "pa$$word" -- db password 
dbname = "iLoveTrains" -- db name 
-------------------- 
db = dbConnect( "mysql", "dbname="..dbname..";host="..host, username, password) 
  
function showmenu ( playerSource ) 
     local pName = getPlayerName ( playerSource ) 
     local consulta = dbQuery(db,"SELECT admin FROM `accounts` WHERE nick = '"..pName.."'") 
     local Result = dbPoll ( consulta, -1 ) 
     if (Result[1] == nil) then return end -- :~ happened :3 
     if (Result[1].admin >= 6) -- epic WIN 
          triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
          outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
     else 
          triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
          outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
     end 
end 
  
addCommandHandler ( "dbman", showmenu ) 

Thanks mate but am using a mysql script to do the connection etc.

[Renkon]

Well, you can maybe try this

[TheNightRider]

Well, you can maybe try this

It does not do nothing but when I added a test to show on chat what result is getting it seems to be counting in HEX LOL.

I see its just returning a nil causing it to end but however it shouldn't be right?

  
  
  
db = dbConnect( "mysql", "dbname="..dbname..";host="..host, username, password,port) 
function showmenu ( playerSource ) 
     local pName = getPlayerName ( playerSource ) 
     local consulta = dbQuery(db,"SELECT admin FROM `accounts` WHERE username = '"..pName.."'") 
     local Result = dbPoll ( consulta, -1 ) 
     outputChatBox ( tostring ( Result ) ) 
     if (Result[1] == nil) then return end -- :~ happened :3 
     if (Result[1].admin >= 6) then -- epic WIN 
          triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
          outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
     else 
          triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
          outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
     end 
end 
  

[Renkon]

Line 7: replace with

local consulta = dbQuery(db,"SELECT admin FROM `accounts` WHERE username == '"..pName.."'")

[TheNightRider]

Line 7: replace with

local consulta = dbQuery(db,"SELECT admin FROM `accounts` WHERE username == '"..pName.."'")

Yeah what about it and what is the point in having more than 1 script which has connection details?

PS that duplicates the Primary Key, do not need to do connection because the query itself is querying the database. 1 Way we tried we got an error saying unknown Column Username etc meaning it was saying the Username it got from the table was a Column so it was in away getting confused but like I said it shows the script is querying the database

[Renkon]

Then use this instead

mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local pName = getPlayerName ( playerSource ) 
    local result = mysql:query ( "SELECT admin FROM `accounts` WHERE `username`=='".. pName .."'" ) 
    if ( result and mysql:fetch_assoc ( result ) [ "result.admin" ] >= 6 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 

[TheNightRider]

Then use this instead

mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local pName = getPlayerName ( playerSource ) 
    local result = mysql:query ( "SELECT admin FROM `accounts` WHERE `username`=='".. pName .."'" ) 
    if ( result and mysql:fetch_assoc ( result ) [ "result.admin" ] >= 6 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 

having username`=='".. pName .."'" returns an syntax error due to the == if its only a = i says attempt to index a nil value regarding the following line

[Renkon]

Then use this instead

mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local pName = getPlayerName ( playerSource ) 
    local result = mysql:query ( "SELECT admin FROM `accounts` WHERE `username`=='".. pName .."'" ) 
    if ( result and mysql:fetch_assoc ( result ) [ "result.admin" ] >= 6 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 

having username`=='".. pName .."'" returns an syntax error due to the == if its only a = i says attempt to index a nil value regarding the following line

Yea yeah, I dont know why I thought it should have been double ==. Yet, it should work, it's extremely weird. The one I posted BEFORE worked.

[TheNightRider]

Then use this instead

mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local pName = getPlayerName ( playerSource ) 
    local result = mysql:query ( "SELECT admin FROM `accounts` WHERE `username`=='".. pName .."'" ) 
    if ( result and mysql:fetch_assoc ( result ) [ "result.admin" ] >= 6 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 

having username`=='".. pName .."'" returns an syntax error due to the == if its only a = i says attempt to index a nil value regarding the following line

Yea yeah, I dont know why I thought it should have been double ==. Yet, it should work, it's extremely weird. The one I posted BEFORE worked.

it does not work

[Castillo]

Copy my code again, it should work now.

[TheNightRider]

Its working thanks to SoildSnake but there is something wrong with the client triggers keeps telling me not added clientside but they are.

  
  
mysql = exports.mysql 
function showmenu ( playerSource )--check user has rights or not. 
    local result = mysql:query_fetch_assoc ( "SELECT admin FROM `accounts` WHERE `username`='".. getPlayerName ( playerSource ) .."'" ) 
    if ( result and tonumber ( result [ "admin" ] ) >= 1 ) then -- Check if the level is 6 or higher. 
        triggerClientEvent ( playerSource, "ShowMenuC", playerSource, true )--show user interface. 
        outputChatBox ( 'I did something YAY XD', playerSource );-- test if working or not 
    else 
        triggerClientEvent ( playerSource, "notify", playerSource, true )--tell user. 
        outputChatBox ( 'Sorry No Can Do!!!', playerSource );-- test if working or not 
    end 
end 
addCommandHandler ( "dbman", showmenu ) 
  
  

EDIT: Sorry forget to post the clientside function

  
  
addEvent("tellusr",true) 
function notify(playerSource)--inform low level user. 
  
  
outputChatBox('You do not have permission to access this terminal',playerSource);-- notify user. 
end 
addEventHandler ("telluser",getRootElement(),notify) 
  
  

Edited September 26, 2012 by Guest

[Renkon]

When you executed the command, were files downloaded?

[Castillo]

Post the client side part, TheNightRider.

[TheNightRider]

Post the client side part, TheNightRider.

I did do mate