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: [EMAIL PROTECTED] AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| "ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
