Raised This Month: $ Target: $400
 0% 

SQL Get column as cvar.


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
XmasterOfficial
Junior Member
Join Date: Oct 2014
Old 12-29-2015 , 13:51   Re: SQL Get column as cvar.
Reply With Quote #1

Quote:
Originally Posted by Bugsy View Post
What I gave you works why do you keep changing it
Because the way you did it doesn't help me. I need it to find if the server IP from get_user_ip 0 is the same as one of the IP-s from table Licenses. So in the table Licenses there would be like 5 columns. The name of every column would be an different IP and the value would be 1. So I need to check if the server ip is in the table Licenses as a column and if it is inside the table then it will server_print something and if not then server_print something else. Later I will change the server_print with a variable. Like if it is licensed then set licensed = 1 and if is not licensed then licensed = 0 and later in the plugin I will use that. If licensed == 1 then do something etc.
Edit: The way you gave me is working but I need to edit the .sma for every server and is not something I want.

Edit 2: I finaly found a way:
Code:
#include <amxmodx>
#include <sqlx>

new const Version[] = "1.0"
new const szHost[] = "HOST"
new const szUser[] = "USER"
new const szPass[] = "PASS"
new const szDB[] = "plugin_licenses"

new Handle:g_SQLTuple

public plugin_init() 
{
register_plugin( "Check IP License" , Version , "XmasterOfficial" )
g_SQLTuple = SQL_MakeDbTuple(szHost, szUser, szPass, szDB)
new error, szError[128]
new Handle:hConn = SQL_Connect(g_SQLTuple, error, szError, 127)
if( hConn == Empty_Handle ){
set_fail_state( szError )
}
new szIP[20]
new Handle:query
get_user_ip( 0 , szIP , charsmax( szIP ) , 1 )
query = SQL_PrepareQuery(hConn,"SELECT * FROM `Licenses` WHERE `%s`=1", szIP)
if(!SQL_Execute(query)){
server_print( "Server license not found!" )
} else {
server_print( "Server license found!" )
SQL_FreeHandle(query)
}
SQL_FreeHandle(hConn)
}

Last edited by XmasterOfficial; 12-29-2015 at 16:46.
XmasterOfficial is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 12-29-2015 , 20:19   Re: SQL Get column as cvar.
Reply With Quote #2

I'm not going to argue with you over this, but you're going about it the wrong way. Do you have any experience with databases or SQL? Why would you name your columns the server IP addresses? That makes no sense to me..can it work? I guess, but why? Use 2 columns, one for the IP addresses and one for the license status.
__________________
Bugsy is offline
XmasterOfficial
Junior Member
Join Date: Oct 2014
Old 12-29-2015 , 21:29   Re: SQL Get column as cvar.
Reply With Quote #3

