Page 1 of 1
Need to find current unique id
Posted: Thu Jul 09, 2009 6:28 am
by Addos
Is there anyway I can query a database to get the value of the next unique id?
I need to return the last ID value and add 1 to it for an insert into another table so for example if the database has the following automatically incremented id values.
1
2
3
5
8
I want to try and query the database to find the next value which could be 9 but if that row had been deleted then it would be 10 etc so I can’t rely on returning that the very last numeric value and add 1 to it as this would be inaccurate. So I need to query the database and check to see what would be the actual current unique ID value should a new row be added.
Was playing with this but it’s not going to be accurate.
Thanks for any help
Code: Select all
mysql_select_db($****, $****);
$query_GetUniqueId = "SELECT content_id FROM addos ORDER BY content_id DESC";
$GetUniqueId = mysql_query($query_GetUniqueId, $****) or die(mysql_error());
$row_GetUniqueId = mysql_fetch_assoc($GetUniqueId);
$totalRows_GetUniqueId = mysql_num_rows($GetUniqueId);
$uniqueID = $row_GetUniqueId['content_id'] + 1;
echo $uniqueID;
Re: Need to find current unique id
Posted: Thu Jul 09, 2009 7:48 am
by Eric!
Try this
Code: Select all
mysql_select_db($****, $****);
// Limit 1 will just return the last row after ordered by desc
$query_GetUniqueId = "SELECT content_id FROM addos ORDER BY content_id DESC LIMIT 1";
$GetUniqueId = mysql_query($query_GetUniqueId, $****) or die(mysql_error());
$row_GetUniqueId = mysql_fetch_assoc($GetUniqueId);
$uniqueID = $row_GetUniqueId['content_id'] + 1;
echo $uniqueID;
Re: Need to find current unique id
Posted: Thu Jul 09, 2009 8:24 am
by Addos
Thanks very much but unfortunately that doesn’t work and is actually the exact problem I’m having. At the moment the last unique id in the table is 28 but the next entry that would go into the database would have an id of 33 and what you suggest only returns 29.
I need to be able to predict what the next id should be before any entry goes into the database.
It’s like I want to look/query AUTO_INCREMENT=32 so that I can predict that the next id will be 32. Does that make sense?
Thanks again
Re: Need to find current unique id
Posted: Thu Jul 09, 2009 8:36 am
by onion2k
Don't try to guess it or calculate it.
[sql]SHOW TABLE STATUS WHERE `name` = 'tm_artist'[/sql]
That'll return a row with columns of things about the table. The "Auto_increment" column is the value you want. So..
Code: Select all
$result = mysql_query("SHOW TABLE STATUS WHERE `name` = 'tm_artist'");
$record = mysql_fetch_array($result);
$next_id = $record['Auto_increment'];
Re: Need to find current unique id
Posted: Thu Jul 09, 2009 10:38 am
by Addos
Thanks very much for this help. I had never heard of this query and I had to do a bit of research to understand and get it to work. I ended having to use "SHOW TABLE STATUS LIKE 'table name'" and this worked a treat.
Thanks again
Re: Need to find current unique id
Posted: Fri Jul 10, 2009 8:25 am
by Eric!
Sorry, I didn't understand the question. I thought you were just making your own id numbers.
This unique_id is the current total number of records (i.e all the fields)? How do you use this to help you? Is there some kind of search speedup trick?
Re: Need to find current unique id
Posted: Fri Jul 10, 2009 8:43 am
by Addos
No problem and appreciated the help.
Basically I have a form where I insert a new member. When this form is submitted it inserts his details into a table and obviously it creates a new unique id. I have another table that at the same time I need to insert location or locations that are related to this new member table so I wanted to insert into that ‘location’ table the id for the member table so I can reference this later in a query. Until the new member was actually submitted his unique id wouldn’t be created so I needed to find this id so that when I submitted his details both tables ‘member’ & ‘location’ were updated. The members id became a foreign id in the location table.
Hope this makes sense.
Re: Need to find current unique id
Posted: Fri Jul 10, 2009 9:00 am
by Eric!
The part I don't understand is why the number depends on the table structure. Couldn't it be any unique number as long as they are linked between tables with the same number?
Re: Need to find current unique id
Posted: Fri Jul 10, 2009 10:15 am
by Addos
Couldn't it be any unique number as long as they are linked between tables with the same number?
Absolutely but I just ended up trying to do it this way. I’m not very experienced and guess like so many scripts there are many ways to do this. In a way I was glad I chose this as I had never heard of SHOW TABLE STATUS LIKE so it was a nice thing to discover.
Ta
Re: Need to find current unique id
Posted: Fri Jul 10, 2009 10:34 am
by onion2k
If you're using it to link between tables you could have just used mysql_insert_id() after you've inserted the first record.
Re: Need to find current unique id
Posted: Fri Jul 10, 2009 10:42 am
by Addos
Ah! I see that now and thanks for the tip. Looks very interesting so I'll look into this now.
Thanks again.
Re: Need to find current unique id
Posted: Fri Jul 10, 2009 11:08 am
by andyhoneycutt
onion2k wrote:If you're using it to link between tables you could have just used mysql_insert_id() after you've inserted the first record.
Addos wrote:When this form is submitted it inserts his details into a table and obviously it creates a new unique id.
This is exactly the safest way to do this, especially if you make the call to mysql_insert_id() immediately following the original insert statement.
-Andy