[Dubya]

Not sure how to explain, but take a look at my query and tell me if what i'm trying to do is possible in one query:

pawn Код:

mysql_tquery(mysql,"SELECT `id`,`houses`.`ownerid`,`name`,`door`,`x`,`y`,`z`,`ex`,`ey`,`ez,`evw`,`eint`,`safe,`pot`,`coke`,`acid`,`price`,`owner`=(SELECT `username` FROM `accounts` WHERE `accounts`.`id`=`houses`.`ownerid` LIMIT 1;) FROM `houses` WHERE 1", "OnLoadHouses");

[00:00:11] [ERROR] CMySQLQuery::Execute[OnLoadHouses] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`,`eint`,`safe,`pot`,`coke`,`acid`,`price`,`owner `=(SELECT `username` FROM `acco' at line 1
[00:00:11] [DEBUG] CMySQLQuery::Execute[OnLoadHouses] - error will be triggered in OnQueryError

[BroZeus]

And what are you trying to do?
If you are trying to load all houses OnGameModeInit then this would do :

PHP код:

mysql_tquery(mysql, "SELECT * FROM `houses`", "OnLoadHouses", ""); 


And if you are trying to load a particular house that belongs to a player online then this would do :

PHP код:

new pname[MAX_PLAYER_NAME], query[100];
GetPlayerName(playerid, pname, sizeof(pname));//playerid = the player who's house is to be loaded
mysql_format(mysql, query, sizeof(query), "SELECT * FROM `houses` WHERE `owner` = '%e'", pname);
mysql_tquery(mysql, query, "OnLoadHouses", ""); 


[Vince]

A player's name should not appear more than once in the entire database. Seeing as a house can only be owned by one player you don't require an associative table here, but you do need to link it to the player table by means of the id. I don't think you need the "owner" field at all seeing as you already have a field "ownerid" which is probably linked like that.

So, if you want to load each house, along with the name of the player who owns it, you will need a join. If you want all houses regardless of whether they're currently owned use a left join. If you only want houses that are currently owned use an inner join.

PHP код:

SELECT houses.*, IFNULL(accounts.name, 'No-One') AS name FROM houses
LEFT JOIN accounts ON houses.ownerid = accounts.id 


[Konstantinos]

PHP код:

SELECT h.*,a.username FROM houses h LEFT JOIN accounts a ON a.id=h.ownerid GROUP BY h.id; 


In case you use cache_get_row/cache_get_row_* functions, the player's name will be last.

EDIT: Didn't see the above, sorry.