AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   MySQL Help (https://forums.alliedmods.net/showthread.php?t=12505)

v3x 04-17-2005 01:50

MySQL Help
 
Alright, this is my first attempt at making a plugin that involves MySQL. Is everything here correct? It's not storing anything into the database.

Code:
#include <amxmodx> #include <amxmisc> #include <dbi> public plugin_init() {     register_plugin("Store user info","0.1","v3x")     register_clcmd("storeinfo","doInsert")     set_task(0.1,"sql_init") } new Sql:dbc new Result:result new Result:userexists public sql_init() {     new host[64], username[32], password[32], dbname[32], error[32]     get_cvar_string("amx_sql_host",host,63)     get_cvar_string("amx_sql_user",username,31)     get_cvar_string("amx_sql_pass",password,31)     get_cvar_string("amx_sql_db",dbname,31)     dbc = dbi_connect(host,username,password,dbname,error,31)     if(dbc == SQL_FAILED) {         log_amx("[AMXX] Failed to connect to DB")     } else {         dbi_query(dbc,"CREATE TABLE IF NOT EXISTS `userinfo` (`name` VARCHAR(32) NOT NULL,`steamid` VARCHAR(32) NOT NULL,`ip` VARCHAR(63) NOT NULL,`num` INT NOT NULL, PRIMARY KEY(`steamid`))")     } } public client_connect(id) {     new steamid[32]     get_user_authid(id,steamid,31)     new name[32]     get_user_name(id,name,31)     new ip[64]     get_user_ip(id,ip,63,1)     new num[32]     userexists = dbi_query(dbc,"SELECT steamid FROM userinfo WHERE steamid = '%s'",steamid)         if(userexists) {         result = dbi_query(dbc,"UPDATE connects SET num=num+1 WHERE steamid = '%s'",steamid)         dbi_free_result(result)     }     else if(!userexists) {         num[31] = 1         result = dbi_query(dbc,"INSERT INTO userinfo (name,steamid,ip,num) values ('%s','%s','%d','%d')",name,steamid,ip,num)         dbi_free_result(result)     }     dbi_free_result(userexists)     return PLUGIN_HANDLED } public doInsert(id) { // Used for testing!     new steamid[32]     get_user_authid(id,steamid,31)     new name[32]     get_user_name(id,name,31)     new ip[64]     get_user_ip(id,ip,63,1)     new num[32]     userexists = dbi_query(dbc,"SELECT * FROM userinfo WHERE steamid = '%s'",steamid)         if(dbc == SQL_FAILED) {         console_print(id,"[AMXX] Couldn't connect to MySQL server!")     }     if(dbc == SQL_OK) {         console_print(id,"[AMXX] Connected to the MySQL server")     }     if(dbi_num_rows(userexists) < 1) {         num[31] = 1         result = dbi_query(dbc,"INSERT INTO userinfo (name,steamid,ip,num) values ('%s','%s','%s','1')",name,steamid,ip)         if(result == RESULT_OK)             console_print(id,"[AMXX] Success!")         else if(result == RESULT_FAILED)             console_print(id,"[AMXX] Success!")         dbi_free_result(result)     }     else {         result = dbi_query(dbc,"UPDATE connects SET num=num+1 WHERE steamid = '%s'",steamid)         switch(result) {             case RESULT_OK: console_print(id,"[AMXX] Success (updated)!")             case RESULT_FAILED: console_print(id,"[AMXX] Failed to update")         }         dbi_free_result(result)     }     dbi_free_result(userexists)     return PLUGIN_HANDLED }

XxAvalanchexX 04-17-2005 02:55

a) I thought you knew how to code! ;-) If an array's size is 64, the parameter value should be 63. If the size is 32, the parameter should be 31. This goes for all of those get_cvar_* functions you use.

b) result = dbi_query(dbc,"INSERT INTO userinfo (name,steamid,ip,num) values ('%s','%s','%s')",name,steamid,ip,num)

Count the number of of %s. Count the name of extra parameters at the end.

v3x 04-17-2005 03:38

Who is to say I coded that part? ;)

Anywho, I updated the above code slightly.. I made it into a command and put a bunch of console_print() functions in there to test it out. It doesn't print anything into console.. Any help?

slurpycof 04-17-2005 06:25

I use log_amx for testing to make sure I see the results. I have written a few SQL plugins if you want to look through my code. Noy saying I did it the best way, but it works well.

v3x 04-17-2005 06:39

Do you see anything wrong?

slurpycof 04-17-2005 07:04

These are set in the sql.cfg so these are not needed
Code:
public plugin_init() {     register_plugin("Store user info","0.1","v3x")     register_clcmd("storeinfo","doInsert") //    register_cvar("amx_sql_host","") //    register_cvar("amx_sql_user","") //    register_cvar("amx_sql_pass","") //    register_cvar("amx_sql_db","") //    register_cvar("amx_sql_table","")     // Removed the values Smile     set_task(0.1,"sql_init") }

Why is the IP 48?

connects is not a database
Code:
 userexists = dbi_query(dbc,"SELECT steamid FROM connects WHERE steamid = '%s'",steamid)

I am assuming you are using the num field to track connects?
Code:
 userexists = dbi_query(dbc,"SELECT num FROM userinfo WHERE steamid = '%s'",steamid) result = dbi_query(dbc,"UPDATE userinfo SET num=num+1 WHERE steamid = '%s'",steamid)

you declare new ip[64] but use %d to try and store it
Also, you do not need to assign a value "num[31] = 1", just use a 1 instead of %d with a replace
Code:
 result = dbi_query(dbc,"INSERT INTO userinfo (name,steamid,ip,num) values ('%s','%s','%d','%d')",name,steamid,ip,num)

I will look through it more later when I am awake and don't have a baby on my lap

v3x 04-17-2005 07:14

48?

Anywho..
Code:
userexists = dbi_query(dbc,"SELECT steamid FROM username_db WHERE steamid = '%s'",steamid)
Like that?

Because in PHP, I select it from a table like so:
Code:
$result = mysql_query("SELECT * FROM pages WHERE cl='$cl'") or die(mysql_error());

slurpycof 04-17-2005 07:25

This is how I would have done it.

Code:
public client_connect(id) {     new steamid[32]     get_user_authid(id,steamid,31)     new name[32]     get_user_name(id,name,31)     new ip[64]     get_user_ip(id,ip,63,1)         //check for user     userexists = dbi_query(dbc,"SELECT * FROM userinfo WHERE steamid = '%s'",steamid)         if (dbi_num_rows(result) < 1) { //first time, create new entry         result = dbi_query(dbc,"INSERT INTO userinfo (name,steamid,ip,num) values ('%s','%s','%s',1)",name,steamid,ip)         dbi_free_result(result)     }else{ //Record exists         result = dbi_query(dbc,"UPDATE userinfo SET num=num+1 WHERE steamid = '%s'",steamid)         dbi_free_result(result)     }     dbi_free_result(userexists)     return PLUGIN_HANDLED }

and the 48 comment is about this:
you declare the ip as "new ip[64]"
but you create a db field that is "`ip` VARCHAR( 48 ) NOT NULL,"

v3x 04-17-2005 07:36

Don't you mean..
Code:
if (dbi_num_rows(userexists) < 1) { //first time, create new entry
?

slurpycof 04-17-2005 07:37

yes, sorry


All times are GMT -4. The time now is 09:59.

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