[SOLVED] Getting last id of the auto-increment key

[backyard]

I have two tables and I need to get the last id of the primary key in one of the tables. I did some searching and found a solution that works.

 

$sql=mysql_query("INSERT INTO (table) (column) VALUES ('test')") or die (mysql_error());
$insert_id=mysql_insert_id();
$num = $insert_id + 1;   //need to increase the last id by one to match new entry in other table

 

This works well. The downside is in order for this to work an actual value is filed into a new row of (table) after the last id found in (column) thus giving me a blank row. While this isn't too bad, I could imagine I'll end up with a bunch of empty rows in my table. Is there a way to prevent this from happening -- i.e., a bunch of empty rows?

[teng84]

you may declare in your db the not null or do the select that will find a certain id before doing any thing

[Wildbug]

You can also use the MySQL function LAST_INSERT_ID() if you run the second query after the one inserting into the auto incrementing table.

[ss32]

SELECT LAST_INSERT_ID() FROM table

[Wildbug]

SELECT LAST_INSERT_ID() FROM table

 

Eh, not quite meaningful, and the FROM clause is pointless.

 

More useful in an UPDATE context after an INSERT.  I.e., UPDATE table2 SET table1_id=LAST_INSERT_ID(), col2="something";

[teng84]

You can also use the MySQL function LAST_INSERT_ID() if you run the second query after the one inserting into the auto incrementing table.

 

whats the difference between the tread starter mysql_insert_id() and yours

is there any?

[backyard]

Thanks for the help. Maybe I should give more info on what I'm doing. I have some software an mp3 player that uses two tables where one stores the artist (table1) and the other stores the location of the music(table2) file. There's a column (matchart) in table2 that matches the artist with the music so matchart needs to lineup with the id of table1 thus I need to find the last_id of table1 and then increment this by one and insert this into matchart when the next person uploads music.

[AndyB]

??? ??? isn't the whole idea of relational databases to have relationships rather than near misses?  I fail to understand why you've chosen what seems to be such an abstruse approach.  Maybe someone else will understand ...

[redarrow]

basic setup for u.

users

------

user_id // int not null auto_increment pimary_key

user_name // varchar 50 not null

group_name // varchat 100 not null

date_added // int not null

logged_in // int not null

 

music

-----

music_id // int not null auto_increment pimary_key

user_id // int not nul

tune_name // varchar 50 not null

date_added // int not null

 

 

logged_on

---------

user_id // int not null auto_increment primark_kry

active // int not null

[backyard]

??? ??? isn't the whole idea of relational databases to have relationships rather than near misses?  I fail to understand why you've chosen what seems to be such an abstruse approach.  Maybe someone else will understand ...

Are you referring to my post or someone elses? I'm new to php so I could very well be using a hammer to drive in a screw  .

 

 

basic setup for u.

users

------

user_id // int not null auto_increment pimary_key

user_name // varchar 50 not null

group_name // varchat 100 not null

date_added // int not null

logged_in // int not null

 

music

-----

music_id // int not null auto_increment pimary_key

user_id // int not nul

tune_name // varchar 50 not null

date_added // int not null

 

 

logged_on

---------

user_id // int not null auto_increment primark_kry

active // int not null

 

So if I set all the tables columns to not null this'll save me from adding an extra row so in other words if just one column is not null I'll get the extra row? Some of the columns need to be null so could I just set a default value of something just to get around this?

[Wildbug]

You can also use the MySQL function LAST_INSERT_ID() if you run the second query after the one inserting into the auto incrementing table.

whats the difference between the tread starter mysql_insert_id() and yours

is there any?

 

Probably not.  I didn't fully understand what the OP was trying to do, but it looked a little like he was using the mysql_insert_id() for another query.  But if one were to run another query, it would save a line or two of PHP code simply to use LAST_INSERT_ID() in the next SQL query.  However, looking at it in the light of day, it's doesn't appear relevant to the original problem.

[soycharliente]

SELECT * FROM table ORDER BY id DESC LIMIT 1

[backyard]

Thanks charlie, that solved my problem.