Atirag Atirag - 1 year ago 70
MySQL Question

Php: fetching last inserted row on mysql table to use for another mysql statement

I need to fetch the id of the last inserted row from a mysql table using php and use that id to enter a new entry on another table. I have this until now

$inductionmethod = $_POST['inductionmethod'];
$injectionmethod = $_POST['injectionmethod'];
$dosage = $_POST['dosage'];
$metric = $_POST['metric'];
$notes = $_POST['notes'];

global $db_usr;

$query = "SELECT MAX( experiment_id ) FROM experiment";
$prep = $db_usr->prepare($query);
$lastid = $prep->fetch();

$query ="INSERT INTO experiment_using_methods (experiment_id, induction_method, injection_method, dosage_quantity, dosage_unit, dosage_qualitative)
'".$lastid['MAX( experiment_id )']."', # the fetched ID of the corresponding dataset
(SELECT induction_method_id FROM induction_method WHERE im_name = '".$inductionmethod."'), # name of induction method
(SELECT injection_method_id FROM injection_method WHERE im_name = '".$injectionmethod."'), # name of the injection method
'".floatval($dosage)."', # dosage quantity
'".$metric."', # dosage unit or metric
'".$notes."' # qualitative dosage - REMOVE??

$prep = $db_usr->prepare($query);

I think I'm getting an error while fetching the MAX( experiment_id) or maybe I'm using it incorrectly on the INSERT statement because if I replace the ".$lastid['MAX( experiment_id )']." part by a number the insert statement works fine. On the other hand I also test the SELECT MAX( experiment_id ) FROM experiment statement on the mysql command line and it also works fine. Am I using fetch and referencing the result value correctly?

Answer Source

Thing this is the main issue:

$prep = $db_usr->prepare($query);   
$lastid = $prep->fetch();

change it to:

$prep = $db_usr->prepare($query);   
$lastid = $prep->fetch();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download