· 10 years ago · Jun 23, 2016, 06:36 PM
1// Net message for the UI
2util.AddNetworkString( "munch_mysqlmenu" )
3util.AddNetworkString( "munch_mysqlmenu2" )
4
5// List containing everything we need for MySQL
6Stats_MySQL = {}
7
8// Simple variable for our data
9Stats_MySQL.Data = {}
10
11// MySQL Connection Settings
12Stats_MySQL.Host = "127.0.0.1"
13Stats_MySQL.Username = "root"
14Stats_MySQL.Password = "test"
15Stats_MySQL.Port = 3306
16Stats_MySQL.Database = "sys"
17
18// Connection Info
19Stats_MySQL.Connection = nil
20
21require( "mysqloo" )
22
23// Connect to the database
24local db = mysqloo.connect( Stats_MySQL.Host, Stats_MySQL.Username, Stats_MySQL.Password, Stats_MySQL.Database, Stats_MySQL.Port )
25// If the connection is a success
26function db:onConnected()
27 Stats_MySQL.Connection = self
28 Stats_MySQL:CheckTable()
29end
30// If the connection is a failure
31function db:onConnectionFailed( err )
32 print( "MySQL Error: "..err )
33end
34db:connect()
35
36// Creates the needed table if it doesn't exist already
37function Stats_MySQL:CheckTable()
38 // Make sure we're connected
39 if not self.Connection then
40 print( "MySQL Error: Not Connected!" )
41 return
42 end
43
44 // Create the table if it doesn't exist
45 local q = self.Connection:query( "CREATE TABLE IF NOT EXISTS test_table( steamid varchar(64) NOT NULL PRIMARY KEY, kills int, description varchar(100) );" )
46 // If the query succeeded
47 function q:onSuccess( data )
48 Stats_MySQL:GetData()
49 end
50 // If it failed
51 function q:onError( err )
52 print( "MySQL Error: "..err )
53 end
54 q:start()
55end
56
57// Get everything fron the table
58function Stats_MySQL:GetData()
59 // Make sure we're connected
60 if not self.Connection then
61 print( "MySQL Error: Not Connected!" )
62 return
63 end
64
65 // Get all the data
66 local q = self.Connection:query( "SELECT * FROM test_table;" )
67 // If the query succeeded
68 function q:onSuccess( data )
69 print( "MySQL Data:" )
70 PrintTable( data )
71 for k, v in pairs( data ) do
72 Stats_MySQL.Data[v.steamid] = v
73 end
74 end
75 // If it failed
76 function q:onError( err )
77 print( "MySQL Error: "..err )
78 end
79 q:start()
80end
81
82// Add some data or update it if the SteamID exists in the table
83function Stats_MySQL:AddUpdateData( steamid, kills, desc )
84 // Make sure we're connected
85 if not self.Connection then
86 print( "MySQL Error: Not Connected!" )
87 return
88 end
89
90 // Make the data MySQL safe
91 // DO THIS FOR ANY USER-INPUT DATA (LIKE TEXT)
92 desc = self.Connection:escape( desc )
93
94 // Add the data
95 local q = self.Connection:query( "INSERT INTO test_table VALUES( \""..steamid.."\", "..kills..", \""..desc.."\" ) ON DUPLICATE KEY UPDATE kills="..kills..", description=\""..desc.."\";" )
96 // If the query succeeded
97 function q:onSuccess( data )
98 Stats_MySQL.Data[steamid] = { steamid=steamid, kills=kills, description=desc }
99 print( "Inserted/Updated value in table" )
100 end
101 // If it failed
102 function q:onError( err, sql )
103 print( "MySQL Error: "..err )
104 end
105 q:start()
106end
107
108// Remove data by SteamID
109function Stats_MySQL:DeleteData( steamid )
110 // Make sure we're connected
111 if not self.Connection then
112 print( "MySQL Error: Not Connected!" )
113 return
114 end
115
116 // Add the data
117 local q = self.Connection:query( "DELETE FROM test_table WHERE steamid=\""..steamid.."\"" )
118 // If the query succeeded
119 function q:onSuccess( data )
120 Stats_MySQL.Data[steamid] = nil
121 print( "Removed value from table" )
122 end
123 // If it failed
124 function q:onError( err, sql )
125 print( "MySQL Error: "..err )
126 end
127 q:start()
128end
129
130net.Receive( "munch_mysqlmenu", function( len, ply )
131 local steamid = net.ReadString()
132 local kills = tonumber( net.ReadString() )
133 local desc = net.ReadString()
134 Stats_MySQL:AddUpdateData( steamid, kills, desc )
135end )
136
137net.Receive( "munch_mysqlmenu2", function( len, ply )
138 net.Start( "munch_mysqlmenu2" )
139 net.WriteTable( Stats_MySQL.Data )
140 net.Send( ply )
141end )