[tomato3017]

mrflippy posted:

Just for documentation in the thread, here are the values for the client_flag parameter for the connect function (At least I think so) :) :

#define CLIENT_LONG_PASSWORD    1       /* new more secure passwords */
#define CLIENT_FOUND_ROWS       2       /* Found instead of affected rows */
#define CLIENT_LONG_FLAG        4       /* Get all column flags */
#define CLIENT_CONNECT_WITH_DB  8       /* One can specify db on connect */
#define CLIENT_NO_SCHEMA        16      /* Don't allow database.table.column */
#define CLIENT_COMPRESS         32      /* Can use compression protocol */
#define CLIENT_ODBC             64      /* Odbc client */
#define CLIENT_LOCAL_FILES      128     /* Can use LOAD DATA LOCAL */
#define CLIENT_IGNORE_SPACE     256     /* Ignore spaces before '(' */
#define CLIENT_PROTOCOL_41      512     /* New 4.1 protocol */
#define CLIENT_INTERACTIVE      1024    /* This is an interactive client */
#define CLIENT_SSL              2048    /* Switch to SSL after handshake */
#define CLIENT_IGNORE_SIGPIPE   4096    /* IGNORE sigpipes */
#define CLIENT_TRANSACTIONS     8192    /* Client knows about transactions */
#define CLIENT_RESERVED         16384   /* Old flag for 4.1 protocol  */
#define CLIENT_SECURE_CONNECTION 32768  /* New 4.1 authentication */
#define CLIENT_MULTI_STATEMENTS 65536   /* Enable/disable multi-stmt support */
#define CLIENT_MULTI_RESULTS    131072  /* Enable/disable multi-results */
#define CLIENT_REMEMBER_OPTIONS (((ulong) 1) << 31)

I had to use CLIENT_MULTI_RESULTS to return a result set back from a stored procedure. I have no idea whether any of the rest of the values will work with the module or not.

Wow, very useful flippy! I have been looking for something like this.

[mrflippy]

tomato3017 posted:

Wow, very useful flippy! I have been looking for something like this.

It's actually for our project :)

[andyvincent]

I've got a threaded version that you guys can test - Download (Source)

Example usage:

print("------------------------------------------------------------------\n");
print("Threaded test!")
thread, isok, error = mysql.query(db, "SELECT * FROM names", mysql.QUERY_NOBLOCK);
if (thread) then
	print("thread=" .. thread .. " -> started\n")
	
	function printResults(threadid)
		if (mysql.query_complete(threadid)) then
			print("thread=" .. threadid .. " -> finished!")
			test, isok, error = mysql.query_read(threadid)
			if (test) then
				PrintTable(test)
			end
			if (!isok) then
				print( tostring(error) );
			end
			mysql.query_cleanup(threadid)
		else
			print("thread=" .. threadid .. " -> still not finished")
			timer.Simple(1, printResults, threadid)
		end
	end
	
	timer.Simple(0, printResults, thread)
end

[mrflippy]

Nice :) I will check that out tonight after work.

[buildaholic]

local db, error = mysql.connect("*******", "*******", "******", "********");
if (db == 0) then print(tostring(error) .. "\n") return end
 
print("connection opened - " .. db .. "!\n");

steam, isok, error = mysql.query(db, "SELECT steam FROM users");

local succ, error = mysql.disconnect(db)
if (not succ) then
    print( error );
end
print("connection closed!\n"); 

if ([ply: steamID()]  == steam  ) then

print("Worked!\n");

end

Can i have some help with this

[mrflippy]

buildaholic posted:

if ([ply: steamID()]  == steam  ) then

