[Unknown123]

pawn Code:

mysql_query("INSERT INTO `rofl`(`SomeID`, `Name`) VALUES (LAST_INSERT_ID() + 1, 'TestNAME')");

i want mysql to get a free spot in SomeID, if i do my code over here it retun this in table

SomeID = 1 | Name = TestNAME

so how to get free ID spot with mysql?

[MdeRooy]

Do you mean fill up a deleted row?

[Unknown123]

Quote:

Originally Posted by MdeRooy Do you mean fill up a deleted row?

no, something like this

in my table (id 5 missing);

SomeID = 1 | Name = TestNAME
SomeID = 2 | Name = TestNAME
SomeID = 3 | Name = TestNAME
SomeID = 4 | Name = TestNAME
SomeID = 6 | Name = TestNAME
SomeID = 7 | Name = TestNAME
SomeID = 8 | Name = TestNAME
SomeID = 9 | Name = TestNAME

but when i run this code
mysql_query("INSERT INTO `rofl`(`SomeID`, `Name`) VALUES (LAST_INSERT_ID() + 1, 'TestNAME')");
it is supposed to fill the free row (id 5) but it dont

[MdeRooy]

What i do, is not delete one of the rows, but mark one of them as 'inactive'. Like make an extra column called "Active". If you delete one, put 0 as active value. Then if you load them, don't load the ones with Active = 0.

That way, when creating, you just search for Active = 0, with limit 1. If it finds one, UPDATE it with your new values and mark it with Active = 1 again. If there are no inactive rows, just INSERT a new one.

[Unknown123]

no other way, i gonna use this for my ban system "BanID"

and one row get deleted if i unbann someone

[MdeRooy]

Why not make a table like this for bans?

+--- IP --- PlayerDBID --- AdminDBID --- Reason --- IssuedOn --- ExpiresOn ---+

So you don't need ID's, but just search on the players database ID or the IP?

[[HiC]TheKiller]

What you would probably do is something similar to what MdeRooy said. When you unban then, instead of deleting the line, mark it as inactive. Then you can just go find if there are any inactive bans first and fill them up.

pawn Code:

mysql_query("SELECT id FROM tablename WHERE inactive = 1 ORDER BY id DESC LIMIT 1");
mysql_store_result();
if(mysql_num_rows() == 1)
{
    new query[200], id = mysql_fetch_int();
    mysql_free_result();
    format(query, sizeof(query), "UPDATE ..... WHERE id = %d", id);
    mysql_query(query);
}

else
{
    mysql_query("INSERT ..... ");
}

[MdeRooy]

Very important in TheKillers' solution, make sure you add LIMIT 1 to the query. It makes sure you only get 1 row returned when looking for inacitve rows.