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"CustomerID <-PKNameAddressPhoneE-mail

Child table: "Orders"OrderID <-PKStatusTotalCustomerID <-FK

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-21T19:32:37Z —
#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-22T04:46:44Z —
#3

Thanks a lot.

I applied your indication.Could you please check what is wrong in these instructions:

You don't even need to select it separately to use it. You can just do your first insert...

... 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-22T22:14:17Z —
#7

itmitică;5140406 said:

... like you do for new orders with old customers.

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-22T22:20:37Z —
#8

r937 said:

by definition, a new customer doesn't have a customerid yet, and post#1 is talking about a new customer

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.

Sir_Arcturua said:

I have a parent and a child tables.I load some data into the parent table [...][Then] I need to load some data into the child table [...]

How can I get the value of the parent table primary key, in order to use it to load data into the child table?

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-22T22:23:17Z —
#9

itmitică;5140435 said:

So it's seems that I know what I'm talking about:

that remains to be seen, in this particular instance

itmitică;5140435 said:

first insert a new customer. By doing that, it becomes an old customer. Then employ the mechanism normally used to insert orders for a customer.

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-22T22:32:04Z —
#10

r937 said:

if you insert a new customer, how will you know what its customerid is?

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.

r937 said:

the suggestion is to use LAST_INSERT_ID()

what specificially is wrong with that approach, please?

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-22T22:35:44Z —
#11

itmitică;5140445 said:

Everything. It's guessing, not proper retrieving of relational data.

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-22T22:45:45Z —
#12

r937 said:

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()

if you would kindly post a test case that proves it doesn't work, i'd be inclined to take you more seriously

itmitică;5140450 said:

multiple inserts in one hit

irrelevant, we are not adding more than one customer at a time

system
—
2012-06-22T23:00:17Z —
#14

r937 said:

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

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...

<hr>

r937 said:

irrelevant, we are not adding more than one customer at a time

Really?! Or should I say:

r937 said:

that remains to be seen, in this particular instance

SpacePhoenix
—
2012-06-23T06:05:53Z —
#15

Sir_Arcturua said:

Thanks a lot.

I applied your indication.Could you please check what is wrong in these instructions:

What I got was the string: "Resource id #6", instead of the number 11, the ID of the table last inserted row.

r937 said:

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

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)

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!

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-23T13:31:00Z —
#17

ScallioXTX said:

$sql_id = "SELECT LAST_INSERT_ID() FROM Purchases";

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-23T15:38:01Z —
#18

Sir_Arcturua said:

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 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)

but this:- 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-22T16:09:06Z —
#19

This topic is now archived. It is frozen and cannot be changed in any way.