I'm not quite sure what you're doing here. I'm not familiar with the bracket notation, and there shouldn't be a space between the colon and steamID(). (and it's actually SteamID() with a capital 'S')

If you want to check for whether a player's steam id exists in the database, you probably want a sql query like

local query = "select count(*) from player where steam = '"..ply:SteamID().."'"

This should return the number of records in the player table that have a steam id matching the player's.

Or, if you want to check every player in the game, you could use the query that you have there now, but you would also need to loop through each of the players and loop through each of the steam IDs returned. (The sql queries should return a table as far as I know.)

[buildaholic]

To be honest i'm not that great with lua. I can do the mysql/php part easily all i need is:

it to read my mysql database and get table users then find out is the player that is currently on the server has his steamid in the database.

i can do

if ([ply:SteamID()]  == steam  ) then

//DO MY STUFF HERE


end

just above not so sure abote this, here is so far

local db, error = mysql.connect("crossstar.co.uk", "ben_bbservers", "****", "ben_BBservers");
if (db == 0) then print(tostring(error) .. "\n") return end
 
print("connection opened - " .. db .. "!\n");

local steam = "select count(*) from player where steam = '"..ply:SteamID().."'"

local succ, error = mysql.disconnect(db)
if (not succ) then
    print( error );
end
print("connection closed!\n"); 

if ([ply:SteamID()]  == steam  ) then
//do my stuff here if there id is in the database

end

[UberMensch]

MySQL queries return a 2d table as far as I know, so you should do:

tostring(steam[1]["rowname"])

When you compare something with another string.

[Guardian9978]

Sorry to raise this old thread but I'm trying to get this to work.

I was playing around with this and was wondering I would go about having it work with player spawn...

For logging purposes.

I tried to integrate this into the sandbox game mode which some of you may find not very smart but I didn't know how to do it any other way... since it has to be on player spawn and whenever I try to spawn the console throws this error:

attempted to index global 'ply' (a nil value)

and the lua code i sued for that line its arguing about is this:

local query = "select count(*) from player where steam = '"..ply:SteamID().."'"

anyone know how to do any of this?

Also how would I log time that they spawned?

[The Scherf]

ply is nil... Simple as that, you didn't define it.
You probably named the argument pl or something like that.

[UberMensch]

Guardian9978 posted:

Sorry to raise this old thread but I'm trying to get this to work.

I was playing around with this and was wondering I would go about having it work with player spawn...

For logging purposes.

I tried to integrate this into the sandbox game mode which some of you may find not very smart but I didn't know how to do it any other way... since it has to be on player spawn and whenever I try to spawn the console throws this error:

attempted to index global 'ply' (a nil value)

and the lua code i sued for that line its arguing about is this:

local query = "select count(*) from player where steam = '"..ply:SteamID().."'"

anyone know how to do any of this?

Also how would I log time that they spawned?

Make another gamemode and derive sandbox into it.

Also, take a look at this for time things.

[The Scherf]

UberMensch posted:

Make another gamemode and derive sandbox into it.

It's got nothing to do with that, he just need to define ply.

[Guardian9978]

EDIT: Newest problem is in my last post.

[Guardian9978]

Sorry fro the double post.

EDIT: newest problem is in my last post.

[Statement]

You need to specify a Database.

[Guardian9978]

Look in the post above my last 1 I just removed the data for my SQL servers protection but one is specified.

Since when I tested the insert it worked just fine.

Just the Select line isn't working.

[maurits150]

Guardian9978 posted:

OK I Partially have it working this is what I have for the player initial spawn:

/*--------------------------------------------*/
print("\n======================================================================\n");
print("======================================================================\n");
print("======================================================================\n");
  local db, error = mysql.connect("*****", "*****", "*****", "*****")
  if (db == 0) then print(tostring(error) .. "\n") return end

  local query22 = mysql.query("select count(*) from server1 where ID2 = '"..ply:SteamID().."'");

    if(query22 == 0) then
      steamid = ply:SteamID()
      name = ply:Nick()
//      joined1 = os.date() 
      print("\nAttempting to insert into SQL\n\n");
//      result, isok, error = mysql.query(db, "INSERT INTO server1 (ID2,Name,J1) VALUES('" .. steamid .. "','" .. name .. "','" .. joined1 .. "');");
      result, isok, error = mysql.query(db, "INSERT INTO server1 (ID2,Name,J1) VALUES('" .. steamid .. "','" .. name .. "','0');");
    
        if (!isok) then
                print("Failed to add player.\n\n");
        end
      print("Added Player:\n\nJoined: " .. os.date() .. "\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n");
    end

  local succ2, error = mysql.disconnect(db)
  if (not succ2) then Msg( error .. "\n" ) end
print("Done!!!");
print("======================================================================\n");
print("======================================================================\n");
print("======================================================================\n");
/*--------------------------------------------*/

and this is the message I am getting from it:

SERVER: Insufficient parameters!

There's nothing wrong with the MySQL script but you forgot to write the os.date format flags

fix this

print("Added Player:\n\nJoined: " .. os.date() .. "\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n"); end

to this

print("Added Player:\n\nJoined: " .. os.date(%I:%M %p) .. "\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n"); end
--
--

Edit:

Damn why do the lua tags always give errors :(

[Guardian9978]

Ok I figured out my mistake this line:

local query22 = mysql.query("select count(*) from server1 where ID2 = '"..ply:SteamID().."'");

should be:

local query22 = mysql.query(db, "select count(*) from server1 where ID2 = '"..ply:SteamID().."';");

Since I fixed that I tried to print out the result of query22 and all i get from it is:

table: 02638920

(The number changes depending on what I change in the table data.)
(Doesn't matter whats in the column even if its empty it shows the same thing.)



-------------------------------------------------------------------------------------------------------------------------------------------



now about the time part...

when i have this:

result, isok, error3 = mysql.query(db, "INSERT INTO server1 (steam_id,name,j1) VALUES('" .. steamid .. "','" .. name .. "','" .. os.date(%D) .. "');");

it says:

Unexpected symbol near '%'

[tomato3017]

I have figured out if you make threaded queries too fast that the database will go away. I found this out by simulating a heavy traffic to see if it would handle the load.

Not to mention this crashes the server sometimes. This is a fresh install of the server I use to test my addons on.

Here's my test code

require( "mysql" )
local xdb, isok, error = mysql.connect("192.168.0.10", "****", "*****", "testdatabase")
local threadtable = {}

function querytest1()
	local thread, isok, error = mysql.query(xdb, "SELECT 1 + 1 FROM servers", mysql.QUERY_NOBLOCK);
	if(isok) then
		table.insert(threadtable,thread)
		print("Thread:" .. thread .. " made")
	else
		mysql.query_cleanup(thread)
	
	end
	timer.Simple(1,querytest1)

end


function querytest2()
	local thread, isok, error = mysql.query(xdb, "SELECT 1 + 1 FROM servers", mysql.QUERY_NOBLOCK);
	if(isok) then
		table.insert(threadtable,thread)
		print("Thread:" .. thread .. " made")
	else
		mysql.query_cleanup(thread)
	
	end
	timer.Simple(1,querytest2)

end

function querytest3()
	local thread, isok, error = mysql.query(xdb, "SELECT 1 + 1 FROM servers", mysql.QUERY_NOBLOCK);
	if(isok) then
		table.insert(threadtable,thread)
		print("Thread:" .. thread .. " made")
	else
		mysql.query_cleanup(thread)
	
	end
	timer.Simple(1,querytest3)

end

function querycallback(query)
    local count = 0
    local atable = {}
    atable = table.Copy(threadtable)

	for i,v in pairs(atable) do
	count = count + 1
		if(mysql.query_complete(v)) then
			local result, isok, error = mysql.query_read(v)
			mysql.query_cleanup(v)
			
			if !isok then
				print("THREAD FAILED")
				print(error)
			else
				print("THREAD #" .. v .. " FINISHED")
				print(result)
			end
			table.remove(threadtable,i)
		else
			print("THREAD NOT DONE")
		end

	end
	PrintTable(threadtable)
	timer.Simple(5, querycallback)

end

timer.Simple(1,querytest1)
timer.Simple(1,querytest2)
timer.Simple(1,querytest3)
timer.Simple(1,querytest1)
timer.Simple(1,querytest2)
timer.Simple(5, querycallback)

[Guardian9978]

EDIT: irrelevant to anything...

[tomato3017]

Guardian9978 posted:

How can my script be accessing the sql database too fast?

I'm in my test server and all my websites and anything else using my sql is disabled.

I'm the only 1 connecting to it.. and i try to connect when the server finishes loading and its told to print query22...

All I get is table: #########

but the query insert into table works just fine with out a problem...

I wasn't talking to you. Sorry if I made you confused.

[Guardian9978]

Oh sorry I'm just very desperate to fix my problem...

[tomato3017]

Guardian9978 posted:

Ok I figured out my mistake this line:

local query22 = mysql.query("select count(*) from server1 where ID2 = '"..ply:SteamID().."'");

should be:

local query22 = mysql.query(db, "select count(*) from server1 where ID2 = '"..ply:SteamID().."';");

Since I fixed that I tried to print out the result of query22 and all i get from it is:

table: 02638920

That's the table your COUNT is in. Try print(query22[1])
That should work.

[Guardian9978]

Thank you Ill try that.

EDIT:
I tried that method and it gave me the same Table: ######## message.

This is what I have currently:

/*--------------------------------------------*/
print("\n======================================================================\n");
print("======================================================================\n");
print("======================================================================\n");
local db, error = mysql.connect("server", "username", "password", "database")
if (db == 0) then print(tostring(error) .. "\n") return end

query22 = mysql.query(db, "SELECT count(*) FROM iota WHERE ID2 = '" .. ply:SteamID() .. "';");

print("\nQuery22: ");
print(query22[1])
print("\n\n");

if(query22[1] == 0) then
  steamid = ply:SteamID()
  name = ply:Nick()
  print("\nAttempting to insert into SQL\n\n");

  query33 = mysql.query(db, "INSERT INTO iota (ID2,name,j1) VALUES('" .. steamid .. "','" .. name .. "','0');");
    
  if (!query33) then
     print("Failed to add player.\n\n");
  end
  print("Added Player:\n\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n");
end

local succ2, error = mysql.disconnect(db)
if (not succ2) then Msg( error .. "\n" ) end
print("Done!!!");
print("======================================================================\n");
print("======================================================================\n");
print("======================================================================\n");
/*--------------------------------------------*/

[tomato3017]

Guardian9978 posted:

-snip-

It's returning a 2d table then, use [1][1] instead of just [1] then. Basically its returning this: {{result}}

Also, os.date("%D") is the correct way per the definition on the garrys mod wiki which I suggest you read. http://www.garrysmod.com/wiki/?title=Lua

[Guardian9978]

That worked like a charm thanks...

Now to figure out why my if statement isn't working...

if(query22[1][1] == 0) then

[tomato3017]

Guardian9978 posted:

That worked like a charm thanks...

Now to figure out why my if statement isn't working...

if(query22[1][1] == 0) then

Try and see if this works.

if(tonumber(query22[1][1]) == 0) then

[Guardian9978]

Never mind it worked I just made the edit to the wrong file since I was trying to test it on my game so I didn't have to keep closing and opening my dedicated server.

Thank you.

EDIT: This is what I got and it works thanks to tomato3017.

This script saves the following information:
SteamID
Nickname(ingame name)
Date they FIRST spawned in the server
Date they Last joined the the server. (Updates every initial spawn or first spawn doesn't update if they're respawning from a death.)

This script also sets everyone as disconnected after the dedicated starts up so if the server crashes when you start it up again everyone is marked as offline. (also works with listen servers.)

Only lines you will need to modify are the following:
Lines 5 and 14 - Connection info
Lines 7,17,24, and 23 - change the table name iota to whatever the table name you want to use.

function MySQLload()
       Msg("////////////////////////////////////////////////////\n")
       Msg("///               MySQL Script Loaded            ///\n")
       Msg("////////////////////////////////////////////////////\n")
       local db, error = mysql.connect("SERVER", "USERNAME", "PASSWORD", "DATABASE")
       if (db == 0) then print(tostring(error) .. "\n") return end
       mysqlstarta = mysql.query(db, "UPDATE iota SET ONLINE = 0;")
       local succ2, error = mysql.disconnect(db)
       if (not succ2) then Msg( error .. "\n" ) end
       Msg("///        All players marked as offline.        ///\n")
       Msg("////////////////////////////////////////////////////\n")
end
function MySQLSpawn(ply)
  local db, error = mysql.connect("SERVER", "USERNAME", "PASSWORD", "DATABASE")
  if (db == 0) then print(tostring(error) .. "\n") return end

       query22 = mysql.query(db, "SELECT count(*) FROM iota WHERE ID2 = '" .. ply:SteamID() .. "';");

if(tonumber(query22[1][1]) == 0) then
  steamid = ply:SteamID()
  name = ply:Nick()
  joined1 = os.date("%m/%d/%y")
  print("\nAttempting to insert into SQL\n\n");
  query33 = mysql.query(db, "INSERT INTO iota (ID2,name,j1,lastlogin1) VALUES('" .. steamid .. "','" .. name .. "','" .. joined1 .. "','" .. joined1 .. "');");
  print("Added Player:\n\nDate: " .. joined1 .. "\nNick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n");
end

if(tonumber(query22[1][1]) == 1) then
  steamid = ply:SteamID()
  name = ply:Nick()
  joined1 = os.date("%m/%d/%y")
  print("Updating " .. name .. "'s Profile.");
  query33 = mysql.query(db, "UPDATE iota SET lastlogin1 = '" .. joined1 .. "' WHERE ID2 = '" .. ply:SteamID() .. "';");
end

  local succ2, error = mysql.disconnect(db)
  if (not succ2) then Msg( error .. "\n" ) end
end

  hook.Add( "PlayerInitialSpawn", "MySQLSpawn", MySQLSpawn )
  hook.Add( "InitPostEntity", "MySQLload", MySQLload )

[Sware]

	steamid = pl:SteamID()
	test, isok, error = mysql.query(db, "INSERT INTO names (SteamId,Name) VALUES('" .. steamid .. "','it worked')");

I tried putting this in, and when ran it out puts in .. how do I run lua inside the mysql queries?

[Guardian9978]

What exactly does it put out?

Because from your example with the steamid that's the correct way.

[Sware]

It puts out ..

On a listen server aka Multiplayer

[maurits150]

Well. I'm having another problem. I can't connect my SQL databse because it's a webserver. I can't enter an IP because i don't know it.

Here's the code.

host = "sql.fluitmedia.nl"  --the MySQL server. I don't have a IP.
username = "****"
password = "****"
database = "mauritsdebruin_**********"
port = 3306    --standart port

function MySQLConnect()
	local mysql_db, error = mysql.connect( host, username, password, database , port)
	if (mysql_db == 0) then 
		print(tostring(error) .. "\n") 
		return 
	end
end

But now I get.

Unknow host "sql.fluitmedia.nl"

The server isn't invalid. Because I'm hosting forums on it.

Help would be appreciated!

[Ronin_Yakusa]

Some one tell me what this error means and how to fix it.

connection opened - 1!

1:
1 = 2
Incorrect integer value: 'fuck me!' for column 'SteamId' at row 1

connection closed!

[Guardian9978]

@Ronin_Yakusa

Ronin_Yakusa posted:

Some one tell me what this error means and how to fix it.

connection opened - 1!

1:
1 = 2
Incorrect integer value: 'fuck me!' for column 'SteamId' at row 1

connection closed!

'Fuck Me!' is not a number its giving you that error because the column is set for Interger which is a number SteamID isn't an interger... try setting the column SteamID to VARCHAR or TEXT.

Edit:

@maurits150

maurits150 posted:

Well. I'm having another problem. I can't connect my SQL databse because it's a webserver. I can't enter an IP because i don't know it.

Here's the code.

host = "sql.fluitmedia.nl"  --the MySQL server. I don't have a IP.
username = "****"
password = "****"
database = "mauritsdebruin_**********"
port = 3306    --standart port

function MySQLConnect()
	local mysql_db, error = mysql.connect( host, username, password, database , port)
	if (mysql_db == 0) then 
		print(tostring(error) .. "\n") 
		return 
	end
end

But now I get.

Unknow host "sql.fluitmedia.nl"

The server isn't invalid. Because I'm hosting forums on it.

Help would be appreciated!

To find an ip of a website or server do the following:
1. Click 'Start'
2. Click 'Run'
3. Type in CMD
4. Type in the black box that poped up:

ping sql.fluitmedia.nl

5. It should say something like this: (the example below is what would happen if you were to ping google.)

Pinging google.com [64.233.167.99] with 32 bytes of data:

Reply from 64.233.167.99: bytes=32 time=62ms TTL=242
Reply from 64.233.167.99: bytes=32 time=63ms TTL=242
Reply from 64.233.167.99: bytes=32 time=61ms TTL=242
Reply from 64.233.167.99: bytes=32 time=63ms TTL=242

Ping statistics for 64.233.167.99:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 61ms, Maximum = 63ms, Average = 62ms

Reply from #.#.#.# is the ip of your server.

[pcwizdan]

ronin, before sending info to the Server use
local IDSteam = string.gsub(player:SteamID(), ":", "", "steamID" )

That should work, am i correct? or must you split up SteamID into 7 letters?

then do the same when it is requesting from the Sql server...

[UberMensch]

pcwizdan posted:

ronin, before sending info to the Server use
local IDSteam = string.gsub(player:SteamID(), ":", "", "steamID" )

That should work, am i correct? or must you split up SteamID into 7 letters?

then do the same when it is requesting from the Sql server...

You shouldn't have to do anything with SteamIDs. It's things like quotes and semi-colons you need to use mysql.escape() for.

http://forums.theshadys.co.uk/PlayerList.php

The SteamIDs listed there aren't escaped, but names and jobs are due to the open nature.

[redser]

Oh my god, how the hell me install?!?

[nicktelli]

maurits150 posted:

Well. I'm having another problem. I can't connect my SQL databse because it's a webserver. I can't enter an IP because i don't know it.

Here's the code.

host = "sql.fluitmedia.nl"  --the MySQL server. I don't have a IP.
username = "****"
password = "****"
database = "mauritsdebruin_**********"
port = 3306    --standart port

function MySQLConnect()
	local mysql_db, error = mysql.connect( host, username, password, database , port)
	if (mysql_db == 0) then 
		print(tostring(error) .. "\n") 
		return 
	end
end

But now I get.

Unknow host "sql.fluitmedia.nl"

The server isn't invalid. Because I'm hosting forums on it.

Help would be appreciated!

Me thinks unknown host means sql.fluitmedia.nl doesn't have MySQL running on the default port of 3306

[Incompatible]

How can I check if I am already connected to a certain DB? I want to maintain an open connection, I have a heartbeat to keep the connection alive, but I fear it may disconnect, and not know what to do.

	if (DBConnected == 0) then
		DBConnected, errormsg = mysql.connect(sqlip, sqluser, sqlpass, sqldb, sqlport)
	end

Is what I have to check, but I am very unsure of this, I know it will return zero if it failed to connect, but I doubt the value will change if it loses connection.

[TheTiger]

MySQL rocks file databases are laggy and big files