Finding Next Index Number

[Onloac]

Hello, I was wondering what the easiest way is to determine the next index number in a database table. Would I just call that last number and then add +1 or is there a specific function for it?

[RussellReal]

I'm not sure if you mean the LAST insert id, or the next one coming up..?

 

last insert id is simple: mysql_insert_id or you could get the next one up by following this website's instructions:

 

http://blog.jamiedoris.com/geek/560/

 

note I've never did the second one, only ever the first one, and I'm more than sure there is an easier way to get the next auto increment, but, I don't know that way so here this way is

[aschk]

It depends if you're doing the sequence yourself or relying on MySQL auto_increment to provide you with the number. If the latter, then you can retrieve this information only AFTER a new record has been inserted. There's no guarantee that by adding +1 to the last ID that that will be your next id.

[Maq]

Hello, I was wondering what the easiest way is to determine the next index number in a database table. Would I just call that last number and then add +1 or is there a specific function for it?

 

Adding 1 is not reliable because a deleted record could cause inaccuracy.  Instead you could do something like:

 

$query = mysql_query("SHOW TABLE STATUS LIKE 'table'");
$result = mysql_fetch_object($query);
echo $result->Auto_increment;

[Onloac]

Yeah it doesn't seem like there is an answer cause I've searched the web. Basically what I'm trying to do should be simple. I have two tables, article and article_relation. article relation contains two columns, articleID, and categoryID. Now when I update the article table with an article I want it to update the article_relation table as well. Only problem is the articleID isn't generated until the article is added. I could just pull the very last record that was added, but that could cause problems if more then one person posts at the same time. What do you thing the best solution is for my situation?

[PFMaBiSmAd]

Only problem is the articleID isn't generated until the article is added

Until the article is actually added, it does not exist as data in the database. Why are you messing around with trying to use information about data that has not yet been added to the database.

 

Add the article, then use any information about that data. If you must, insert a record with missing information and go back and UPDATE it once everything is known about it.

[Onloac]

Thats the thing. I'm trying to do that, I just trying to understand the best method. I'm adding the article to the article table, and right after that I'm trying to add rows to the article_relation table. I'm trying to add the articleID and categoryID. The article is added first, I'm just trying to figure out how to select that EXACT row and obtain the articleID. If I simply ask for the last row added that could cause problems if multiple articles are added by users at the same time. the articleID is really the only unique id for the article, so how would I determine which article to select?

[PFMaBiSmAd]

RussellReal already told you in the first reply - mysql_insert_id

[Onloac]

Ohhh! sorry I overlooked it. That's exactly what I wanted! Thanks guys!

[Onloac]

I've tried for the life of me to get this to work but it seems that mysql_insert_id() doesn't want to output a result. I'm guessing its not finding the last insert!?! It's hard for me to post the code because my CMS is running off code developed by the MyBB team. Basically I'm using the myBB files to connect to the database and they have coded it so instead of query its $db->query. Would this have something to do with the function not working? For example:

$db->query("SELECT * FROM table")

is how things work, rather then the regular query you seen on all the PHP sites. I understand they simply put the database information in a function, but could this effect what I'm trying to do or am I dealing with another type of problem?

[Maq]

That should have no effect one what you're trying to accomplish.  The style you see there is just an object oriented approach.  Most 3rd party applications have their own classes and methods to handle modifications and SQL to reduce the risk of you screwing something up.

 

I've tried for the life of me to get this to work but it seems that mysql_insert_id() doesn't want to output a result.

 

What and where is "this"?  I don't see any code, how are we supposed to help you?

[RussellReal]

mysql_insert_id will only output a number after you perform an insert operation in the same script...

 

want the ultimate last used? try this:

 

<?php
$q = mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 1");
$id = mysql_fetch_object($q);
$id = $id->id;
?>

[taquitosensei]

Don't do this

 
<?php
   $q = mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 1");
   $id = mysql_fetch_object($q);
   $id = $id->id;
?>

it's bad practice. If someone else adds a record in between you adding a record and retrieving the id you now have the wrong id.

you can use last_insert_id() like this

select last_insert_id() as lastid from table limit 1

 

 

 

[

[J.Daniels]

I've tried for the life of me to get this to work but it seems that mysql_insert_id() doesn't want to output a result. I'm guessing its not finding the last insert!?! It's hard for me to post the code because my CMS is running off code developed by the MyBB team. Basically I'm using the myBB files to connect to the database and they have coded it so instead of query its $db->query. Would this have something to do with the function not working? For example:

$db->query("SELECT * FROM table")

is how things work, rather then the regular query you seen on all the PHP sites. I understand they simply put the database information in a function, but could this effect what I'm trying to do or am I dealing with another type of problem?

 

As stated before, mysql_insert_id() only works after an INSERT.  So you should do something like this:

 

$db->query("INSERT INTO article (title, ... ) VALUES ('Title', ... )");
$lastID = mysql_last_id(); // You can also check the CMS's SQL class to see if they have a function to grab the last id
$db->query("INSERT INTO article_relation (articleId, categoryID) VALUES ($lastID, $catID)");

[RussellReal]

taquito, I'm not exactly sure what you're getting at but he WANTS the last ID.. lol so if sum1 adds a row to the database, I'm sure he'd want that row's id instead of the one before it.. lol

[taquitosensei]

taquito, I'm not exactly sure what you're getting at but he WANTS the last ID.. lol so if sum1 adds a row to the database, I'm sure he'd want that row's id instead of the one before it.. lol

 

Not according to this

 

I could just pull the very last record that was added, but that could cause problems if more then one person posts at the same time.

[RussellReal]

taquito, I'm not exactly sure what you're getting at but he WANTS the last ID.. lol so if sum1 adds a row to the database, I'm sure he'd want that row's id instead of the one before it.. lol

 

Not according to this

 

I could just pull the very last record that was added, but that could cause problems if more then one person posts at the same time.

 

according to this.. it wouldn't be smart to get the last ID either way.... just set the id field to auto increment..