Housing System - Loading from MySQL

Housing System - Loading from 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: Housing System - Loading from MySQL (/showthread.php?tid=463627)

Housing System - Loading from MySQL - AphexCCFC - 13.09.2013

pawn Код:

stock LoadHouseInfo()
{
    new query[300], savingstring[20], string[256];
    for(new houseid=1; houseid<MAX_HOUSES; houseid++)
    {
        format(query, sizeof(query), "SELECT * FROM houses WHERE ID=%d", houseid);
        mysql_query(query);
        mysql_store_result();
        if(mysql_num_rows())
        while(mysql_fetch_row_format(query,"|"))
        {
            mysql_fetch_field_row(savingstring, "Address"); HouseInfo[houseid][hAddress] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Owner"); HouseInfo[houseid][hOwner] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Owned"); HouseInfo[houseid][hOwned] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Price"); HouseInfo[houseid][hPrice] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "X"); HouseInfo[houseid][hX] = floatstr(savingstring);
            mysql_fetch_field_row(savingstring, "Y"); HouseInfo[houseid][hY] = floatstr(savingstring);
            mysql_fetch_field_row(savingstring, "Z"); HouseInfo[houseid][hZ] = floatstr(savingstring);
        }
        mysql_free_result();
        HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 0);
        format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
        HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 20.0);
        printf("All houses have been successfully loaded.");
        format(string, sizeof(string), "House ID: %f", HouseInfo[houseid][hX]);
        printf(string);
        houseid++;
        break;
    }
    return 1;
}

Then under OnGameModeInit I have:

pawn Код:

LoadHouseInfo();

It doesn't load the houses I've created from the database into the variables. Any idea why? I've spent hours trying to fix this, been up all night with no joy :/

Re: Housing System - Loading from MySQL - WopsS - 13.09.2013

pawn Код:

stock LoadHouseInfo()
{
    new query[300], savingstring[20], string[256];
    for(new houseid=1; houseid<MAX_HOUSES; houseid++)
    {
        format(query, sizeof(query), "SELECT * FROM houses");
        mysql_query(query);
        mysql_store_result();
        if(mysql_num_rows())
        while(mysql_fetch_row_format())
        {
            mysql_fetch_field_row(savingstring, "Address"); HouseInfo[houseid][hAddress] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Owner"); HouseInfo[houseid][hOwner] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Owned"); HouseInfo[houseid][hOwned] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Price"); HouseInfo[houseid][hPrice] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "X"); HouseInfo[houseid][hX] = floatstr(savingstring);
            mysql_fetch_field_row(savingstring, "Y"); HouseInfo[houseid][hY] = floatstr(savingstring);
            mysql_fetch_field_row(savingstring, "Z"); HouseInfo[houseid][hZ] = floatstr(savingstring);
        }
        mysql_free_result();
        HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 0);
        format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
        HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 20.0);
        printf("All houses have been successfully loaded.");
        format(string, sizeof(string), "House ID: %f", HouseInfo[houseid][hX]);
        printf(string);
        houseid++;
        break;
    }
    return 1;
}

What plugin you use?

Re: Housing System - Loading from MySQL - DanishHaq - 13.09.2013

On the database: localhost/phpmyadmin, make sure that all your floats are Varchar's, with around 75 characters to use. The same thing happened to me.. And that fixed it.

Else, if that doesn't work, try and post your mysql_log.txt

Re: Housing System - Loading from MySQL - AphexCCFC - 13.09.2013

Hello. I am using MySQL plugin R6-2.

I have tried setting the floats as varchars but still with no success.

Here is my code incase I changed it a little:

pawn Код:

