The INSERT statement with SELECT in VALUES:
$stmt = $db->prepare("INSERT INTO weld_reference(weld_id,report_id) VALUES ((SELECT w.id FROM welds w WHERE w.weld_number=?),?)")
The below code loops through an array of user submited strings ($welds) and tries insert its ID into table weld_reference
if it exists in table welds
.
$stmt->bind_param("si",$weld_number,$_POST['report_id']); foreach($welds as $weld_number){ if($stmt->execute() and $stmt->num_rows){ ++$insert_count; } }
I don’t want to do another query to see if the row exists, I was hoping that I could get the number of rows or success of the INSERT after each $stmt->execute().
However, $stmt->affected_rows
, and $stmt->insert_id
always returns 1 and
$stmt->num_rows
always returns 0
whether a row was inserted or not.
How can I get a read on this and see if a row has been inserted to send an accurate feedback message to the user?
Advertisement
Answer
How do you check the value of $stmt->insert_id
? Because it should return you “Get the ID generated from the previous INSERT operation”. If ou only ($evaluate) it it will return true (as 1)
The way i do it is i store the id’s in an array so i can have a nice list of what has been inserted.
$stmt->bind_param("si",$weld_number,$_POST['report_id']); foreach($welds as $weld_number){ if($stmt->execute()){ $insertedId[]=$stmt->insert_id; } } echo count($insertedId);
Or since an id would logicaly never be empty you could do
$stmt->bind_param("si",$weld_number,$_POST['report_id']); foreach($welds as $weld_number){ if($stmt->execute() && !empty($stmt->insert_id)){ $insert_count++; // ++$insert_count; //unaware of this syntax -.- } }