Re: Not quite relevant question about coding and query

On Fri, 19 Jan 2001, Jacky@lilst wrote: > Can I just write a query to retrive UserID right after I have inserted > user detail into Usertable? and then assign into a variable and use > that value to insert into UserID field in another table? > like this: > > $getID = "select userID from user"; > $resultID = mysql_query($getID); Others have explained to you why this will not work. Still others have suggested using: > $getID = "select MAX(userID) from user"; But that won't work either. If two or more people run your little insert/find last script at the same time, this might happen: User 1: does insert, max userid is now 10. User 2: does insert, max userid is now 11. User 1: does select to find last insert, gets 11. User 2: does select to find last insert, gets 11. Thus user 1 now has invalid data, and will corrupt your table if he does further inserts based on this information. The final suggestion you got: <?PHP $linkID = mysql_connect("somehost", "someuser", "somepass"); mysql_select_db("somedb", $linkID); $query = "insert into user (firstname, lastname, address, phone)". "values('firstname','lastname','address','phone')"; $resultinsert = mysql_query($query, $linkID); // If multiple inserts are done on multiple tables, just returns // the most recent one. $id = mysql_insert_id($linkID); ?> The above code will *always* return the value you want. Notice that I kept track of which mysql connection I made. This is *very* important. If you start using a utility that uses its own connection, or lose track of your connect/disconnect statements, you never know which connection you're using. The others who told you to use mysql_insert_id also had the syntax wrong. You can't specify which insert id you want back. You can only say that you want one. The function just returns the id of the last insert statement that took place for the mysql connection you specify. In other words, this can wreak havoc with persistant connections unless you're using the version of mysql that supports transactions. If you're not, you'll get the same hypothetical situation we ran into when using MAX to find the last insert. Still, this is the least dangerous of the two, and will always work if connections are not persistant. Remember, the documentation is your friend. Download it, and if you have *any* question about a function or family of functions, just go there. I found this by reading the mysql section of the documentation. Good luck, -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | "ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+