MySQL insert help - Printable Version

MySQL insert help - 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: MySQL insert help (/showthread.php?tid=613302)

MySQL insert help - Ahmed21 - 26.07.2016

Hello everyone, I've made houses system, it's based on a MySQL table. It loads the house and everything is going well, so my problem is in /createhouse. The command (/createhouse) inserts a new row on the mysql database, but I have to restart the server to load the new house, and this is a really bad idea to restart the server whenever I want to make a new house. Also, I don't want to reload ALL houses whenever I create a house, because if a player is in a house and the houses get reloaded, he'll get fucked.

So, how to get the ID of the house that I made using /createhouse (from the mysql table)? Help fast please.

My /createhouse command:

PHP код:



CMD:createhouse(playerid, params[])

{

    if(!IsAdminLevel(playerid, 5)) return NoPermsMSG(playerid);

    

    new price;

    if(sscanf(params, "i", price)) return SendUsageMSG(playerid, "/createhouse [PRICE]");

    if(price < 666) return SendErrorMSG(playerid, "Invalid house price.");

    

    new Float:X, Float:Y, Float:Z, string[128];

    GetPlayerPos(playerid, X, Y, Z);

    CreateHouse(price, X, Y, Z);

    SendAdminMSG(playerid, "You have created a new house.");

    format(string, sizeof(string), "%s(%d) has created a new house.", PlayerName(playerid), playerid);

    SendALogMSG(string);

    return 1;

}

CreateHouse function:

PHP код:



stock CreateHouse(price, Float:x, Float:y, Float:z)

{

    new query[128];

    mysql_format(mysql, query, sizeof(query), "INSERT INTO `houses` (`EnterX`, `EnterY`, `EnterZ`, `Price`) VALUES ('%f', '%f', '%f', '%d')", x, y, z, price);

    mysql_tquery(mysql, query, "", "");

}

Re: MySQL insert help - Konstantinos - 26.07.2016

In mysql_tquery, specify a callback and in that callback use cache_insert_id to retrieve the unique ID (the column has to be set with auto increment). I can't say about the index (if you have variables for the house data at all) much as you didn't post any additional information but having a global variable that stores the number of loaded houses will come in handy, not only for the loops but it is the index of the next house (the house to be created).

Re: MySQL insert help - Ahmed21 - 26.07.2016

Quote:

Originally Posted by Konstantinos

In mysql_tquery, specify a callback and in that callback use cache_insert_id to retrieve the unique ID (the column has to be set with auto increment). I can't say about the index (if you have variables for the house data at all) much as you didn't post any additional information but having a global variable that stores the number of loaded houses will come in handy, not only for the loops but it is the index of the next house (the house to be created).

Well, I got a variable "LoadedHouses", even it printf's how many houses were loaded when it finishes loading.
And ye there is a auto increment.

PHP код:



forward LoadHouses();

public LoadHouses()

