Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've made this design similar to my problem, it's not exact but it close enough to understand my problem: http://i42.tinypic.com/bhbp5.jpg

Some cellular client frequently send their GPS position and the WLAN name that they have in range, sometimes only their position.

I've auto-generated timestamp and auto-incremental idModelGPS from ModelGPS table and I'm using javaDB to write to the DB.

I want to link the ModelGPS to an WLAN name where it happens, but I can't do it because the idModelGPS is autogenerated. I cannot access it to write in the ModelGPS_WLAN table.

I don't know how to handle this, maybe there is a design error or maybe there is a way to solve this problem that I'm unaware of. I'll be glad to hear your opinions.

share|improve this question
    
This sounds like you need a query more than expert level help, which would be the domain of Stack Overflow, but I'm having a hard time parsing your problem. Can you provide us with some sample table DDL, and maybe the query DML you're having troubles with? –  jcolebrand Feb 6 '12 at 16:24

1 Answer 1

up vote 4 down vote accepted

So you just need to fetch the last auto-increment value that was inserted? There are a couple of ways to do this, but they're all pretty simple.

Query: SELECT LAST_INSERT_ID()

php mysql: $id = mysql_insert_id($mysql_conn);
http://php.net/manual/en/function.mysql-insert-id.php

php mysqli: $id = $mysqli->insert_id;
http://php.net/manual/en/mysqli.insert-id.php

Both of those links have some example code that looks pretty easy to follow.

share|improve this answer
    
But this solution allows that other thread makes an insert after your insert and before your select and this can make you take an ID that is not this you want, that's the problem I have –  Rigal Feb 7 '12 at 9:18
    
The LAST_INSERT_ID() function is scoped to an individual connection/process. If connection A inserts a new auto-increment value, then connection B does the same, connection A will get the value that it inserted when evaluating LAST_INSERT_ID() (and likewise for connection B). As long as the connection retrieves that value immediately after inserting, you shouldn't have to worry about race conditions. –  db2 Feb 7 '12 at 13:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.