PDA

View Full Version : Getting seperate tables[MYSQL]


vassilis
01/12/2015, 11:25 PM
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?

CmZxC
02/12/2015, 12:48 AM
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.


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);
}

PinkFloydLover
02/12/2015, 04:13 AM
I was wondering about this a little while ago, i remember you could do something like:


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.

feartonyb
02/12/2015, 04:32 AM
I was wondering about this a little while ago, i remember you could do something like:


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.

Dokins
02/12/2015, 09:03 AM
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(...

vassilis
02/12/2015, 04:39 PM
I was wondering about this a little while ago, i remember you could do something like:


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

JasperM
02/12/2015, 05:52 PM
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'

Vince
02/12/2015, 06:24 PM
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.

vassilis
02/12/2015, 09:46 PM
That's how it should be done.

so i create a variable named

PlayerID[MAX_PLAYERS]

then at the function that player logs in i will use
PlayerID[playerid] = cache_get_row_int(columntame)
and then what?
I Use the variable to set same value at `HouseOwnerID` column?

Dokins
02/12/2015, 09:55 PM
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:


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