[vassilis]

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 ?

[Vince]

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).

[vassilis]

Got it.

[vassilis]

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?

[SyS]

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

[vassilis]

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!

[SyS]

Quote:

Originally Posted by vassilis This post was 2 years ago i have learned to use MySQL mostly!

lol im sorry

[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 ?

[PrO.GameR]

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

[Konstantinos]

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

[vernz]

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.

[Konstantinos]

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.

[vernz]

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, "", "") 


[Konstantinos]

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

[vernz]

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 ?

[Vince]

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

[vernz]

Got it, thank you !

[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 ?

[Richie©]

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).

[vernz]

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 ?