[Tutorial] Saving weapon data (MySQL)

[Tutorial] Saving weapon data (MySQL) - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] Saving weapon data (MySQL) (/showthread.php?tid=505081)

Pages: 1 2 3

Re: Saving weapon data (MySQL) - vassilis - 25.11.2015

So vince after reading it again and came to conclusion of how to use foreign keys and parental/child structuring. I have a small concern which i didn't actually get correctly in my brain. I can't actually understand how to get something from a field(Sorry if i speak bad English and you can't understand). Example:
I am making a House System and as you had told me in a post at scripting section

Quote:

House Owner should be linked to the player's unique id in the player table. A player's name is only to appear once in the entire database. Any other tables that require the player's information must link to the original table via a foreign key.

How could i set a player owner of a house or how to get if a player is owner of the house using id from player table?
What i did and i don't really think could work well is changing Username on my account table to Unique and then i setted a relation view through HOwner(variable for house owner name from houses table) with Username from account table. How could it work? In other words? How i could get the owner of the house(ID or name) using foreign key ?

Re: Saving weapon data (MySQL) - Vince - 25.11.2015

Like I said, the name should only appear once in the database. While it is technically possible to set up a foreign key relation with a text type field, it is discouraged to do so because string comparison is obviously much slower than integer comparison. Especially for varchar fields where length is not fixed.

If you have it properly set up you should have a column "ownedBy" or "ownerId" or something like that in the house table. That column only contains numeric ids, like 5 or 42. This id corresponds to ONE player. You can go and look this up in the player table to find out who it is.

Now, there's obviously also queries that do this. You can do this with two separate queries, or with a join. That depends on the situation.

PHP Code:



SELECT house.*, player.name 
FROM house
JOIN player
ON house.ownerId = player.id

That will give all columns from the house table (house.*), plus an extra column which contains the player's name from the player table (player.name).

Re: Saving weapon data (MySQL) - vassilis - 26.11.2015

Got it.

Re: Saving weapon data (MySQL) - vassilis - 05.03.2016

PHP Code:



public OnLoadPlayerWeapons(playerid)
{
    new
        weaponid,
        ammo;
    
    for(new i, j = cache_get_row_count(userDB); i < j; i++) // loop through all the rows that were found
    {
        weaponid     = cache_get_row_int(i, 0, userDB);
        ammo        = cache_get_row_int(i, 1, userDB);
        
        if(!(0 <= weaponid <= 46)) // check if weapon is valid (should be)
        {
            printf("[info] Warning: OnLoadPlayerWeapons - Unknown weaponid '%d'. Skipping.", weaponid);
            continue;
        }
        
        GivePlayerWeapon(playerid, weaponid, ammo); 
    }
    return;
}

Vince shouldn't this return a value at the end?

Re: Saving weapon data (MySQL) - SyS - 05.03.2016

Quote:

Originally Posted by vassilis

Aha i got it.. Seems good and readable good job vince.. i wonder if you would make something similar for y_ini it would really help me

+rep though!

humm check my works u can get it for y ini

OT:Thanks Vince u r always helpful

Re: Saving weapon data (MySQL) - vassilis - 05.03.2016

Quote:

Originally Posted by Sreyas

humm check my works u can get it for y ini

OT:Thanks Vince u r always helpful

This post was 2 years ago i have learned to use MySQL mostly!

Re: Saving weapon data (MySQL) - SyS - 05.03.2016

Quote:

Originally Posted by vassilis

This post was 2 years ago i have learned to use MySQL mostly!

lol im sorry

Re: Saving weapon data (MySQL) - vernz - 08.05.2016

What do you do if you have a weapon in the slot 7, weaponid 35 by example and then you buy a weapon who use the same slot like weaponid 36, it will create a new weapon and leave the previous one in the table.. anything we could do for that ?

Re: Saving weapon data (MySQL) - PrO.GameR - 08.05.2016

Well as this topic is bumped I might aswell do it too, Vince why do you go to such long lengths to update a row on duplicates? Why don't you use REPLACE keyword? it acts like exactly like INSERT and if such unique ids exists it just updates them

Re: Saving weapon data (MySQL) - Konstantinos - 08.05.2016

Quote:

Originally Posted by vernz

What do you do if you have a weapon in the slot 7, weaponid 35 by example and then you buy a weapon who use the same slot like weaponid 36, it will create a new weapon and leave the previous one in the table.. anything we could do for that ?

As long as you execute a DELETE query before, it doesn't matter.

Quote:

Originally Posted by PrO.GameR

Why don't you use REPLACE keyword? it acts like exactly like INSERT and if such unique ids exists it just updates them

http://stackoverflow.com/questions/9...168948#9168948
http://stackoverflow.com/questions/9...66919#27966919

Re: Saving weapon data (MySQL) - vernz - 08.05.2016

So it would be something like:

PHP Code:



CMD:givewep(playerid, params[])

{

    new userid, weaponid, ammo, query[156];


    //if(PlayerInfo[playerid][pAdmin] < 4) return SendErrorMessage(playerid, ERROR_ADMIN);

    if(sscanf(params, "udI(500)", userid, weaponid, ammo)) return SendSyntaxMessage(playerid, "/givewep [playerid/name] [weaponid] [ammo]");

    if(userid == INVALID_PLAYER_ID) return SendErrorMessage(playerid, ERROR_PLAYER);

    if(weaponid <= 0 || weaponid > 46 || (weaponid >= 19 && weaponid <= 21)) return SendErrorMessage(playerid, "You have specified an invalid weapon.");


    mysql_format(mysql, query, sizeof(query), "DELETE FROM weapons WHERE Slot=%d", GetWeaponSlot(weaponid));

    mysql_tquery(mysql, query, "", "");



    GivePlayerWeapon(playerid, weaponid, ammo);

    SendInfoMessage(playerid, "You have gave %s a %s with %d ammo.", ReturnName(userid, 0), ReturnWeaponName(weaponid), ammo);

    return 1;

}

Just an example with the givewep command.

Re: Saving weapon data (MySQL) - Konstantinos - 08.05.2016

I was referring mostly on the loop (for example saving all weapons on disconnect). What PrO.GameR suggested though is way better. Having userid + weapon slot as unique so if the a weapon is inserted with the same slot, it updates the weaponid and ammo.

Re: Saving weapon data (MySQL) - vernz - 08.05.2016

I was trying to do it, but it wasn't working for some reason, nothing was updating, I remove weaponid as unique and added the field "slot" as unique with the userid, I am not sure how to do it properly:

This is my insert query:

PHP Code:



mysql_format(mysql, query, sizeof(query), "INSERT INTO weapons VALUES (%d, %d, %d, %d) ON DUPLICATE KEY UPDATE Slot=%d", PlayerInfo[playerid][pID], weaponid, ammo, GetWeaponSlot(weaponid), GetWeaponSlot(weaponid));

            mysql_tquery(mysql, query, "", "");

I am entire sure how it should be

EDIT:

This is working I think, is it correct ? :

PHP Code:



mysql_format(mysql, query, sizeof(query), "INSERT INTO weapons VALUES (%d, %d, %d, %d) ON DUPLICATE KEY UPDATE Weaponid=%d, Ammo=%d", PlayerInfo[playerid][pID], weaponid, ammo, GetWeaponSlot(weaponid), weaponid, ammo);

            mysql_tquery(mysql, query, "", "")

Re: Saving weapon data (MySQL) - Konstantinos - 08.05.2016

Keep "userid + weaponid" as primary and "userid + weaponslot" as unique index. Your last query is correct.

Re: Saving weapon data (MySQL) - vernz - 08.05.2016

Now I get it, thank you ! I was also wondering why the weaponid field is 0 ?

PHP Code:



weaponid     = cache_get_row_int(i, 0, mysql);

        ammo        = cache_get_row_int(i, 1, mysql);

Why not userid 0, weapon 1, ammo 2 etc ?

Re: Saving weapon data (MySQL) - Vince - 08.05.2016

Because the SELECT portion of the query only selects those two columns and not the entire row.

Re: Saving weapon data (MySQL) - vernz - 08.05.2016

Got it, thank you !

Re: Saving weapon data (MySQL) - vernz - 08.05.2016

Another question, if I want to know what items I have, is there a better way than that:

PHP Code:



CMD:myitems(playerid, params[])

{

    new query[128];

    mysql_format(mysql, query, sizeof(query), "SELECT ItemID, ItemQuantity FROM inventory WHERE Userid=%d", PlayerInfo[playerid][pID]);

    mysql_tquery(mysql, query, "myitems", "i", playerid);

    return 1;

}


forward myitems(playerid);

public myitems(playerid)

{

    new itemid, itemquantity;


    for(new i, j = cache_get_row_count(mysql); i < j; i++)

    {

        itemid                 = cache_get_row_int(i, 0, mysql);

        itemquantity        = cache_get_row_int(i, 1, mysql);


        SendInfoMessage(playerid, "ItemID: %d | ItemQuantity: %d", itemid, itemquantity);

    }

    return 1;

}

or this is the right way ?

Quote:

Originally Posted by vernz

Another question, if I want to know what items I have, is there a better way than that:

PHP Code:



CMD:myitems(playerid, params[])

{

    new query[128];

    mysql_format(mysql, query, sizeof(query), "SELECT ItemID, ItemQuantity FROM inventory WHERE Userid=%d", PlayerInfo[playerid][pID]);

    mysql_tquery(mysql, query, "myitems", "i", playerid);

    return 1;

}


forward myitems(playerid);

public myitems(playerid)

{

    new itemid, itemquantity;


    for(new i, j = cache_get_row_count(mysql); i < j; i++)

    {

        itemid                 = cache_get_row_int(i, 0, mysql);

        itemquantity        = cache_get_row_int(i, 1, mysql);


        SendInfoMessage(playerid, "ItemID: %d | ItemQuantity: %d", itemid, itemquantity);

    }

    return 1;

}

or this is the right way ?

you don't need to use mysql_format when you don't escape.
other then that, you don't check for race condition (if player uses the cmd and /q and another player connects with same ID and the callback is executed for him).

Re: Saving weapon data (MySQL) - vernz - 09.05.2016

What should I do then ?

If I'm doing an inventory system like this, how can I compare if the playerid has a specific item in the child table ?