Next Autoindex

[Destramic]

just wondering how i could get the next Autoindex of a table?

thanks ricky

[Jessica]

get the highest one and add one?
$sql = "SELECT id FROM table ORDER BY id DESC LIMIT 1";

[Destramic]

nope that wouldnt work...would just be like using

[code]
mysql_num_rows() + 1;
[/code]

basically if there has been 10 entries to the database and you've deleted the 2 recent entries, then mysql_num_rows would return 8 which infact i would need it to return the next index which would be 11.

hope someone can help ?

thanks ricky

[Orio]

First- why do you need it? Maybe there's a simple way doing it, you just need to provide more information...

The best thing I can think of is to insert a new record, run [url=http://www.php.net/manual/en/function.mysql-insert-id.php]mysql_insert_id()[/url] and add one to the result and then delete the record.
But as I said- if you just want to know what will be the id of the next new record, you can simply insert it and then use mysql_insert_id(). It really depends on what you're asking for.

Orio.

[Destramic]

well i have a table called 'games'
------------------
game_id
name
icon (will be saved as /images/icons/"game_id".gif)
-----------------
so i need to get the next autoindex of the game before it has been inserted so i can insert the icon uri...?

thanks ricky

[ShogunWarrior]

Although it seems irritating I'd do an insert, get mysql_insert_id and then update the table with the icon URL.

[.josh]

you don't need to insert the number in auto_incrementing columns. just leave it blank and mysql will automatically fill it in. Hence the [i]auto[/i]-increment.

[ShogunWarrior]

Yes, but apparently he wants to name the image file with it's corresponding row, before he has inserted it.

[.josh]

well to be honest, I don't really see the point in that.  One of the points of having a database is to seperate data from identifiers.  That's why you have an id column in the first place.  adding on the id number to the image name and then putting it into the table that already has the id number associated with it is at best, redundant. 

[Orio]

Ok, I made a research and found the answer. Run this:

[code]<?php
$tbl_name = "games";

$result1 = mysql_query("SELECT MAX(game_id) AS id FROM `$tbl_name`");
$info1 = mysql_fetch_array($result1);
echo "Current last id: ".$info1['id']."<br>";

$result2 = mysql_query("SHOW TABLE STATUS LIKE '$tbl_name'");
$info2 = mysql_fetch_array($result2);
echo "Next id will be: ".$info2['Auto_increment'];

?>[/code]

Orio.

[Destramic]

thank you very much :D