[Juvanii]

Hello guys, i have created a table for houses using mysql, a column called houseid is like:


i wanna know how to get the missing house id (1) to use it in /createhouse command like this:

PHP Code:

CMD:createhouse(playerid, params[])
{
    new houseid = GetFreeHouseID();
    // rest of the command ...
    return 1;
}
stock GetFreeHouseID()
{
    for(new i; i < 50; i++)
    {
        new query[128], rows, fields;
        mysql_format(ConnectionHandle, query, sizeof(query),"SELECT * FROM houses WHERE houseid = %i", i);
        mysql_tquery(ConnectionHandle, query, "", "");
        cache_get_data(rows, fields, ConnectionHandle);
        if(!rows) return i;
    }
} 


[Vince]

The primary key mustn't have any meaning. It is merely there to identify the row in the table. In the house structure (probably the enum) you must create a variable that will hold this id. If you want to create a new house you do a normal insert. Then you use cache_insert_id() to assign the newly created id to said variable.

[Juvanii]

can you explain more please ?

[SecretBoss]

If you have A_I enabled on your INSERT query the house id will be increased automatically then you can retrieve it with a query or save it on a variable with cache_insert_id()

[Konstantinos]

The thing is that auto increment is not set to "houseid" column. There are more issues than that with the code itself:
- Executing 50 queries when 1 is enough (looping through the rows returned, there's rowid in cache_get_row(_int/_float) functions).
- Not specifying callback to retrieve the data afterwards.
- Calling cache functions out of the callback will result in warnings for no active cache.

Enable auto increment for "houseid" column and don't have it as variable[index] where index is the "houseid" from the database. Have a separate variable that will hold each "houseid" from the database so you can update according to that the correct row («here»).

As for the command: When first loading the houses, have a global variable that will store the houses loaded. In the command, check if the value of the variable is not equal to the max houses (reached limit) and assign the appropriate values to the array with index the value of the global variable that stores the number of loaded houses. When you do that, execute an INSERT query to insert a new record and specify a callback in which you'll retrieve the "houseid" from the database returned using cache_insert_id function as the above users already mentioned. Store it to the variable I mentioned above (look at «here» because I mentioned more).

[Juvanii]

Auto Increment will increase the houseid one by one.
Consider that i have 4 houses (0, 1, 2, 3) and i have deleted houseid 1 for example, when i create a new house using the command, it will assign houseid 4 instead of the missing id (1) .. i need something to get the missing id to assign it as the new houseid.

Konstantinos can you show me a simple code so i can get to the point ?
Thank you

[[cS]Owain]

http://www.codediesel.com/mysql/sequ...-mysql/#p26976