[appleomax]

I'm trying to create house system with mysql, but I don't know how to load and create every house from mysql table.

Here's my try, and that for() loop makes my server laggy

Код:

stock LoadHouses(playerid)
{
    new query[300], pname[24],savingstring[20] ,labelstring[100];
    new HousePrice,id = HouseCount;
    new world;
    new Float:x,Float:y,Float:z;
    for(new i = 0; i < MAX_HOUSES;i++)
 	{
    GetPlayerName(playerid, pname, 24);
    format(query, sizeof(query), "SELECT * FROM housedata WHERE id = '%d'", i);
    mysql_query(query); 
    mysql_store_result(); 
    while(mysql_fetch_row_format(query,"|"))
    {
    mysql_fetch_field_row(savingstring, "price"); HousePrice = strval(savingstring);
    mysql_fetch_field_row(savingstring, "id"); HouseCount = strval(savingstring);
    mysql_fetch_field_row(savingstring, "world"); world = strval(savingstring);
	mysql_fetch_field_row(savingstring, "xpos"); x = strval(savingstring);
    mysql_fetch_field_row(savingstring, "ypos"); y = strval(savingstring);
    mysql_fetch_field_row(savingstring, "zpos"); z = strval(savingstring);
	format(labelstring,sizeof(labelstring),"House: %i \nPrice: %i",HouseCount, HousePrice);
	HInfo[id][HouseLabel] = Create3DTextLabel(labelstring,0xFF0000FF,x,y,z,25.0,world);
    }
    format(labelstring,sizeof(labelstring),"House: %i \nPrice: %i",HouseCount, HousePrice);
	HInfo[id][HouseLabel] = Create3DTextLabel(labelstring,0xFF0000FF,x,y,z,25.0,world);
    }
    mysql_free_result();
    return 1;
}

[Scenario]

You should be using threaded queries. Here's an example of loading houses from a MySQL database using the cache functions provided by BlueG's MySQL plugin.

pawn Код:

mysql_tquery(connectionHandle, "SELECT * FROM `houses`", "thread_LoadHouses", ""); // put this under OnGameModeInit to load all of the houses

public thread_LoadHouses()
{
    /*
        Mock table structure:
        
            houseID
            houseType
            housePrice
            houseOwned
            houseOwner
            housePosX
            housePosY
            housePosZ
            houseIntPosX
            houseIntPosY
            houseIntPosZ
            houseInterior
    */

    new
        rows,
        fields,
        szTempString[30];
    
    cache_get_data(rows, fields, connectionHandle);
    
    if(rows > 0)
    {
        for(new i = 0; i < rows; i++)
        {
            cache_get_row(i, 0, szTempString), houseInfo[i][houseID] = strval(szTempString);
            cache_get_row(i, 1, szTempString), houseInfo[i][houseType] = strval(szTempString);
            cache_get_row(i, 2, szTempString), houseInfo[i][housePrice] = strval(szTempString);
            cache_get_row(i, 3, szTempString), houseInfo[i][houseOwned] = strval(szTempString);
            cache_get_row(i, 4, houseInfo[i][houseOwner], MAX_PLAYER_NAME);         
            cache_get_row(i, 5, szTempString), houseInfo[i][housePosX] = floatstr(szTempString);
            cache_get_row(i, 6, szTempString), houseInfo[i][housePosY] = floatstr(szTempString);            
            cache_get_row(i, 7, szTempString), houseInfo[i][housePosZ] = floatstr(szTempString);            
            cache_get_row(i, 8, szTempString), houseInfo[i][houseIntPosX] = floatstr(szTempString);         
            cache_get_row(i, 9, szTempString), houseInfo[i][houseIntPosY] = floatstr(szTempString);
            cache_get_row(i, 10, szTempString), houseInfo[i][houseIntPosZ] = floatstr(szTempString);
            cache_get_row(i, 11, szTempString), houseInfo[i][houseInterior] = strval(szTempString);
        }
        
        printf("DEBUG INFO: %d houses have been loaded.", rows);
    }
    return 1;
}

There are other natives to avoid some of that code, but I don't use them myself so I didn't use them in this code. Nevertheless, the point was to show you an example of using threaded queries to load all of your houses efficiently.