MYSQL > Autoincrement-Problem
#1

How can i get the next ID that will be created in my Database for a autoincrement field?
Example:

id | name
1 | a
2 | b
3 | c

I delete 2 + 3
next ID would be 4

how do i get the ID 4 to use it in a string:
printf("The next ID is: 4, want to insert?",nextID);

Mysql from BlueG
Reply
#2

Hmm, save the last deleted ID in some variable and just add one to it to figure out next ID. It's just a quick thinking, check this example maybe:

pawn Код:
// When deleting for example ID 5
lastDeletedID = 5;
// Then somewhere else
printf("The next ID will be: %d, want to insert?", lastDeletedID + 1);
Edit:

I think you should add to my code to check if the deleted ID is highest or not.
Reply
#3

ok, but this only works during the server runing without any restart
when i restart, the id would be anyways id:4
but there is no deleted item everytime
Reply
#4

You can save last deleted ID somewhere (your database) I think. Also, check my edit in my first post, it's important.
Reply
#5

There is a built-in function in MySQL for this. Try something like:

pawn Код:
SELECT MAX(column_name) + 1 FROM `table_name`
Reply
#6

Like:

stock getLastID()
{
mysql_query(sqlConnection,"SELECT MAX(gID) + 1 FROM `gangzones`");
lastZoneGIDDatabase = cache_get_field_content_int(0,"MAX(gID)");
return 1;
}
Reply
#7

MySQL uses threaded queries so unfortunately you can't do that. You'll have to pass it through a callback and do it that way.

pawn Код:
mysql_query(sqlConnection, "SELECT MAX(gID) + 1 FROM `gangzones`", "onGetLastID");
pawn Код:
forward onGetLastID();
public onGetLastID() {
    lastZoneGIDDatabase = cache_get_row_int(0, 0);
}
Reply
#8

Quote:
Originally Posted by Emmet_
Посмотреть сообщение
MySQL uses threaded queries so unfortunately you can't do that. You'll have to pass it through a callback and do it that way.

pawn Код:
mysql_query(sqlConnection, "SELECT MAX(gID) + 1 FROM `gangzones`", "onGetLastID");
pawn Код:
forward onGetLastID();
public onGetLastID() {
    lastZoneGIDDatabase = cache_get_row_int(0, 0);
}
Not quite accurate because if a row is deleted, it will return the last existed gID + 1 and an INSERT statement would give a different value than that.
Reply


Forum Jump:


Users browsing this thread: 2 Guest(s)