How to extract current Primary Key

[mpsn]

Hi, I am trying to build a function that takes in the current primary key as a parameter. My question is how do I extract it/retrieve it and store it in a variable?

 

Any help is much appreciated!

[Pikachu2000]

Are you talking about the ID of the last inserted record? mysql_insert_id

[mpsn]

I think so. Basically if I have four database tables, how do I specify to retrieve the most recently added primary key of a certain table?

[requinix]

If your code just inserted something then use mysql_insert_id(). That's just for your code which just did something.

 

If not, and you want to guess at the most recently used value from any query used in any code, you could assume that it's one short of the next auto_increment value. Do a SHOW TABLE STATUS LIKE table and grab the Auto_increment value...

[mpsn]

Thanks 

[mpsn]

It is working, BUT each time I reload/refresh the browser, the script increments this value. You see, for my assignment, I need other tables to reference this PK as a FK (primary key, foreign key). Is there a simpler/better way to reference a primary key as a foreign key for other tables in same database?

 

//get current Id of some table
function mysql_next_id($table) {
    $result = mysql_query('SHOW TABLE STATUS LIKE "'.$table.'"');
    $rows = mysql_fetch_assoc($result);
    return $rows['Auto_increment'];
}

$someId=mysql_next_id("document")-1;

 

 

[mpsn]

I was checking online and came across this useful tidbit:

 

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

 

My question is how can I take advantage of this query to only output the LAST entered primary key, b/c I want to store that as a foreign key for another table in the same database.

 

Please help a fellow out!

[Pikachu2000]

Then you need mysql_insert_id(). Insert the record into the table, get its PK id, then insert into the other tables using the value returned by mysql_insert_id() as the FK for those tables. If it keeps incrementing, you're doing something wrong, like allowing duplicate data insertions.

[mpsn]

No, I'm saying each time I reload the script through the browser, it inserts another duplicate record. And also, I WANT to store the last inserted primary key for a given database table b/c I want to pass it as parameter to a function.

 

Any help appreciated, thanks.

[Pikachu2000]

That's what I just said. You should code to prevent that from happening by defining UNIQUE indexes on your database table, or at least checking to see if the data already exists before trying to INSERT it. If it's inserting empty records, then you need to prevent the query from running unless the form has been submitted, and contains valid data.