{

    new rows, fields;

     cache_get_data(rows, fields, mysql);

    if(rows)

    {

        new housepw[4], housetitle[MAX_HOUSE_NAME];

        for(new i = 0; i < cache_get_row_count(); i++)

        {

            hInfo[i][hID] = cache_get_row_int(i, 0);

            cache_get_row(i, 2, housetitle);

            cache_get_row(i, 3, housepw);

            

            hInfo[hInfo[i][hID]][hOwner] = cache_get_row_int(i, 1);

            hInfo[hInfo[i][hID]][hInterior] = cache_get_row_int(i, 10);

            hInfo[hInfo[i][hID]][hPrice] = cache_get_row_int(i, 11);

            hInfo[hInfo[i][hID]][hEnterX] = cache_get_row_float(i, 4);

            hInfo[hInfo[i][hID]][hEnterY] = cache_get_row_float(i, 5);

            hInfo[hInfo[i][hID]][hEnterZ] = cache_get_row_float(i, 6);

            hInfo[hInfo[i][hID]][hExitX] = cache_get_row_float(i, 7);

            hInfo[hInfo[i][hID]][hExitY] = cache_get_row_float(i, 8);

            hInfo[hInfo[i][hID]][hExitZ] = cache_get_row_float(i, 9);

            hInfo[hInfo[i][hID]][hTitle] = housetitle;

            hInfo[hInfo[i][hID]][hPassword] = housepw;

            hInfo[hInfo[i][hID]][hWorldID] = hInfo[i][hID];

            

            new hEntStr[200];

            if(hInfo[hInfo[i][hID]][hOwner] == -1)

            {

                format(hEntStr, sizeof(hEntStr), ""COL_GOLD"House: "COL_WHITE"%s(%d)\n"COL_GOLD"Owner: "COL_WHITE"No-one\n"COL_GOLD"Price: "COL_WHITE"$%s", hInfo[hInfo[i][hID]][hTitle], hInfo[i][hID], AC(hInfo[hInfo[i][hID]][hPrice]));

            }

            if(hInfo[hInfo[i][hID]][hOwner] != -1)

            {

                format(hEntStr, sizeof(hEntStr), ""COL_GOLD"House: "COL_WHITE"%s(%d)\n"COL_GOLD"Owner: "COL_WHITE"%s\n"COL_GOLD"Price: "COL_WHITE"$%s", hInfo[hInfo[i][hID]][hTitle], hInfo[i][hID], GetNameFromMySQLID(hInfo[hInfo[i][hID]][hOwner]), AC(hInfo[hInfo[i][hID]][hPrice]));

            }

            hInfo[hInfo[i][hID]][sEnterLabel] = CreateDynamic3DTextLabel(hEntStr, -1, hInfo[hInfo[i][hID]][hEnterX], hInfo[hInfo[i][hID]][hEnterY], hInfo[hInfo[i][hID]][hEnterZ], 20.0, INVALID_PLAYER_ID, INVALID_VEHICLE_ID, 1, -1, 0, -1, 100.0);

            hInfo[hInfo[i][hID]][sExitLabel] = CreateDynamic3DTextLabel(""COL_GOLD"[EXIT]", -1, hInfo[hInfo[i][hID]][hExitX], hInfo[hInfo[i][hID]][hExitY], hInfo[hInfo[i][hID]][hExitZ], 20.0, INVALID_PLAYER_ID, INVALID_VEHICLE_ID, 1, hInfo[hInfo[i][hID]][hWorldID], hInfo[hInfo[i][hID]][hInterior], -1, 100.0);

            hInfo[hInfo[i][hID]][hEnterCP] = CreateDynamicCP(hInfo[hInfo[i][hID]][hEnterX], hInfo[hInfo[i][hID]][hEnterY], hInfo[hInfo[i][hID]][hEnterZ], 1.0, -1, 0, -1, 100.0);

            hInfo[hInfo[i][hID]][hExitCP] = CreateDynamicCP(hInfo[hInfo[i][hID]][hExitX], hInfo[hInfo[i][hID]][hExitY], hInfo[hInfo[i][hID]][hExitZ], 1.0, hInfo[hInfo[i][hID]][hWorldID], hInfo[hInfo[i][hID]][hInterior], -1, 100.0);

               LoadedHouses++;

        }

        printf("Loaded %d houses", LoadedHouses);

    }

    else if(!rows)

    {

        printf("There are NO houses to load");

    }

    return 1;

}

Re: MySQL insert help - Konstantinos - 26.07.2016

Okay, your way can be vastly improved. "hID" will hold the unique ID in the table and that's all you need - you shouldn't use it as an index in the "hInfo" array because reloading the houses would be necessary and you don't want that. Use "i" as an index instead.

When it comes to create a new house:

pawn Код:

if (LoadedHouses + 1 == sizeof hInfo) return ....
// error that limit was reached

...
// execute the query

// in the callback from mysql_tquery:
hInfo[LoadedHouses][hID] = cache_insert_id();
LoadedHouses++;

---

On another note, you keep calling cache_get_row_count multiple times. You got "rows" already so just:

pawn Код:

for (new i = 0; i < rows; i++)

and also the "else if" is not needed, just "else".

