mysql get free id - 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 get free id (/showthread.php?tid=302194)
+- 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 get free id (/showthread.php?tid=302194)
mysql get free id - Unknown123 - 07.12.2011
pawn Code:
mysql_query("INSERT INTO `rofl`(`SomeID`, `Name`) VALUES (LAST_INSERT_ID() + 1, 'TestNAME')");
SomeID = 1 | Name = TestNAME
so how to get free ID spot with mysql?
Re: mysql get free id - MdeRooy - 07.12.2011
Do you mean fill up a deleted row?
Re: mysql get free id - Unknown123 - 07.12.2011
Quote:
|
Originally Posted by MdeRooy
 Do you mean fill up a deleted row?
|
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
Re: mysql get free id - MdeRooy - 07.12.2011
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.
Re: mysql get free id - Unknown123 - 07.12.2011
no other way, i gonna use this for my ban system "BanID"
and one row get deleted if i unbann someone
Re: mysql get free id - MdeRooy - 07.12.2011
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?
Re: mysql get free id - [HiC]TheKiller - 07.12.2011
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 ..... ");
}
Re: mysql get free id - MdeRooy - 07.12.2011
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.