sir_arcturua — 2012-06-21T15:04:51-04:00 — #1
Let's I have a parent and a child tables.
I load some data into the parent table, so a new record is generated with its primary key.
As the private key is auto increment, I do not know the value of this index.
Now, I need to load some data into the child table, where there is a foreing key linked to the parent table primary key.
How can I get the value of the parent table primary key, in order to use it to load fata into the child table?
Parent table: "Customers"
Child table: "Orders"
Summarizing, I need to load data into both tables, my problem is because there is a "CustomerID" field I need to know from the parent table to be able to load the child table, and I do not know how to get it.
If there is another easier way to do it, much better!!
Thanks a lot!!!
guelphdad — 2012-06-21T15:32:37-04:00 — #2
use LAST_INSERT_ID to get the value of the auto increment value from the insert into the previous table.
sir_arcturua — 2012-06-22T00:46:44-04:00 — #3
Thanks a lot.
I applied your indication.
Could you please check what is wrong in these instructions:
$sql_id = "SELECT LAST_INSERT_ID() FROM Purchases";
$PurchaseID = mysql_query($sql_id);
What I got was the string: "Resource id #6", instead of the number 11, the ID of the table last inserted row.
r937 — 2012-06-22T06:51:39-04:00 — #4
you should always test your query outside of php first
the "resource id" error message means your query didn't even run, and your query didn't run because it has a syntax error
guelphdad — 2012-06-22T16:09:20-04:00 — #5
You don't even need to select it separately to use it. You can just do your first insert:
INSERT INTO customers
and then the second like this:
INSERT INTO orders
system — 2012-06-22T16:48:19-04:00 — #6
... and probably fail to record it properly.
You should have means to get the CustomerID for new orders with new customers like you do for new orders with old customers.
r937 — 2012-06-22T18:14:17-04:00 — #7
retrieving old customers is done by customerid
by definition, a new customer doesn't have a customerid yet, and post#1 is talking about a new customer
system — 2012-06-22T18:20:37-04:00 — #8
By definition, a new customer always has an ID, otherwise it won't be called a customer in the first place, since it's not registered yet. New means recently registered, not unregistered, in database language. In PR language it may mean something else.
It's seems that I know what I'm talking about: first insert a new customer. By doing that, it becomes an old customer. Then employ the mechanisms normally used to insert orders for a customer.
The OP is trying to insert a new customer and its orders in one go. He can do that if he retrieves and saves the customer id in a variable.
But the idea of last inserted id has nothing to do with relational data and its integrity. It has everything to do with guessing.
r937 — 2012-06-22T18:23:17-04:00 — #9
that remains to be seen, in this particular instance
if you insert a new customer, how will you know what its customerid is?
the suggestion is to use LAST_INSERT_ID()
what specificially is wrong with that approach, please?
system — 2012-06-22T18:32:04-04:00 — #10
Here's a wild proposition: make a query and store the result in a variable? Like you probably do for older, already registered customers, at the moment of a new order.
Everything. It's guessing, not proper retrieving of relational data. It's sidestepping on what falsely seem like a particular case. The proper mechanisms are already in place, no need to build redundant ones.
r937 — 2012-06-22T18:35:44-04:00 — #11
that, sir, is an opinion -- perhaps a well-informed opinion, with some merit -- but it certainly does not give you the right to denigrate a proven technique and make it sound like people are wrong to use LAST_INSERT_ID()
perhaps you should register your opinion with mysql.com
meanwhile, we will continue to recommend a proven technique to mysql developers
system — 2012-06-22T18:45:45-04:00 — #12
- concurrent inserts
- multiple inserts in one hit
No need to inform MySQL.com sir, they already know: http://bugs.mysql.com/bug.php?id=34319.
r937 — 2012-06-22T18:48:26-04:00 — #13
guaranteed not possible, as stated in da manual
if you would kindly post a test case that proves it doesn't work, i'd be inclined to take you more seriously
irrelevant, we are not adding more than one customer at a time
system — 2012-06-22T19:00:17-04:00 — #14
Oh, "da' manual". Right. Sure. "da' manual" is never wrong. Anyway, what do I know, I'm a Oracle and PostgreSQL guy myself, but still, I can read about bugs with MySQL MyISAM or InnoDB server settings. It seems LAST_INSERT_ID() has failed on some fellows, but if da' manual states that it's not possible...
Really?! Or should I say:
spacephoenix — 2012-06-23T02:05:53-04:00 — #15
The returning of a resource id means that the query run fine, if the query had failed then false would have been returned.
To get the last inserted ID if you're using the mysql_ extension in php you need to use the mysql_insert_id() function to get the ID of the last INSERT query (use it before you fire off another INSERT query).
You might want to consider migrating from using the mysql_ extenstion to either the mysqli_ extenstion or PDO which:
- You can use prepared statements to prevent sql injection (http://php.net/manual/en/security.database.sql-injection.php)
- You can use transactions to prevent issues with data integrity if something goes wrong when processing an order (http://dev.mysql.com/doc/refman/5.0/en/commit.html)
scallioxtx — 2012-06-23T08:04:03-04:00 — #16
The problem with this is that mysql_query only returns a query resource, not the result of the query.
Instead you should do,
$sql_id = "SELECT LAST_INSERT_ID() FROM Purchases";
$res = mysql_query($sql_id);
$row = mysql_fetch_array($res);
$purchaseID = $row;
or, as @SpacePhoenix; says, use mysql_insert_id
Also note that it says in the manual
The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.
So concurrent inserts is not really an issue here; unless -maybe- you're using multiple threads that use the same connection, but then you'd have bigger fish to fry than the mysql_insert_id() function anyway
As for the multiple inserts, that's also a problem when you're not using last_insert_id(), so it's not an argument against using last_insert_id(). At most it's an argument not to use multiple inserts on a table with an AUTO_INCREMENT field if you need the generated ID values afterward because the behavior is undefined!
That's not even a bug. As it says further down:
Thank you for the bug report. Currently that is the expected behavior:
For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This allows multiple-row inserts to be reproduced correctly on other servers in a replication setup."
The "bug" reporter then goes on about the name of the function which incorrect, as opposed to the behavior of the function. He has a point, but your argument that last_insert_id() is flawed in MySQL is invalid; at least when based on this "bug" report.
r937 — 2012-06-23T09:31:00-04:00 — #17
i believe FROM Purchases is incorrect here
earlier in this thread, i thought it was invalid, and that the syntax error was the cause of the resource id, but i don't do php, so i got that wrong
it actually does work -- it returns the same value once for every row in the table!!
not exactly what is desired here, is it
system — 2012-06-23T11:38:01-04:00 — #18
If I read this correctly, you don't have yet code to cover new orders from old customers? You're starting up with the "new customer - new order" scenario?
If so, I believe you should cover the "old customer - new order" scenario first. Then, in case of new customers, you only have to register the new customer data first, then call the mechanism in place for "old customer - new order" scenario.
What I'm trying to say, in case of a new customer,
don't do this:
- call the procedure for new customer registration and then the procedure for "new customer - new order" registration (which is redundant to a "old customer - new order" procedure)
- call the procedure for new customer registration and then the unique procedure for "old customer - new order" registration
One could argue that using two redundant old/new procedures and a LAST_INSERT_ID call, it may save something for you. It's not true. You'd have to always do and remember the old/new customer check. And you'd have... two redundant procedures.
mittineague — 2014-09-22T12:09:06-04:00 — #19
This topic is now archived. It is frozen and cannot be changed in any way.