foreach update query sets same last value from array

Question

I have a foreach loop which completes multiple inserts.

if ($action == '2'){
    foreach(array_combine($test_results, $reference_intervals) as $test_result => $reference_interval){     
    $query = "INSERT INTO model_lab_test_results (model_lab_test_results_pk, case_fk, level, lab_test_fk, result, reference_interval, created, created_by) VALUES ('', '$case_pk', '$level', '$lab_test_pk', '$test_result', '$reference_interval', NOW(), '$author_pk')";
$result = mysql_query($query, $connection) or die(mysql_error());
$inserted_ids[] = mysql_insert_id();
}

Then I run another foreach loop updating each of the records inserted.

if($result){
foreach($lab_tests_pk as $lab_test_pk){
$query_update_results = "UPDATE model_lab_test_results SET lab_test_fk = '$lab_test_pk' WHERE model_lab_test_results_pk IN (" . implode(",", $inserted_ids) . ")";
$result_update_results = mysql_query($query_update_results, $connection) or die(mysql_error());
}

The problem is that each of the four inserted records in array $inserted_ids:

Array ( [0] => 153 [1] => 154 [2] => 155 [3] => 156 )

is updated with the same last value in the array $lab_tests_pk, ie 1776:

Array ( [0] => 2249 [1] => 1349 [2] => 1126 [3] => 1776 )

How can I get each inserted record to be updated with seperate values from $lab_tests_pk?

I know I should be using PDO...thanks...

@MartyWallace I know I should be using PDO...thanks

BlitZ
– BlitZ

2013-05-23 03:31:16 +00:00
Commented May 23, 2013 at 3:31 — BlitZ
– BlitZ, Commented May 23, 2013 at 3:31

BlitZ · Accepted Answer · 2013-05-23 03:33:33Z

3

You might need to use a key for $inserted_ids too. In you case you just overwrite all recods with each value. All for update to each one. So you only see the last. Try this:

foreach($lab_tests_pk as $key => $lab_test_pk){
$query_update_results = "UPDATE model_lab_test_results SET lab_test_fk = '{$lab_test_pk}' WHERE model_lab_test_results_pk = {$inserted_ids[$key]}";
$result_update_results = mysql_query($query_update_results, $connection) or die(mysql_error());
}

edited May 23, 2013 at 3:33

answered May 23, 2013 at 3:28

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alejandro Iván Over a year ago

+1 for being able to understand that horribly spaghetti code.

IlludiumPu36 Over a year ago

Thanks for helping CORRUPT...You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

BlitZ Over a year ago

@PeterBrowne in first version I have a typ0: $inserted_ids were $insert_ids :( Is current version causing error too?

IlludiumPu36 Over a year ago

You're a superstar CORRUPT! Many thanks for your quick solution and understanding about my horribly spaghetti code...

Collectives™ on Stack Overflow

foreach update query sets same last value from array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related