Re: MySQL insert help - Ahmed21 - 26.07.2016

Quote:

Originally Posted by Konstantinos

Okay, your way can be vastly improved. "hID" will hold the unique ID in the table and that's all you need - you shouldn't use it as an index in the "hInfo" array because reloading the houses would be necessary and you don't want that. Use "i" as an index instead.

When it comes to create a new house:

pawn Код:

if (LoadedHouses + 1 == sizeof hInfo) return ....
// error that limit was reached

...
// execute the query

// in the callback from mysql_tquery:
hInfo[LoadedHouses][hID] = cache_insert_id();
LoadedHouses++;

---

On another note, you keep calling cache_get_row_count multiple times. You got "rows" already so just:

pawn Код:

for (new i = 0; i < rows; i++)

and also the "else if" is not needed, just "else".

I can't understand anything tbh, because you're just saying put "load" bla bla without telling me where to put them. So just make it to load the house please? Now you know all the functions stuff etc... please do it.

Re: MySQL insert help - Ahmed21 - 26.07.2016

Bro, I will load the stuff from MySQL and create checkpoints and labels, u just connect the CreateHouse function with a callback (let it get the new house ID from the table), and I will do everything else.

Re: MySQL insert help - Konstantinos - 26.07.2016

Quote:

Originally Posted by Ahmed21

I can't understand anything tbh, because you're just saying put "load" bla bla without telling me where to put them.

You are using the unique ID from the table as the index of the array

Код:

hInfo[i][hID] = cache_get_row_int(i, 0); 
...
hInfo[hInfo[i][hID]][...]

which not only makes it required to reload the houses when creating a new one but will also cause problems (run time error 4) when the unique ID exceeds the limit of houses. What I'm basically saying is using the "i" (which is the iterator variable) as an index to avoid reloading houses - so when creating a house, it will be stored last in the array.

Код:

hInfo[i][hID] = cache_get_row_int(i, 0); 
...
hInfo[i][...]

---

In the /createhouse command:

Код:

if (LoadedHouses + 1 == sizeof hInfo) return ... error: that limit was reached

// rest of your code..
CreateHouse(price, X, Y, Z); 
// rest of your code..

In CreateHouse function, specify a callback in mysql_tquery function with no parameters like:

Код:

mysql_tquery(mysql, query, "OnHouseCreate", "");

In the callback from mysql_tquery (OnHouseCreate in our case):

Код:

hInfo[LoadedHouses][hID] = cache_insert_id();
// retrieve the last unique ID from the table ..
// .. "LoadedHouses" is the index of the array

LoadedHouses++;
// Increasing by 1 as a new house created

Re: MySQL insert help - Ahmed21 - 26.07.2016

Quote:

Originally Posted by Konstantinos

You are using the unique ID from the table as the index of the array

Код:

hInfo[i][hID] = cache_get_row_int(i, 0); 
...
hInfo[hInfo[i][hID]][...]

Код:

hInfo[i][hID] = cache_get_row_int(i, 0); 
...
hInfo[i][...]

---

In the /createhouse command:

Код:

if (LoadedHouses + 1 == sizeof hInfo) return ... error: that limit was reached

// rest of your code..
CreateHouse(price, X, Y, Z); 
// rest of your code..

In CreateHouse function, specify a callback in mysql_tquery function with no parameters like:

Код:

mysql_tquery(mysql, query, "OnHouseCreate", "");

In the callback from mysql_tquery (OnHouseCreate in our case):

Код:

hInfo[LoadedHouses][hID] = cache_insert_id();
// retrieve the last unique ID from the table ..
// .. "LoadedHouses" is the index of the array

LoadedHouses++;
// Increasing by 1 as a new house created

Thank you so much!

Re: MySQL insert help - Ahmed21 - 26.07.2016

Oh damn, it creates a row and saves the house, but it doesn't create a checkpoint/label, why?!!!

Re: MySQL insert help - Konstantinos - 26.07.2016

Because you only create the checkpoints and labels on loading (when the server starts). In CreateHouse function, you got the coordinates as parameters so execute the query and then create them.