PHP/MySQL Getting Variable before insert

[jay7981]

Hey all,

Im not sure if this is the correct place for this question, if not please moderator move it to where it belongs.

 

i am coding a form that will eventually insert the values into MySQL however i am stuck on how to get the next auto increment ID for a specific table.

 

here is the process,

User fills out form and clicks submit,

page transfers to a confirmation page (also a form) that displays the information that was $_POST

User then Clicks YES and the new form is sent to process the Insert.

 

this is where i am stuck, there is a table sm_admins that will set a id (auto inc) once the info is inserted, however i need to use this id in another table during the same insert, any clue how to do this? Also with deleting and adding records i dont belive that mysql_num_rows() will work because if i have 1, 2, 3, 4, 5 and i delete 3 the next row id will be 6 but mysql_num_rows() will say its 5.

 

i was thinking of some sort of (i know the code wont work but to get teh ideal) select * from sm_admins get last id then $next_avail_id = $lastid +1.... by doing this on the first form i could simply echo $next_avail_id into a hidden text field and once the form is submitted it will be fine... RIGHT?

[Goat]

I am not completely sure that I am following you, but to get the value of last insert id, you can use mysql_insert_id function. See http://php.net/manual/en/function.mysql-insert-id.php for details.

 

regards,

 

Goat

[jay7981]

that will only work "after" the inital insert, i need it before the insert. but thanks i may have use for that later...

[PFMaBiSmAd]

You can only get the actual value of an auto-increment field after you have inserted a row. Insert the rows that make use of the value AFTER you execute the main query that establishes the auto-increment value.

 

This method is need to prevent concurrent INSERTS from using an out of date value. If this method does not seem to work for what you are doing, then you need to re-think the way you are trying to accomplish this task.

[Goat]

that will only work "after" the inital insert, i need it before the insert. but thanks i may have use for that later...

 

Are you sure that you can't set things up so that you insert things first then fetch id then insert that id where you need? I am not sure that there is a way to know for certain what will the next id be unless you actually insert next row (but you'll have to ask people who know more about mysql db than me). Even if you count rows you don't know what will the next id be (if you ,say, delete row with highest id, next one will be one higher by one).

 

I can't really conceive the situation where you need to have two tables and both have foreign keys pointing to another. You can always get IDs you need if you call them in order.

 

regards,

 

Goat

[sader]

mysql_query("INSERT INTO admins ...");
$admin_id = mysql_insert_id();

//other calculations or whatever
mysql_insert("INSERT INTO privlegies (admin_id) VALUES($admin_id)");

 

mysql_num_rows() wrks only for SELECT queries for other quries DELETE,UPDATE,INSERT use

mysql_affected_rows()

[jay7981]

could i not do something like this, then invoke the function as $admin_id?

as I've never been real good at using functions, if i was to try this method how would i define $admin_id as the result of this function?

 

<?php
function get_current_insert_id($smadmin_table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $smadmin_table"; 
    return mysql_num_rows(mysql_query($q)) + 1;
}
?>

[sader]

I think like so

function get_current_insert_id($smadmin_table)
{    
$q = "SELECT LAST_INSERT_ID() FROM $smadmin_table";     
$r = mysql_fetch_array(mysql_query($q));
return $r[0];
}

[jay7981]

ok so i figured it out with all of your help and some more googleing,

 

here is what i did,

<?php
$admin_id = get_current_insert_id($smadmin_table);

function get_current_insert_id($smadmin_table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $smadmin_table"; 
    return mysql_num_rows(mysql_query($q)) + 1;
}
echo "$admin_id";
?>

works beautifully!

[PFMaBiSmAd]

works beautifully!

Not when you have concurrent visitors performing database queries.

 

Modern interrupt driven multitasking operating systems cannot guarantee the order in which queries are executed against your database server.