stock LoadHouseInfo()
{
    new query[300], savingstring[20], string[256];
    for(new houseid=1; houseid<MAX_HOUSES; houseid++)
    {
        format(query, sizeof(query), "SELECT * FROM houses WHERE ID=%d", houseid);
        mysql_query(query);
        mysql_store_result();
        if(mysql_num_rows() == 1) continue;
        while(mysql_fetch_row_format(query,"|"))
        {
            mysql_fetch_field_row(savingstring, "Address"); HouseInfo[houseid][hAddress] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Owner"); HouseInfo[houseid][hOwner] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Owned"); HouseInfo[houseid][hOwned] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Price"); HouseInfo[houseid][hPrice] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "X"); HouseInfo[houseid][hX] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Y"); HouseInfo[houseid][hY] = strval(savingstring);
            mysql_fetch_field_row(savingstring, "Z"); HouseInfo[houseid][hZ] = strval(savingstring);
        }
        mysql_free_result();
        HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 0);
        format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
        HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 20.0);
        printf("All houses have been successfully loaded.");
        format(string, sizeof(string), "House ID: %d", HouseInfo[houseid][hID]);
        printf(string);
        houseid++;
        break;
    }
    return 1;
}

The issue isn't with the database because the information saves into the database, it just doesn't save into the variables. When I restart the gamemode the house doesn't recreate.

Re: Housing System - Loading from MySQL - AphexCCFC - 13.09.2013

Anyone?

Re: Housing System - Loading from MySQL - AphexCCFC - 13.09.2013

Wow.. Bump.

Re: Housing System - Loading from MySQL - Kirollos - 13.09.2013

I have tweaked this abit, also i have commented your errors etc..

pawn Код:

stock LoadHouseInfo()
{
    new query[100], savingstring[20], string[256];
    for(new houseid=1; houseid<MAX_HOUSES; houseid++)
    {
        format(query, sizeof(query), "SELECT * FROM houses WHERE ID=%d", houseid);
        mysql_query(query);
        mysql_store_result();
        if(mysql_num_rows())
        {
            while(mysql_fetch_row_format(query,"|"))
            {
                /*mysql_fetch_field_row(savingstring, "Address"); HouseInfo[houseid][hAddress] = strval(savingstring);
                mysql_fetch_field_row(savingstring, "Owner"); HouseInfo[houseid][hOwner] = strval(savingstring);*/
                /*i wonder why you use strval to convert strings? Look below for good ones for strings..*/

                //Strings
                mysql_fetch_field_row(savingstring, "Address"); format(HouseInfo[houseid][hAddress], 100, "%s", savingstring);
                mysql_fetch_field_row(savingstring, "Owner"); format(HouseInfo[houseid][hOwner], MAX_PLAYER_NAME, "%s", savingstring);

                //Integers
                mysql_fetch_field_row(savingstring, "Owned"); HouseInfo[houseid][hOwned] = strval(savingstring);
                mysql_fetch_field_row(savingstring, "Price"); HouseInfo[houseid][hPrice] = strval(savingstring);

                //Floats
                mysql_fetch_field_row(savingstring, "X"); HouseInfo[houseid][hX] = floatstr(savingstring);
                mysql_fetch_field_row(savingstring, "Y"); HouseInfo[houseid][hY] = floatstr(savingstring);
                mysql_fetch_field_row(savingstring, "Z"); HouseInfo[houseid][hZ] = floatstr(savingstring);
            }
            
            HouseInfo[houseid][hID] = houseid;
            HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 0);
            format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
            HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 20.0);
            /*format(string, sizeof(string), "House ID: %f", HouseInfo[houseid][hX]);
            printf(string);*/
            printf("House ID: %i", HouseInfo[houseid][hID]);
            //houseid++; // no need
            //break; // Why zdding this? :/
         }
         mysql_free_result();
    }
    printf("All houses have been successfully loaded.");
    return 1;
}

Re: Housing System - Loading from MySQL - AphexCCFC - 13.09.2013

Quote:

Originally Posted by kirollos

I have tweaked this abit, also i have commented your errors etc..

pawn Код:

