[Include] YourSQL - Making a MYSQL query's simplified
#1

yourSQL 1.3

About

YourSQL is a very simple include that simplifies the usage of MYSQL querys in pawn.
However, you must have already a database structure, if you dont have it ? you can set one up with PhpmyAdmin.

It has the following functions:
Код:
yoursql_connect(host[],username[],database[],password[])//returns nothing
yoursql_close()//closes the connection (in case you want to switch to another database, this is standard called when you exit your gamemode/filterscript)

yoursql_insert(table[],type[],{float,_}:...)UPDATED!//returns 1 if the query was executed, 0 when there was an mysql error
yoursql_update(table[],updater[],identifier[])//returns 1 if the query was executed, 0 when there was an mysql error
yoursql_delete(table[],identifier[])//returns 1 if the query was executed, 0 when there was an mysql error
yoursql_receive(strquery[])//will return the splitted information if the query was executed, and the query itself if there was a mysql_error

yoursql_countrows(strquery[])//returns the ammount of selected rows
yoursql_escape_string(str[]);//Returns escaped string
How to use:

yoursql_connect(host,username,database,password)
This will connect to the database, there is no need to close the connection (this will automaticly be done on GameModeExit or OnFilterScriptExit).

Example script:
pawn Код:
public OnFilterScriptInit()
{
     yoursql_connect("localhost","root","users","");
     return 1;
}
//this will connect to the mysql server

yoursql_insert(table[],type[],{Float,_}:...) & yoursql_escape_string(str)
This allows you to insert easily data into your database.

Example script:
pawn Код:
stock OnPlayerRegister(playerid)
{
     yoursql_insert("users","sii",yoursql_escape_string(PlayerInfo[playerid][UserName]),PlayerInfo[playerid][money],PlayerInfo[playerid][score]);
/*

This will insert all the info from the enum in the database

Explaining the input:
table: users
types: string(s), int(i) and another int(i)
values: PlayerInfo[playerid][UserName],PlayerInfo[playerid][money],PlayerInfo[playerid][score]

Available types:
s: string
i: int
f: float

About the values:
Please note that you ALWAYS need to use yoursql_escape_string() before entering/updating a string comming directly from the user. Else your database will be voulnerable to mysql injections!
*/

     return 1;
}
yoursql_update(table[],updater[],identifier[])
This will allow you to update existing data in your database.

Example script:
pawn Код:
stock SavePlayer(playerid)
{
     new string[128],string2[64],pName[MAX_PLAYER_NAME];
     GetPlayerName(playerid,pName,sizeof pName);
     format(string,sizeof string,"'%s',%i,%i",yoursql_escape_string(PlayerInfo[playerid][UserName]),PlayerInfo[playerid][money],PlayerInfo[playerid][score]);
     format(string2,sizeof string2,"Username = '%s'",pName); //the identifier, so it will actually execute UPDATE blablabla WHERE Username = (player's username here)
     yoursql_update("users",string,string2)
     return 1;
}
/*this will update all the changes from the enum in the database.

Table: Users

New values: yoursql_escape_string(PlayerInfo[playerid][UserName]),PlayerInfo[playerid][money],PlayerInfo[playerid][score]
*!Please notice that this works just like a query, if you update a string you NEED to use quotemarks for them (instead of value you use 'value')

Identifier: Where username = yourname //so it will only update where the username is
equal to the selected rows, you need to have a little bit knowledge about mysql using this.
*/
yoursql_delete(table[],identifier[])
This allows you to delete a record from the database.

Example script:
pawn Код:
COMMAND:deleteme(playerid,params[])
{
     new string[64],pName[MAX_PLAYER_NAME];GetPlayerName(playerid,pName);
     format(string,sizeof string,"WHERE username = '%s'",pName);
     yoursql_delete("users",string);
     return 1;
}
//this will remove the player from the database
yoursql_receive(table[],identifier[])
This will read data from the database

Example script:
pawn Код:
stock OnPlayerLogin(playerid)
{
     new string[128],pName[MAX_PLAYER_NAME];GetPlayerName(playerid,pName,sizeof pName);
     format(string,sizeof string,"SELECT * FROM USERS WHERE `username` = '%s'",pName);
     sscanf(yoursql_receive(string),"p<|>e<s[MAX_PLAYER_NAME]ii>",PlayerInfo[playerid]); //the function returns value1|value2|value3, with sscanf you can split this, or put it directly in a enum!
     return 1;
}
//receives the data from the database, it will receive all data in one string!
You can use sscanf to split them (split character: '|')
yoursql_countrows(strquery[])
This will count the selected rows in a query

Example script:
pawn Код:
stock IsPlayerAlreadyRegistered(playerid)
{
     new string[128],pName[MAX_PLAYER_NAME];GetPlayerName(playerid,pName,sizeof pName);
     format(string,sizeof string,"SELECT * FROM users WHERE username = 's'",pName);
     if(yoursql_countrows(strquery) > 0) return true;
     return false;
}
//counts selected rows, in this case:
if now row has been selected (so there ISNT a row where username = (player's name)), the player is not registered.
Credits:
I specially want to thank ******, Ryder & Hiddos for helping me figuring out the getarg/numarg part. This was really a pain in the arse finding out that my windows XP was bugged (so it never worked at that pc, and DID work on my other computers) and this was not possible without them.

DOWNLOAD:
The include:
http://pastebin.com/VTyARFn1

G-sTyLeZzZ's MYSQL plugin:
https://sampforum.blast.hk/showthread.php?tid=56564
Reply
#2

Looks good, ill check it out soon.
Reply
#3

Cool =D
but mysql is very hard to learn ;\
Reply
#4

Quote:
Originally Posted by Ricop522
Посмотреть сообщение
Cool =D
but mysql is very hard to learn ;\
No, it really isn't, and this simplifies it.

Good job - but C-MySQL is still better for newbies. You should also contemplate creating a function that can parse multiple values and query threading.
Reply
#5

No. Its easier than write to files and do that things, in my mind. And ye, nice work.
Reply
#6

Quote:
Originally Posted by Calg00ne
Посмотреть сообщение
You should (...) query threading.
Yeah , will be added in futhur version


Quote:
Originally Posted by Calg00ne
Посмотреть сообщение
You should also contemplate creating a function that can parse multiple values
what do you mean exactly ?
Reply
#7

Something like:
pawn Код:
yoursql_insert(blababla, "isf", 1, string, float);
Reply
#8

Quote:
Originally Posted by [L3th4l]
Посмотреть сообщение
Something like:
pawn Код:
yoursql_insert(blababla, "isf", 1, string, float);
I always wondered.... how do i do that ? (being able to add multiple strings/floats/ints behind one function)
Reply
#9

Quote:
Originally Posted by Calg00ne
Посмотреть сообщение
You should also contemplate creating a function that can parse multiple values and query threading.
This has now been added in V1.1, thanks for the tip

For the others: please read the new way to use yoursql_insert(), its much easyer/efficient now!
Reply
#10

Quote:
Originally Posted by Ricop522
Посмотреть сообщение
Cool =D
but mysql is very hard to learn ;\
That's what you have been told and you have a closed mind. It sounds hard because its using a database, but its just like setting up a regular file system. This makes it easier to understand, and should help people to realize that MYSQL is very easy to use and very versatile.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)