[Roel]

Hello,

For those who doesn't have any knowlegde with MySQL, please skip this tutorial.

The thing what I will explain here is a way to make different calculations or getting results from tables without having to fetch the data inside your script.
Specialy for those who use MySQL R7 from BlueG, it can be useful if you don't wan't to blow yourself up with
dozens of threads.
Here is an example when this can be useful:
- Adding a amount of money to a player's bank account that isn't online(For example when somebody bought his vehicle or house).

Ofcourse you are able to do much more with it, but I didn't came up with other good examples...

Well lets start,

It's basicly a simple function that you save inside your database.
Below you see a empty function that we are going to fill in.

PHP код:

DELIMITER $$
CREATE FUNCTION `set_bank_amount`() RETURNS
BEGIN
END $$
DELIMITER ; 


Now the first thing we do is set some parameters.
The user ID and the amount of something.

PHP код:

DELIMITER $$
CREATE FUNCTION `set_bank_amount`(_id INT, _amount INT) RETURNS
BEGIN
END $$
DELIMITER ; 


Next thing we do is define what the function will return.
Let's say it returns a integer in this case.

PHP код:

DELIMITER $$
CREATE FUNCTION `set_bank_amount`(_id INT, _amount INT) RETURNS int(11)
BEGIN
END $$
DELIMITER ; 


Okay, now we define what kind of SQL statements the function will use.
You can choose between

PHP код:

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA 


We use READS SQL DATA in this case.

PHP код:

DELIMITER $$
CREATE FUNCTION `set_bank_amount`(_id INT, _amount INT) RETURNS int(11)
READS SQL DATA
BEGIN
END $$
DELIMITER ; 


Now we can go inside the function.
First we add a integer that saves the data that we will receive later.

PHP код:

DELIMITER $$
CREATE FUNCTION `set_bank_amount`(_id INT, _amount INT) RETURNS int(11)
READS SQL DATA
BEGIN
      DECLARE currentAmount INT;
END $$
DELIMITER ; 


Now we will start a SQL statement that selects the current back amount from a character.
This is a simple query like we always use.
Like you see it will use the _id that we set as parameter before.
It also stores the amount of the CHAR_BANK field into the currentAmount variable that we defined before.

PHP код:

DELIMITER $$
CREATE FUNCTION `set_bank_amount`(_id INT, _amount INT) RETURNS int(11)
READS SQL DATA
BEGIN
      DECLARE currentAmount INT;
      SELECT  `CHAR_BANK`
      INTO    currentAmount
      FROM    `plr_character`
      WHERE   `CHAR_ID` = _id;
END $$
DELIMITER ; 


After this we can make our calculation.
And return the value we want to have.

PHP код:

DELIMITER $$
CREATE FUNCTION `set_bank_amount`(_id INT, _amount INT) RETURNS int(11)
READS SQL DATA
BEGIN
      DECLARE currentAmount INT;
      SELECT  `CHAR_BANK`
      INTO    currentAmount
      FROM    `plr_character`
      WHERE   `CHAR_ID` = _id;
      SET currentAmount = currentAmount + _amount;
      RETURN currentAmount;
END $$
DELIMITER ; 


Like you see it counts the current amount with the amount we wanted to add (_amount).

Now our function is done.
Now I will explain how to store this function in your database (phpmyadmin).
So I think most of you use phpmyadmin, so I will only explain it for this SQL editor.
It's not that hard, you just open your database in phpmyadmin.
At the navigator (menu) you will see a button called, SQL, click on it.
Now you copy our function and past it there, you click on Go, and if it gives you a green textbar, it has been added to your database.
Now I show you how you can call this function inside a query:

PHP код:

UPDATE `plr_character` SET `CHAR_BANK` = set_bank_amount(1,1000000) WHERE `CHAR_ID`=1 


So this will add 1000000 to character id 1 bank account.

If you want to remove the function you can easy do this by running this query inside your phpmyadmin query box.

PHP код:

 DROP FUNCTION set_bank_amount 


