Getting seperate tables[MYSQL]

Getting seperate tables[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)
+--- Thread: Getting seperate tables[MYSQL] (/showthread.php?tid=595363)

Getting seperate tables[MYSQL] - vassilis - 01.12.2015

How i can get one value named ID at table1 and set it at table2 with same value
Example
if my table name is players and i have registered with name vassilis and automatically gets `ID` = 0 then
I want my player when he is buying a house to get thevalue from `players`/`ID` table/column and put the value to the second table named `houses` at column `HouseOwnerID` which is the same just in different table?
In other words how i can store one value from the first table to the second?

Re: Getting seperate tables[MYSQL] - CmZxC - 02.12.2015

I have tried to make a reference point for you, but don't take me as an expert - I'm rather the opposite with mysql.

PHP код:



new MySQL;

test(playerid)

{

    new query[128],

        Name[MAX_PLAYER_NAME],

        houseBeingEditedID = 15; // you use your own method of finding out which house is requiring editing


    GetPlayerName(playerid, Name[playerid], MAX_PLAYER_NAME);

    //---- find out player ID by name

    mysql_format(MySQL, query, sizeof(query), "SELECT * FROM `players` WHERE `name` = '%e' LIMIT 1", Name[playerid]);

    mysql_tquery(MySQL, query);

    new localID = cache_get_field_content_int(0, "ID");

    cache_delete(); // not necessary

    //---- localid = A_I'd player ID now

    mysql_format(MySQL, query, sizeof(query), "UPDATE `houses` SET `HouseOwnerID`=%i WHERE `ID`=%i", localID, houseBeingEditedID);

    mysql_tquery(MySQL, query);

}

Re: Getting seperate tables[MYSQL] - PinkFloydLover - 02.12.2015

I was wondering about this a little while ago, i remember you could do something like:

pawn Код:

UPDATE `houses` SET `HouseOwnerID`= (SELECT `ID` FROM `players` WHERE `name` = '%s') WHERE `HouseID` = '%d'

I tried a similar query with my player/vehicle tables just then and it worked fine.

EDIT: forgot to mention that doing this will make this query take considerably longer than normal and should be avoided at all costs, as others have said its better to get the SQLID when the player connects and use from there.

Re: Getting seperate tables[MYSQL] - feartonyb - 02.12.2015

Quote:

Originally Posted by PinkFloydLover

I was wondering about this a little while ago, i remember you could do something like:

pawn Код:

UPDATE `houses` SET `HouseOwnerID`= (SELECT `ID` FROM `players` WHERE `name` = '%s') WHERE `HouseID` = '%d'

I tried a similar query with my player/vehicle tables just then and it worked fine.

That's much better solution as it saves you some memory because you do it only at one query without using any variables.

Re: Getting seperate tables[MYSQL] - Dokins - 02.12.2015

Why don't you retrieve the players MySQL ID when they log in and then save it into a variable?

Retrieve the row using their name as a reference and then use something like

PlayerSQLID[playerid] = cache_get_row_int(...

Re: Getting seperate tables[MYSQL] - vassilis - 02.12.2015

Quote:

Originally Posted by PinkFloydLover

I was wondering about this a little while ago, i remember you could do something like:

pawn Код:

UPDATE `houses` SET `HouseOwnerID`= (SELECT `ID` FROM `players` WHERE `name` = '%s') WHERE `HouseID` = '%d'

I tried a similar query with my player/vehicle tables just then and it worked fine.

I guess thats the best way..!

Re: Getting seperate tables[MYSQL] - JasperM - 02.12.2015

Why don't you just fetch all the players info when they connect, so you can do something like this:

UPDATE `houses` SET `HouseOwnerID`=pInfo[playerid][userID] WHERE `HouseID`='%d'

Re: Getting seperate tables[MYSQL] - Vince - 02.12.2015

Quote:

Originally Posted by Dokins

Why don't you retrieve the players MySQL ID when they log in and then save it into a variable?

That's how it should be done.

Re: Getting seperate tables[MYSQL] - Dokins - 02.12.2015

Once you've stored it, you can manipulate it in any way.

To save it to the house owner it's simple, just do this:

pawn Код:

format(query, sizeof(query), "UPDATE `houses` SET `HouseOwnerID`= %d WHERE `HouseID` = %d, PlayerID[playerid], //houseid);

Also, don't use ' ' around %d as it's a fraction slower as it has to convert from a string to an integer!
(Just a tip).