how to retrieve last record when using 2 PK ?

[eluser]minoh[/eluser]
hi guys,

in my project i have table with 2 primary keys, after the insert i want to get the last inserted pk2.

any body have an idea how to do that using CI ?

[eluser]jedd[/eluser]
A couple of things.

Do you mean you have one primary key, but it's a compound key?

In general this is a bad idea - you should consider turning those each into indexed columns and having an id column as your PK.

It's also good to say what you've tried already, especially what hasn't worked. For example, what happens when you use $this->db->insert_id()? (I'm not optimistic that it'd provide anything useful, of course.)

[eluser]minoh[/eluser]
yes this is a compound key, i have two tables 'places' and 'equipements' and a x-y relation, so that why i'm using a compound key in the 'places_has_equipements'.

i thought about changing the both keys to indexed columns and adding a PK, but just if dont find a solution, because i'll have to change all my x-y relation tables.

[eluser]InsiteFX[/eluser]
From the MySQL Reference

Requires MySQL 5+

Code:

if ((result = mysql_store_result(&mysql;)) == 0 &&
    mysql_field_count(&mysql;) == 0 &&
    mysql_insert_id(&mysql;) != 0)
{
    used_id = mysql_insert_id(&mysql;);
}

You will need to convert it to CodeIgniter.

Enjoy
InsiteFX

[eluser]jedd[/eluser]
I'm not sure how expensive those calls are, so it might be easier / comparably fast to just have a function in your method, that you call at the end of your insert() function, that does a lookup for the data you just inserted. Because it's a PK, and consequently both columns are indexed (and you might get some benefit from the fact the disk was just there, at the file system level) it should be very fast.

A new thought, however, pops into my head.

If your PK is a compound key, and you just used the two components of that key to generate your INSERT query .. why don't you just use the two components that you presumably have to hand in your variables?

Usually you want to look up the auto-incremented ID column (ie. a non-predictable PK) for a row you've just inserted - but this obviously doesn't apply in your situation.

[eluser]minoh[/eluser]
[quote author="jedd" date="1254539150"]

A new thought, however, pops into my head.

If your PK is a compound key, and you just used the two components of that key to generate your INSERT query .. why don't you just use the two components that you presumably have to hand in your variables?[/quote]

you're right jedd i did'nt think about it, i was focus on only a model side solution. but it can be useful to figure out how to do it using CI.

thanks.