[Resolved] Next ID

BizBoy

Im trying to get the next database ID

/ Get Current House Count /
$homes['count'] = @mysql_num_rows(mysql_query("SELECT * FROM homes_database", $homes_db));

$num = $homes['count'];
if ($num == 0) {
$num = "0";
}else{
$num = $num+1;
}

If theres ID0 and ID1 in the database it
Returns the same ID1.

Thank you.

bradgrafelman

$num = (int)$homes['count'] + 1;

...like that?

Drakla

WHen you say next database ID do you mean the ID of the next insert, or just how many rows there are in the database?

If it's just the number of rows you can do

SELECT count(*) FROM table;

you can find the value that the next insert with give by doing

SHOW TABLE STATUS like 'table';

then check the value of the Auto_increment column - but beware! A race condition is present because it might have already been changed by a different thread / user by the time you utilise it.

If you insert a row then [man]mysql_insert_id/man will tell you what it was.

bradgrafelman

Or, let MySQL figure this out. If this field is of type auto_increment, don't even insert into it

$query = "INSERT INTO MyTable (id, name, data) VALUES ( '', 'brad', 'is testing')";

Or, you can specifically tell it what the next id is:

$query = "INSERT INTO MyTable (id, name, data) VALUES ( MAX(id)+1, 'brad', 'is testing')";

BizBoy

If theres no data in the database is going to add 1

ID0 + 1 = ID1

I want it to return ID0 if theres no data there

$num = $homes['count'];
     if ($num < 0)
     {
        $num = 0;
     }
     else
     {
       $num = (int)$homes['count'] + 1 -1;
     }

Look's like its working so far...

BizBoy

I deleted ID0 and it all goes wrong.

BizBoy

Im trying to upload photos with the ID of the added data

ID2
Name: bob
Phone: 000-000-000
Photos:
ID -NUM
2-01.jpg
2-02.jpg
2-03.jpg
2-04.jpg
2-05.jpg

:glare:

bradgrafelman

Ok, is 'id' an AUTO_INCREMENT field in MySQL? Or is it just a plain INT?

BizBoy

Plain INT

bradgrafelman

Ok, so try this:

$query = mysql_query('SELECT MAX(id) FROM MyTable');
$next_id = mysql_result($query, 0);
if(empty($next_id)) $next_id = 0; else $next_id++;

BizBoy

tnx working now 😉

bradgrafelman

If you have received assistance & your problem is solved, PLEASE click the RESOLVED LINK @ the bottom of the page