Quote:
Originally Posted by Bugsy View Post
I'm not going to argue with you over this, but you're going about it the wrong way. Do you have any experience with databases or SQL? Why would you name your columns the server IP addresses? That makes no sense to me..can it work? I guess, but why? Use 2 columns, one for the IP addresses and one for the license status.
Because is easy for me in this way since I will use phpmyadmin to add all server IPs. So to add a column you just type the server IP and hit the Go button (it doesn't matter if is an INT or VARCHAR) but to add the IP as a value to a column then add the license status is harder.
XmasterOfficial is offline
fysiks
Veteran Member
Join Date: Sep 2007
Location: Flatland, USA
Old 12-29-2015 , 23:26   Re: SQL Get column as cvar.
Reply With Quote #4

Quote:
Originally Posted by XmasterOfficial View Post
Every column in "Licenses" have the IP of the servers. For example: Name: 192.168.1.2 and Value: 1. What could be the problem?
How does a column have a value?

Quote:
Originally Posted by XmasterOfficial View Post
Because is easy for me in this way since I will use phpmyadmin to add all server IPs. So to add a column you just type the server IP and hit the Go button (it doesn't matter if is an INT or VARCHAR) but to add the IP as a value to a column then add the license status is harder.
If it's hard to add a record to a table using existing columns via phpMyAdmin then I suspect that you are doing something wrong. It's not hard at all. Once you define the data type for each column, you never need to worry about it again (since you can't change it). You just click on "Insert" and then fill in the data for each column. So, in your case, you would simply type in the IP in the 'IP' column and a 1 in the 'Licensed' column. There would never be a reason edit the plugin when using this method when you add or remove a server's license.

P.S. Your query causes an error when the column doesn't exist (based on my testing).
__________________

Last edited by fysiks; 12-29-2015 at 23:31.
fysiks is offline
XmasterOfficial
Junior Member
Join Date: Oct 2014
Old 12-29-2015 , 23:59   Re: SQL Get column as cvar.
Reply With Quote #5

Quote:
Originally Posted by fysiks View Post
How does a column have a value?
Well you Insert it. On the Insert tab on phpmyadmin and you put 1 in the Value field. It doesn't matter what value does have the column. It matters what Name does the column have since I am looking to find the IP matching with the column name.

Quote:
Originally Posted by fysiks View Post
If it's hard to add a record to a table using existing columns via phpMyAdmin then I suspect that you are doing something wrong. It's not hard at all. Once you define the data type for each column, you never need to worry about it again (since you can't change it). You just click on "Insert" and then fill in the data for each column. So, in your case, you would simply type in the IP in the 'IP' column and a 1 in the 'Licensed' column. There would never be a reason edit the plugin when using this method when you add or remove a server's license.
I don't know but I find it easyer to just press the Add button under the table and fill the name with the server IP and hit Go.

Quote:
Originally Posted by fysiks View Post
P.S. Your query causes an error when the column doesn't exist (based on my testing).
That's exactly what I need. If there is no column matched with the server IP then it means that the server is not licensed. Else if there is a column (no matter what value does that column have, it can be empty) then the server is licensed. Also I did some more edits so now if somehow my plugin ends in a server that is not authorised then, the server IP will be put as a column in the table Unlicensed.

Here it is what I did so far:
Code:
#include <amxmodx>
#include <sqlx>

new const Version[] = "1.4"
new const szHost[] = "HOST"
new const szUser[] = "USER"
new const szPass[] = "PASS"
new const szDB[] = "plugin_licenses"

new Handle:g_SQLTuple
new unlicensed = 0

public plugin_init() 
{
register_plugin( "Check IP License" , Version , "Xmaster" )
g_SQLTuple = SQL_MakeDbTuple(szHost, szUser, szPass, szDB)
new error, szError[128]
new Handle:hConn = SQL_Connect(g_SQLTuple, error, szError, 127)
if( hConn == Empty_Handle ){
set_fail_state( szError )
}
new szIP[20]
new Handle:query
get_user_ip( 0 , szIP , charsmax( szIP ) , 1 )
query = SQL_PrepareQuery(hConn,"SELECT * FROM `Licensed` WHERE `%s`=1", szIP)
if(!SQL_Execute(query)){
server_print( "Server license not found!" )
new PostQuery[1001]
format(PostQuery,1000,"ALTER TABLE `Unlicensed` ADD `%s` INT NOT NULL",szIP) 
SQL_ThreadQuery(g_SQLTuple,"QueryHandle",PostQuery)
SQL_FreeHandle(query)
} else {
server_print( "Server license found!" )
SQL_FreeHandle(query)
}
SQL_FreeHandle(hConn)
}

public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
if(FailState == TQUERY_CONNECT_FAILED) {
server_print( "Server license not found!" )
return PLUGIN_CONTINUE
} else if(FailState == TQUERY_QUERY_FAILED) {
return PLUGIN_CONTINUE
}
if(Errcode) {
return PLUGIN_CONTINUE
}
return PLUGIN_CONTINUE
}
In the last part it doesn't matter if the query failed because the only way the query can fail is if the server IP is already inside the database so that means there is no reason to put it again (or better to say, it can't put it again since there is already a column with the server IP and it will create an query error).

Last edited by XmasterOfficial; 12-30-2015 at 00:14.
XmasterOfficial is offline
fysiks
Veteran Member
Join Date: Sep 2007
Location: Flatland, USA
Old 12-30-2015 , 01:33   Re: SQL Get column as cvar.
Reply With Quote #6

Quote:
Originally Posted by XmasterOfficial View Post
Well you Insert it. On the Insert tab on phpmyadmin and you put 1 in the Value field. It doesn't matter what value does have the column. It matters what Name does the column have since I am looking to find the IP matching with the column name.

I don't know but I find it easyer to just press the Add button under the table and fill the name with the server IP and hit Go.
Just because you find it easier does not make it the best method. If you are going to use a database, it will be in your interest to do it correctly the first time. Trust me. In the past I've tried to take a shortcut like this because it was easier but in the long run I had to do it all over using the correct method which generally takes longer than doing it correctly the first time.

This is especially true if at some point in the future you want to add additional functionality that requires that each "license" hold more information than just a 1 or a 0. For example, if you want to know "When was the last time that 111.222.333.444 connected to the database?" If you keep doing it your way, you would have to do it all over. If you did it the way Bugsy showed you, you'd simply add a column to the table (and maybe change a little bit of the code).

Another advantage is that you could actually put both licensed and unlicensed servers in the same table where the 'Licensed' column would be a 1 if licensed and a 0 if unlicensed. This would make it more convenient to, for example, change a unlicensed server to a licensed server. You would simply change the value of the 'Licensed' column instead of having to delete a column in one database and add a column in another.


Quote:
Originally Posted by XmasterOfficial View Post
That's exactly what I need. If there is no column matched with the server IP then it means that the server is not licensed. Else if there is a column (no matter what value does that column have, it can be empty) then the server is licensed. Also I did some more edits so now if somehow my plugin ends in a server that is not authorised then, the server IP will be put as a column in the table Unlicensed.
It is a really bad idea to rely on a query failure to indicate that the server is not licensed. What if the query fails for a reason other than the game server not being licensed? I know that you'll tell me that the plugin will just assume that it's unlicensed. However, in the interest of helping make your plugin better, it would be wise to have the ability to differentiate between a query failure and a server actually not being licensed. Then, you can tell the server operator "Unable to contact the license server" when the query fails and "Server not licensed" when the query succeed but found that the server is not licensed.


Ultimately, you came here for advice. We are trying to help you improve your plugin and structuring your data in the database is a very important part of this. We are not telling you that our way is better just because we feel like it, it's actually a fact.
__________________

Last edited by fysiks; 12-30-2015 at 01:43.
fysiks is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 12-30-2015 , 00:25   Re: SQL Get column as cvar.
Reply With Quote #7

You are literally altering the table with each IP addition. Why don't you take our advice and get this working the proper way instead of this hackery method.

You say you can't because its easier for you to just add a column in phpmyadmin. How is it easier to modify a table than it is to add a record to a table?
__________________
Bugsy is offline
Reply



Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 18:07.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode