Update specific record after multiple insert - possibly using last_insert_id()

Question

I have a multiple insert (4 records) using a for loop. I then want to run an update query on the record that was inserted where pathway_allowed = 'y' (only one of the 4 would have this value). I guess last_insert_id() would be useful here but not sure how to use this to update that record inserted with the following:

$pathway_allowed = intval($_POST['allowed']);
$action = mysql_real_escape_string($_POST['actions']);

if(isset($_POST['submit'])){ 

$pathway_comment = array();
foreach($_POST['comment'] as $comment) {
    $pathway_comment[]= mysql_real_escape_string($comment);
}
for($i=0, $count = count($pathway_comment);$i<$count;$i++) {
    $comment = $pathway_comment[$i];
    $query = sprintf(
        "INSERT INTO pathway (             
           pathway_pk,
           case_fk,
           level,
           pathway_action_fk,
           pathway_allowed,
           comment
        ) VALUES (
           '',
           '$case_pk',
           '1',
           '$action',
           '%s',
           '$comment')", $pathway_allowed === $i ? 'y' : 'n');

       $result = mysql_query($query, $connection) or die(mysql_error());
}
if($result){
- SELECT the 4 records here...
}
}

Answer 1

mysql_connect extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: PDO and mysqli_connect()

If you are using PDO you could use this.

$dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');

and in the for loop you do something like this :

$stmt = $dbh->prepare("INSERT INTO test (name, email) VALUES(?,?)");
$tmt->execute( array('user', 'user@example.com'));
$inserted_ids[] = $dbh->lastInsertId(); 

after the loop you will have an array with the 4 inserted ids

Edit: You don't need to reselect values you have entered you can use this

IN FOR LOOP
{
    $stmt = $dbh->prepare($query = sprintf("INSERT INTO pathway (pathway_pk,case_fk,level,pathway_action_fk,pathway_allowed, comment)
    VALUES (
       '',
       '$case_pk',
       '1',
       '$action',
       '%s',
       '$comment')", $pathway_allowed === $i ? 'y' : 'n'););

        // change the values to bindParam 

    $stmt->execute( array('user', 'user@example.com'));
    $data[$i]['pathway_pk'] = $dbh->lastInsertId(); 
    $data[$i]['case_fk'] = $case_pk;
    $data[$i]['level'] = 1;
    $data[$i]['pathway_action_fk'] = $action;
    $data[$i]['pathway_allowed'] = %s;
    $data[$i]['comment'] = $comment;

}

then

foreach($data as $d){

    echo $d['pathway_pk'];
    echo $d['case_fk'];


}

Answer 2

In each loop iteration, store the mysql_insert_id() onto an array, which you can use later to select the new records. Note, I'm replacing your incremental for loop with a tidier foreach here:

// Initialize arrays for later
// one for the new ids
$inserted_ids = array();
// one to keep comments from failed queries
$failed_comments = array();
// and one for the MySQL errors of failed queries
$errors = array();

foreach ($pathway_comment as $comment) {
    $query = sprintf(
        "INSERT INTO pathway (             
           pathway_pk,
           case_fk,
           level,
           pathway_action_fk,
           pathway_allowed,
           comment
        ) VALUES (
           '',
           '$case_pk',
           '1',
           '$action',
           '%s',
           '$comment')", $pathway_allowed === $i ? 'y' : 'n');

       $result = mysql_query($query, $connection);

       // If the iteration was successful, save the insert id onto an array
       // but only if $pathway_allowed == 'y'
       if ($result) {
           if ($pathway_allowed == 'y') {
               $inserted_ids[] = mysql_insert_id();        
           }
       }
       // Otherwise, keep an array of comments that failed
       // Maybe useful later if you want to print those that failed
       else {
           $failed_comments[] = $comment;
           // And keep the mysql_error() as well.
           $errors[] = mysql_error();
       }
}
// Loop is done, now you can implode() the inserted ids array:
// These are all ints from an auto_increment PK, so no additional escaping needed
// Execute the query than do whatever you need with the newly inserted rows.
$query = "SELECT * FROM pathway WHERE pathway_pk IN (" . implode(",", $inserted_ids) . ")";

You've likely heard this before, but in the long run, consider switching to an API which supports prepared statements, like MySQLi or PDO. You have done all the necessary escaping and integer casting here, but prepared statements can be faster if compiled and executed in a loop, in addition to not requiring escaping.