You also can open the tab 'routines' inside phpmyadmin to manage your functions and procedures.

ADDON ; PROCEDURE;
You also can use procedures when you don't wan't to return anything but just want to do alot stuf without needing to run dozens of query's inside your gamemode.
I use this procedure to create a character at the moment.
But because it's not that different from functions I won't explain everything again, I hope you can understand what are the differents.

PHP код:

DELIMITER $$
CREATE PROCEDURE `createcharacter`(_name CHAR(26), _mainname CHAR(26))
MODIFIES SQL DATA
BEGIN
      DECLARE _charid INT;
      
      INSERT 
      INTO plr_character 
      (`CHAR_NAME`) 
      VALUES (_name);
      
      SELECT  `CHAR_ID`
      INTO    _charid
      FROM    `plr_character`
      WHERE   `CHAR_NAME` = _name LIMIT 1;
      
      UPDATE plr_character c, plr_account a 
          SET c.MAIN_ID = a.MAIN_ID, c.MAIN_NAME = a.MAIN_NAME,c.MAIN_IP = a.MAIN_IP 
          WHERE c.CHAR_ID = _charid AND a.MAIN_NAME=_mainname;
      
      INSERT INTO plr_house(`CHAR_ID`, `CHAR_NAME`) SELECT `CHAR_ID`, `CHAR_NAME` FROM `plr_character` WHERE `CHAR_ID` =_charid;
      INSERT INTO plr_items(`CHAR_ID`, `CHAR_NAME`) SELECT `CHAR_ID`, `CHAR_NAME` FROM `plr_character` WHERE CHAR_ID=_charid;
      INSERT INTO plr_jobs(`CHAR_ID`, `CHAR_NAME`) SELECT `CHAR_ID`, `CHAR_NAME` FROM `plr_character` WHERE CHAR_ID=_charid;
      INSERT INTO plr_secure(`CHAR_ID`, `CHAR_NAME`) SELECT `CHAR_ID`, `CHAR_NAME` FROM `plr_character` WHERE CHAR_ID=_charid;
      INSERT INTO plr_logs(`CHAR_ID`, `CHAR_NAME`, `MAIN_ID`) SELECT `CHAR_ID`, `CHAR_NAME`, `MAIN_ID` FROM `plr_character` WHERE CHAR_ID=_charid;
END $$
DELIMITER ; 


If you have any feedback or whatever, please tell me and I will look what I can do to make it better.

[Jarnu]

Fix typo :P

Код:

And return the valeu we want to have.

OnTopic: Learned something new in SQL thanks

[burnuk]

good job sharing your knowledge to us... keep it up...

[AndreT]

Nice tutorial, but there's a thing I don't understand. Your function returns r, which I don't see being a declared variable. I have not been in any decent contact with these functions and thereby cannot tell if it is a mistaken on your side or a predefined variable on the server side :P

Also, take note that the MySQL plugin actually creates only one thread which processes all the pooled queries. A new query does not initiate a new thread, the queries are just ran in another thread to avoid cluttering the main script thread.

Anyways, in the case you present here, adding a value to a value which you get from the DB using a custom function, wouldn't you lose all the hassle by storing the money value in your GM (I suppose it gets accessed quite often or no?).

// EDIT: lol Vince

[Vince]

PHP код:

UPDATE plr_character SET CHAR_BANK = CHAR_BANK + 1000000) WHERE CHAR_ID = 1 


[Roel]

Quote:

Originally Posted by Vince PHP код: UPDATE plr_character SET CHAR_BANK = CHAR_BANK + 1000000) WHERE CHAR_ID = 1

Lol , this might work, but I used this simple calculation for example.
It's just to make clear what you can do with functions.

[BMB1]

wow foreveed alone

[Roel]

wtf are you saying?

[playbox12]

I never actually used functions just because I never really had to, however I just realised for the script I'm working on right now, it'd actually be pretty neat. Thanks for freshening up my memory and the examples.