PDA

View Full Version : MySQL question


NeXoR
11/05/2016, 05:44 AM
Hey guys,
I'm changing my saving method from Dini to MySQL
I have finished the registration & login and now I have to modify all the offline commands
Offline Bans, Offline Admin removals, Offline leadership removals etc etc

My question is,
How can I do this:
format(file, sizeof(file), "users/%s.ini", playerb);
if(!dini_Exists(file)) return SendClientMessage(playerid, COLOR_GREY, "Player name not found.");
if(PlayerInfo[playerid][pAdmin] < dini_Int(file, "Admin")) return SendClientMessage(playerid, COLOR_GREY, "Player has a higher admin level than you.");
In a MySQL way WITHOUT using further callbacks

Gammix
11/05/2016, 05:57 AM
Using mysql_query i guess.
"SELECT * FROM `table` WHERE `condition` = 'match'"

And then just check if there are any rows or not using cache_get_row_count (0 means not existing).

You can also checkout sql.inc which allows you MySQL with SQLite syntax and lot more, in case you are interested or unaware.

]Rafaellos[
11/05/2016, 06:02 AM
new
Cache:result,
Query[128]
;

mysql_format(MySQL, Query, sizeof(Query), "SELECT `Admin` FROM `Users` WHERE `Username` = '%e'", playerb);
result = mysql_query(MySQL, Query);

if(cache_get_row_count(MySQL))
{
new
adminLevel
;

adminLevel = cache_get_row_int(0, 0, MySQL);

if(PlayerInfo[playerid][pAdmin] < adminLevel)
{
SendClientMessage(playerid, COLOR_GREY, "Player has a higher admin level than you.");
}
else
{
mysql_format(MySQL, Query, sizeof(Query), "UPDATE `Users` SET `Admin` = %d WHERE `Username` = '%e'", newLevel, playerb);
mysql_tquery(MySQL, Query, "", "");
}
}
cache_delete(result);

Untested but it will give you the idea.

NeXoR
11/05/2016, 06:11 AM
Rafaellos[;3698314']new
Cache:result,
Query[128]
;

mysql_format(MySQL, Query, sizeof(Query), "SELECT `Admin` FROM `Users` WHERE `Username` = '%e'", playerb);
result = mysql_query(MySQL, Query);

if(cache_get_row_count(MySQL))
{
new
adminLevel
;

adminLevel = cache_get_row_int(0, 0, MySQL);

if(PlayerInfo[playerid][pAdmin] < adminLevel)
{
SendClientMessage(playerid, COLOR_GREY, "Player has a higher admin level than you.");
}
else
{
mysql_format(MySQL, Query, sizeof(Query), "UPDATE `Users` SET `Admin` = %d WHERE `Username` = '%e'", newLevel, playerb);
mysql_tquery(MySQL, Query, "", "");
}
}
cache_delete(result);

Untested but it will give you the idea.

Well unfortunately I didn't know about that Cache: variable
I'll try it out

Using mysql_query i guess.
"SELECT * FROM `table` WHERE `condition` = 'match'"

And then just check if there are any rows or not using cache_get_row_count (0 means not existing).

You can also checkout sql.inc which allows you MySQL with SQLite syntax and lot more, in case you are interested or unaware.

Thanks, I'll take a look

]Rafaellos[
11/05/2016, 06:13 AM
Well unfortunately I didn't know about that Cache: variable
I'll try it out

Well, if you want it WITHOUT further callbacks, you have to use Cache.

Sjn
11/05/2016, 06:30 AM
I'd still go with callbacks rather than using cached variables. It's way more easier (for me). And instead of creating way too many callbacks, simply create one callback using separate thread ids. For me, it's pretty faster.

NeXoR
11/05/2016, 07:08 AM
I'd still go with callbacks rather than using cached variables. It's way more easier (for me). And instead of creating way too many callbacks, simply create one callback using separate thread ids. For me, it's pretty faster.

I thought about creating a callback for all offline modification commands, But I don't know how to set an ID for each CMD, It would be great if you explain me

Sjn
11/05/2016, 08:31 AM
I have many result ids defined in the following ways for separate threads.

#define SQL_THREAD_NONE 0 // Thread used for executing normal queries (no result)
#define SQL_THREAD_OFFLINEBAN 1
#define SQL_THREAD_OFFLINELEVEL 2


And for a callback I have something like

// You can add other parameters in the public callback for more values
forward OnQueryReceive(result_id, int_value, string[], handle);
public OnQueryReceive(result_id, int_value, string[], handle)
{
new rows, fields;

if (result_id != SQL_THREAD_NONE) // If the thread has no result, just execute the query without fetching the data
{
cache_get_data(rows, fields);
}

switch (result_id)
{
case SQL_THREAD_OFFLINEBAN:
{
if (rows)
{
// Row exists, code here
}
else
{
// Row doesn't exist, return error or something
}
}
// More threads bellow
}

return 1;
}


And for executing the query

mysql_tquery(handle, Query, "OnQueryReceive", "iis", SQL_THREAD_OFFLINEBAN, integer_val, string_val);


This is how I use them, all of my queries gets executed through one callback using separated thread ids.

Slawiii
11/05/2016, 10:27 AM
what's mean integer_val and string_val

i want to trasfer this from mysql r5 to mysql r 33+


format(query, sizeof(query), "SELECT * FROM `players` WHERE `name` = '%s'", PlayerName);
mysql_query(query, MYSQL_QUERY_CONNECT, playerid);

Slawiii
13/05/2016, 12:13 PM
BUMP

ilijap
13/05/2016, 04:31 PM
IDK what version are you using but on the most popular version (for php) is like this:

I am making example:

CMD:offban(playerid)
{
new name[MAX_PLAYER_NAME];
if(sscanf(params, "s[24]" name)) return SendClientMesage(playerid, -1, "USE LIKE THIS: /offban [name]");
new check = mysql_feth_array(mysql_query("SELECT * FROM `tablenamehere` WHERE `rowforname` = 'name'"));
if(check == 0) return SendClientMesage(playerid, -1, "USERNAME NOT FOUND");
new adminplayer = check["rowadminhere"]; // i dont think this is the way how you do it on samp :v change it
if(adminplayer > hereyouradmindefinition for playerid) return SendClientMessage(playerid, -1, "That player has a greater admin level than you");
mysql_query("UPDATE `tablenamehere` SET `Banned` = 1 WHERE `namehere`='name'");
return 1;
}

Thats the php way :v its not much different in samp i think :3