Getting autoincrement primary key back from MySql

[Brendan Nolan]

I am working on a PHP/MySql project, and am putting data in that often will only have one unique piece of data - the autoincrement primary key... I then need to get that number and put it into another table... this is how I am currently doing it...

The table looks like this:

rowid | col1 | col2

And the code is like this...


$sql = "INSERT INTO some_table (col1,col2) VALUES ('rowdata1','rowdata2')";
$result = mysql_query($sql);

$sql = "SELECT rowid FROM some_table WHERE col1='rowdata1'";
$result = mysql_query($sql);
$rowid = mysql_result($result,"rowid");


But of course this fails when rowdata1 is the same in more than one column...

What I am asking is: Is there some way of asking MySql what the next autoincrement number is on a particular table.. OR some way for MySql to tell me what number was assigned to the row?

If so how?


Semi Related question... If I drop all of the rows from a table, how can I reset the autoincrement?

[Ribeyed]

hi,
i have never use mySQL but it is still only SQL. I have put 2 options you can try. So give them a try and see how you get on:

$sql = "SELECT rowid FROM some_table WHERE col1='rowdata1' AND col2='rowdata2'";
$result = mysql_query($sql);
$rowid = mysql_result($result,"rowid");

This should work because on the basis that there can only be 1 record where rowdata1 and rowdata2 toghter are unique and the is no other record that has the same values in rowdata1 and rowdata2. For example:

rowid col1 col2
1 eggs bread
2 milk bacon
3 bread eggs
4 bread milk
and so on......

but if this would happen:

rowid col1 col2
1 eggs bread
2 eggs bread
3 milk bacon
4 bread eggs
5 milk bacon
then you would need to do this:

$sql = "SELECT * FROM some_table ORDER BY rowid DESC";
$result = mysql_query($sql);
$rowid = mysql_result($result,"rowid");

This will put the last record entered to the top
Hope this helps, sorry for any Mysql errors

[Brendan Nolan]

Thanks, that is gunna work a treat! (the second one - because there may be some records only identified by the ID)

Hey why is it that everyone always uses breakfast items, or furniture in thier database examples?

Thanks agian!:)

[Ribeyed]

hi,
lol funny, i didn't know that, must watch out for that the next time:D Glad it worked