MySQL badness

[Gayle29uk]

Okay, I've been a busy bunny for the last week putting together a webby for my galaxy (hence my absence) but I ran into one problem that I still haven't found an elegant solution to.

I want to insert data into table X with an incrementing primary key, I then want to insert data into table Y but using the primary key value from table X as an identifier. Did that make sense?

Right now I have to insert data into table X, select all entries by the current user, pick the highest id returned, and use that as the id for all fields entered into table Y. It works but it's ugly, kludgey, and generally horrible.

Any ideas on an elegant solution?

[Mong]

Tbh, that's the way I tend to deal with these things. I really need to look into this as it's bothering me too. I'll let you know...

M.

[Pitchfork]

Originally posted by Gayle29uk
Okay, I've been a busy bunny for the last week putting together a webby for my galaxy (hence my absence) but I ran into one problem that I still haven't found an elegant solution to.

I want to insert data into table X with an incrementing primary key, I then want to insert data into table Y but using the primary key value from table X as an identifier. Did that make sense?

Right now I have to insert data into table X, select all entries by the current user, pick the highest id returned, and use that as the id for all fields entered into table Y. It works but it's ugly, kludgey, and generally horrible.

Any ideas on an elegant solution?

I'm not quite sure, what you mean? Is the key from table X a foreign key in table Y? And what relation is that?

[Gayle29uk]

Originally posted by Pitchfork
I'm not quite sure, what you mean? Is the key from table X a foreign key in table Y? And what relation is that?

Ok, I'll try to be clearer.

Table X is a list of combat reports, each combat report has an ID number which is assigned by MySQL when the record is created and acts as the primary key.

Table Y is a list of elements that make up the combat report in X (ships present, roids taken etc). There is no primary key, each element has a non-unique id that identifies it as being a component of a combat report in table X.

Make sense now?

[Raging.Retard]

So its just a standard referential 1-many relationship then.

Just use the last_insert_id() function after you insert into the news table, to get the ID. Then use that ID to insert the entries. The call to insert the news report, and the ID would obviously need to be transaction locked in a concurrent environment (Unless each db connection is independant and not from a pool, ala the commands description below).

Btw, I looked the command up, case anyone shockingly thinks ive lowered myself to use MySQL.

MySQL has a built in function to return the value of the auto-increment column from the last insert on the connection. last_insert_id() will return the value of an auto_increment column from the last insert when the insert specifically referred to the column and inserted a null or 0 on the current connection, so other connections will not affect the returned value.

[queball]

http://www.php.net/manual/en/functio...-insert-id.php

http://www.mysql.com/doc/en/mysql_insert_id.html

LAST_INSERT_ID ?

[Pitchfork]

I am not sure about MySQL's foreign key behaviour, because I tend to use PostgreSQL, but from what I read here something like

CREATE TABLE X (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    something SOME_DATATYPE,
    PRIMARY KEY (id)
);

CREATE TABLE Y (
    something SOME_DATATYPE,
    x_id SMALLINT UNSIGNED NOT NULL REFERENCES X(id)
);

INSERT INTO X (something) VALUES ($something);

INSERT INTO Y (something, x_id) VALUES ($something, LAST_INSERT_ID());

should work with MySQL 3.23.44 and up using InnoDB storage engine.

[Gayle29uk]

You're all wonderful \o/

Needless to say you've helped to greatly clean up sections of my code

[Mong]

NO!

On a busy site, there's a small chance that the last ID will belong to another user, think about it!

M.

[Gayle29uk]

Originally posted by Mong
NO!

On a busy site, there's a small chance that the last ID will belong to another user, think about it!

M.

Apparently it's the last value issued on the current connection, not the last one issued globally.

[Pineapple]

the only problem youd have is if you used persistant connections (i presume)

[Pitchfork]

Originally posted by Mong
NO!

On a busy site, there's a small chance that the last ID will belong to another user, think about it!

M.

BEGIN;

<insert your insert-statements here>

COMMIT;

Should solve that.

[Raging.Retard]

Originally posted by Raging.Retard
The call to insert the news report, and the ID would obviously need to be transaction locked in a concurrent environment (Unless each db connection is independant and not from a pool

Originally posted by Mong
NO!

On a busy site, there's a small chance that the last ID will belong to another user, think about it!

M.

MONG IN NOT PAYING ATTENTION TO PREVIOUS POSTS SHOCKAH.

[CjC]

Not being a MySQL expert, I assume this is refering to the same kind of thing in MySQL as the @@IDENTITY function in T-SQL(MS SQL Server) - to recall the next Primary key ID from a table (in your case 'X') before its created? Just curious.

[queball]

Originally posted by Raging.Retard
Unless each db connection is independant and not from a pool

Or pooled and independent (from any other process), like the most common setup.

[Mong]

Originally posted by Raging.Retard
MONG IN NOT PAYING ATTENTION TO PREVIOUS POSTS SHOCKAH.

It doesn't happen much, treasure it, print it and frame it

M.