stock LoadHouseInfo()
{
    new query[100], savingstring[20], string[256];
    for(new houseid=1; houseid<MAX_HOUSES; houseid++)
    {
        format(query, sizeof(query), "SELECT * FROM houses WHERE ID=%d", houseid);
        mysql_query(query);
        mysql_store_result();
        if(mysql_num_rows())
        {
            while(mysql_fetch_row_format(query,"|"))
            {
                /*mysql_fetch_field_row(savingstring, "Address"); HouseInfo[houseid][hAddress] = strval(savingstring);
                mysql_fetch_field_row(savingstring, "Owner"); HouseInfo[houseid][hOwner] = strval(savingstring);*/
                /*i wonder why you use strval to convert strings? Look below for good ones for strings..*/

                //Strings
                mysql_fetch_field_row(savingstring, "Address"); format(HouseInfo[houseid][hAddress], 100, "%s", savingstring);
                mysql_fetch_field_row(savingstring, "Owner"); format(HouseInfo[houseid][hOwner], MAX_PLAYER_NAME, "%s", savingstring);

                //Integers
                mysql_fetch_field_row(savingstring, "Owned"); HouseInfo[houseid][hOwned] = strval(savingstring);
                mysql_fetch_field_row(savingstring, "Price"); HouseInfo[houseid][hPrice] = strval(savingstring);

                //Floats
                mysql_fetch_field_row(savingstring, "X"); HouseInfo[houseid][hX] = floatstr(savingstring);
                mysql_fetch_field_row(savingstring, "Y"); HouseInfo[houseid][hY] = floatstr(savingstring);
                mysql_fetch_field_row(savingstring, "Z"); HouseInfo[houseid][hZ] = floatstr(savingstring);
            }
            
            HouseInfo[houseid][hID] = houseid;
            HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 0);
            format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
            HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 20.0);
            /*format(string, sizeof(string), "House ID: %f", HouseInfo[houseid][hX]);
            printf(string);*/
            printf("House ID: %i", HouseInfo[houseid][hID]);
            //houseid++; // no need
            //break; // Why zdding this? :/
         }
         mysql_free_result(); // This is the warning line.
    }
    printf("All houses have been successfully loaded.");
    return 1;
}

Hello kirollos, thanks for replying.
I have tried this code and it gives a warning:

Код:

C:\Users\Mike\Desktop\FA-RP\gamemodes\MySQL.pwn(2118) : warning 217: loose indentation

The warning line has been displayed in the code but despite this warning, when I compile and run the server it shows the "All houses have been successfully loaded" print but not the "House ID: %i" print.

Here is the CREATE TABLE for 'houses':

pawn Код:

mysql_query("CREATE TABLE IF NOT EXISTS houses(ID INT(10), Address VARCHAR(24), Owner VARCHAR(24), Owned INT(10), Price INT(10), X FLOAT(20), Y FLOAT(20), Z FLOAT(20) )");

Here is the enum for the house variables and strings:

pawn Код:

enum hInfo
{
    hID,
    hAddress[50],
    hOwner[50],
    hOwned,
    Float:hX,
    Float:hY,
    Float:hZ,
    hPrice,
    hPickUp,
    Text3D:hText
}

new HouseInfo[MAX_HOUSES][hInfo];

Here is the /createhouse command:

pawn Код:

CMD:createhouse(playerid, params[])
{
    new string[128], query[300], Float:x, Float:y, Float:z;
    GetPlayerPos(playerid, x, y, z);
    if(isnull(params)) return SendClientMessage(playerid, -1, ""Grey"Syntax"White": /createhouse [address].");
    for(new houseid=1; houseid<MAX_HOUSES; houseid++)
    {
        if(HouseInfo[houseid][hID] != 0) continue;
        HouseInfo[houseid][hID] = houseid;
        format(HouseInfo[houseid][hAddress], 100, "%s", params);
        format(HouseInfo[houseid][hOwner],  50, "None");
        HouseInfo[houseid][hOwned] = 0;
        HouseInfo[houseid][hPrice] = 120000;
        HouseInfo[houseid][hX] = x;
        HouseInfo[houseid][hY] = y;
        HouseInfo[houseid][hZ] = z;
        HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, x, y, z, 0);
        format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
        HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, x, y, z, 20.0);
        format(query, sizeof(query), "INSERT INTO houses (ID, Address, Owner, Owned, Price, X, Y, Z) VALUES(%d, '%s', '%s', %d, %d, %f, %f, %f)", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hOwned], HouseInfo[houseid][hPrice], HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ]);
        mysql_query(query);
        format(string, sizeof(string), ""Green"Notice"White": You have created house ID %d.", HouseInfo[houseid][hID]);
        SendClientMessage(playerid, -1, string);
        break;
    }
    return 1;
}

