Getting the index to use it later

Databases
1

Good day,

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 <-PK
Name
Address
Phone
E-mail

Child table: “Orders”
OrderID <-PK
Status
Total
CustomerID <-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!!!

2

use LAST_INSERT_ID to get the value of the auto increment value from the insert into the previous table.

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);
    echo $PurchaseID;

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

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

5

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


INSERT INTO customers
(
CustomerID,
Name,
Address,
Phone,
E-mail)
VALUES
('stooges','larry','curly','moe','shemp');

and then the second like this:



INSERT INTO orders
(
OrderID,
Status,
Total,
CustomerID)
VALUES
('foo','bar','qux',LAST_INSERT_ID());
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.

7

[QUOTE=itmitică;5140406]… like you do for new orders with old customers.[/QUOTE]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

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.

9

[QUOTE=itmitică;5140435]So it’s seems that I know what I’m talking about:[/quote]that remains to be seen, in this particular instance :wink:

[QUOTE=itmitică;5140435] 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.[/QUOTE]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?

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.

11

[QUOTE=itmitică;5140445]Everything. It’s guessing, not proper retrieving of relational data.[/QUOTE]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

12

Issues with:

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

13

[QUOTE=itmitică;5140450]Issues with:

  • concurrent inserts[/quote]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

[QUOTE=itmitică;5140450]- multiple inserts in one hit[/QUOTE]irrelevant, we are not adding more than one customer at a time

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. :wink: It seems LAST_INSERT_ID() has failed on some fellows, but if da’ manual states that it’s not possible…

<hr>

Really?! Or should I say:

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:

  1. You can use prepared statements to prevent sql injection (http://php.net/manual/en/security.database.sql-injection.php)
  2. 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)
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[0];
echo $PurchaseID;

or, as @SpacePhoenix; says, use [fphp]mysql_insert_id[/fphp]

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.
– http://dev.mysql.com/doc/refman/5.0/es/mysql-insert-id.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 :slight_smile:

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!

Also,

[QUOTE=itmitică;5140450]No need to inform MySQL.com sir, they already know: http://bugs.mysql.com/bug.php?id=34319.[/QUOTE]

That’s not even a bug. As it says further down:

Thank you for the bug report. Currently that is the expected behavior:

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

"Note

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.

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 :wink:

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)

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.