Re: [PHP] 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. ThomasINN 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]
RE: [PHP] Not quite relevant question about coding and query
$query = "insert into user values
('firstname','lastname','address','phone')";
$resultinsert = mysql_query($query);
$getID = "select userID from user"; // Will this get me the
// Id of the record I just inserted?
$resultID = mysql_query($getID);
No, it won't - it'll return every userID in the table.
If your userIDs are sequential, you can just select "MAX(userID)" instead
of userID.
Jason
--
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]
Re: [PHP] Not quite relevant question about coding and query
thanks for Jason:-) Lucky the ID is sequential. That was command I was
looking for.
cheers
Jack
[EMAIL PROTECTED]
"There is nothing more rewarding than reaching the goal you set for
yourself"
- Original Message -
From: Jason Murray [EMAIL PROTECTED]
To: 'Jacky@lilst' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, January 18, 2001 11:56 PM
Subject: RE: [PHP] Not quite relevant question about coding and query
$query = "insert into user values
('firstname','lastname','address','phone')";
$resultinsert = mysql_query($query);
$getID = "select userID from user"; // Will this get me the
// Id of the record I just inserted?
$resultID = mysql_query($getID);
No, it won't - it'll return every userID in the table.
If your userIDs are sequential, you can just select "MAX(userID)" instead
of userID.
Jason
--
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]
--
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]
RE: [PHP] Not quite relevant question about coding and query
-Original Message-
From: Jason Murray [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 18, 2001 9:57 PM
To: 'Jacky@lilst'; [EMAIL PROTECTED]
Subject: RE: [PHP] Not quite relevant question about coding and query
$query = "insert into user values
('firstname','lastname','address','phone')";
$resultinsert = mysql_query($query);
$getID = "select userID from user"; // Will this get me the
// Id of the record I just inserted?
$resultID = mysql_query($getID);
No, it won't - it'll return every userID in the table.
If your userIDs are sequential, you can just select "MAX(userID)" instead
of userID.
Jason
--
Im no expert...I am learning this stuff "as we speak"but shouldn't
mysql_insert_id() do it?
Cheers,
Dave
--
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]
Re: [PHP] Not quite relevant question about coding and query
Addressed to: "Jacky@lilst" [EMAIL PROTECTED]
"Jason Murray" [EMAIL PROTECTED]
[EMAIL PROTECTED]
** Reply to note from "Jacky@lilst" [EMAIL PROTECTED] Fri, 19 Jan 2001
13:15:13 -0600
What happens when more than one person updates the tables at nearly
the same time?
a - insert record 9
b - insert record 10
a - get max ID = 10
b - get max ID = 10
Something doesn't look right here
What you are looking for is mysql-insert-id()
Get the id generated from the previous INSERT operation
http://www.php.net/manual/en/html/function.mysql-insert-id.html
thanks for Jason:-) Lucky the ID is sequential. That was command I
was looking for. cheers Jack [EMAIL PROTECTED] "There is
nothing more rewarding than reaching the goal you set for yourself"
- Original Message - From: Jason Murray
[EMAIL PROTECTED] To: 'Jacky@lilst'
[EMAIL PROTECTED]; [EMAIL PROTECTED] Sent:
Thursday, January 18, 2001 11:56 PM Subject: RE: [PHP] Not quite
relevant question about coding and query
$query = "insert into user values
('firstname','lastname','address','phone')";
$resultinsert = mysql_query($query);
$getID = "select userID from user"; // Will this get me the
// Id of the record I just inserted?
$resultID = mysql_query($getID);
No, it won't - it'll return every userID in the table.
If your userIDs are sequential, you can just select "MAX(userID)" instead
of userID.
Rick Widmer
Internet Marketing Specialists
http://www.developersdesk.com
--
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]
Re: [PHP] Not quite relevant question about coding and query
So what i should really do is like this:
$anotherQuery = "select mysql_insert_id(UserID) from user";
$resultUserID = mysql_query($anotherQuery);
is that corerct?
Jack
[EMAIL PROTECTED]
"There is nothing more rewarding than reaching the goal you set for
yourself"
- Original Message -
From: [EMAIL PROTECTED]
To: Jacky@lilst [EMAIL PROTECTED]; Jason Murray
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, January 19, 2001 12:36 AM
Subject: Re: [PHP] Not quite relevant question about coding and query
Addressed to: "Jacky@lilst" [EMAIL PROTECTED]
"Jason Murray" [EMAIL PROTECTED]
[EMAIL PROTECTED]
** Reply to note from "Jacky@lilst" [EMAIL PROTECTED] Fri, 19
Jan 2001 13:15:13 -0600
What happens when more than one person updates the tables at nearly
the same time?
a - insert record 9
b - insert record 10
a - get max ID = 10
b - get max ID = 10
Something doesn't look right here
What you are looking for is mysql-insert-id()
Get the id generated from the previous INSERT operation
http://www.php.net/manual/en/html/function.mysql-insert-id.html
thanks for Jason:-) Lucky the ID is sequential. That was command I
was looking for. cheers Jack [EMAIL PROTECTED] "There is
nothing more rewarding than reaching the goal you set for yourself"
- Original Message - From: Jason Murray
[EMAIL PROTECTED] To: 'Jacky@lilst'
[EMAIL PROTECTED]; [EMAIL PROTECTED] Sent:
Thursday, January 18, 2001 11:56 PM Subject: RE: [PHP] Not quite
relevant question about coding and query
$query = "insert into user values
('firstname','lastname','address','phone')";
$resultinsert = mysql_query($query);
$getID = "select userID from user"; // Will this get me the
// Id of the record I just
inserted?
$resultID = mysql_query($getID);
No, it won't - it'll return every userID in the table.
If your userIDs are sequential, you can just select "MAX(userID)"
instead
of userID.
Rick Widmer
Internet Marketing Specialists
http://www.developersdesk.com
--
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]
Re: [PHP] Not quite relevant question about coding and query
Sorry, I think it should be like this:
$queryInsert = "insert into user values ('firstname','lastname','email')";
$resultinsert = mysql_query($queryInsert);
$UserIdLatest = mysql_insert_id(userId);
Is that correct?
Jack
[EMAIL PROTECTED]
"There is nothing more rewarding than reaching the goal you set for
yourself"
- Original Message -
From: [EMAIL PROTECTED]
To: Jacky@lilst [EMAIL PROTECTED]; Jason Murray
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, January 19, 2001 12:36 AM
Subject: Re: [PHP] Not quite relevant question about coding and query
Addressed to: "Jacky@lilst" [EMAIL PROTECTED]
"Jason Murray" [EMAIL PROTECTED]
[EMAIL PROTECTED]
** Reply to note from "Jacky@lilst" [EMAIL PROTECTED] Fri, 19
Jan 2001 13:15:13 -0600
What happens when more than one person updates the tables at nearly
the same time?
a - insert record 9
b - insert record 10
a - get max ID = 10
b - get max ID = 10
Something doesn't look right here
What you are looking for is mysql-insert-id()
Get the id generated from the previous INSERT operation
http://www.php.net/manual/en/html/function.mysql-insert-id.html
thanks for Jason:-) Lucky the ID is sequential. That was command I
was looking for. cheers Jack [EMAIL PROTECTED] "There is
nothing more rewarding than reaching the goal you set for yourself"
- Original Message - From: Jason Murray
[EMAIL PROTECTED] To: 'Jacky@lilst'
[EMAIL PROTECTED]; [EMAIL PROTECTED] Sent:
Thursday, January 18, 2001 11:56 PM Subject: RE: [PHP] Not quite
relevant question about coding and query
$query = "insert into user values
('firstname','lastname','address','phone')";
$resultinsert = mysql_query($query);
$getID = "select userID from user"; // Will this get me the
// Id of the record I just
inserted?
$resultID = mysql_query($getID);
No, it won't - it'll return every userID in the table.
If your userIDs are sequential, you can just select "MAX(userID)"
instead
of userID.
Rick Widmer
Internet Marketing Specialists
http://www.developersdesk.com
--
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]
Re: [PHP] Not quite relevant question about coding and query
Addressed to: "Jacky@lilst" [EMAIL PROTECTED]
[EMAIL PROTECTED]
** Reply to note from "Jacky@lilst" [EMAIL PROTECTED] Fri, 19 Jan 2001
14:00:02 -0600
Sorry, I think it should be like this:
$queryInsert = "insert into user values ('firstname','lastname','email')";
$resultinsert = mysql_query($queryInsert);
$UserIdLatest = mysql_insert_id(userId);
Is that correct?
Yes, that is the 'proper' way to find out the value of a auto_incremant
field. The SELECT MAX( userid ) method will work fine in testing, but
then you get to pull your hair out when it starts to fail under heavy
load.
You might have a bit of a problem with the values() clause of the
insert. That statement expects you to list all the entries in the
table, in the order they appear, unless you specify a list right after
the table name. I've given up on that syntax in favor of this one:
$QueryInsert = "INSERT INTO user SET " .
" firstname = '$FirstName', " .
" lastname = '$LastName', " .
" email = '$Email' );
It also works with update:
$QueryUpdate = "UPDATE user SET " .
" firstname = '$FirstName', " .
" lastname = '$LastName', " .
" email = '$Email' .
"WHERE userId = 1234 " );
I like the fact that you can list any fields in any order while leaving
out anything you want.
Rick Widmer
Internet Marketing Specialists
http://www.developersdesk.com
--
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]