Here is a screenshot of my 'houses' table after using /createhouse:

Re: Housing System - Loading from MySQL - Emmet_ - 13.09.2013

Quote:

Originally Posted by DanishHaq

On the database: localhost/phpmyadmin, make sure that all your floats are Varchar's, with around 75 characters to use. The same thing happened to me.. And that fixed it.

Else, if that doesn't work, try and post your mysql_log.txt

Why should you set the floats to use varchar values? The float keyword was made for a reason; you were probably using it incorrectly which would explain why it wasn't working as it should.

Try this:

pawn Код:

stock LoadHouseInfo()
{
    new
        rows,
        field[24],
        string[256],
        houseid = 0;

    mysql_query("SELECT * FROM `houses`");
    mysql_store_result();

    rows = mysql_num_rows();
    if (rows)
    {
        while (mysql_retrieve_row())
        {
            mysql_fetch_field_row(field, "Address"); format(HouseInfo[houseid][hAddress], 50, field);
            mysql_fetch_field_row(field, "Owner"); format(HouseInfo[houseid][hOwner], 50, field);
            mysql_fetch_field_row(field, "Owned"); HouseInfo[houseid][hOwned] = strval(field);
            mysql_fetch_field_row(field, "Price"); HouseInfo[houseid][hPrice] = strval(field);
            mysql_fetch_field_row(field, "X"); HouseInfo[houseid][hX] = floatstr(field);
            mysql_fetch_field_row(field, "Y"); HouseInfo[houseid][hY] = floatstr(field);
            mysql_fetch_field_row(field, "Z"); HouseInfo[houseid][hZ] = floatstr(field);

            HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 0);
            format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
            HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 20.0);
            houseid++;
        }
        printf("All houses have been successfully loaded.");
    }
    mysql_free_result();
    return 1;
}

Re: Housing System - Loading from MySQL - Kirollos - 13.09.2013

Quote:

Originally Posted by Emmet_

pawn Код:

stock LoadHouseInfo()
{
    new
        rows,
        field[24],
        string[256],
        houseid = 0;

    mysql_query("SELECT * FROM `houses`");
    mysql_store_result();

    rows = mysql_num_rows();
    if (rows)
    {
        while (mysql_retrieve_row())
        {
            mysql_fetch_field_row(field, "Address"); HouseInfo[houseid][hAddress] = strval(field);
            mysql_fetch_field_row(field, "Owner"); HouseInfo[houseid][hOwner] = strval(field);
            mysql_fetch_field_row(field, "Owned"); HouseInfo[houseid][hOwned] = strval(field);
            mysql_fetch_field_row(field, "Price"); HouseInfo[houseid][hPrice] = strval(field);
            mysql_fetch_field_row(field, "X"); HouseInfo[houseid][hX] = floatstr(field);
            mysql_fetch_field_row(field, "Y"); HouseInfo[houseid][hY] = floatstr(field);
            mysql_fetch_field_row(field, "Z"); HouseInfo[houseid][hZ] = floatstr(field);

            HouseInfo[houseid][hPickUp] = CreateDynamicPickup(1273, 1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 0);
            format(string, sizeof(string), ""Green"ID: %d\nHouse Address: %s\nOwner: %s\nPrice: $%d", HouseInfo[houseid][hID], HouseInfo[houseid][hAddress], HouseInfo[houseid][hOwner], HouseInfo[houseid][hPrice]);
            HouseInfo[houseid][hText] = CreateDynamic3DTextLabel(string, -1, HouseInfo[houseid][hX], HouseInfo[houseid][hY], HouseInfo[houseid][hZ], 20.0);
            houseid++;
        }
        printf("All houses have been successfully loaded.");
    }
    mysql_free_result();
    return 1;
}

Why strval to convert string to string