Insert Query Inside foreach Loop ...

[LesMizzell]

I'm trying to add data to a table from the returned results from different tables...
Basically, a number of partners "GETPRTNR", need coverage rates set for each of 5 coverage types "getR". 
The reason I can't use a simple join for this is that some partners have individual rates that are different from everybody else and each can be edited individually, so this is a one time query to set up the new rates/partners table to allow for this. Once it's run successfully once, it won't be needed again.

1. The echo portion of the loop below is showing all the correct data, so I know my two SELECT queries are working properly.
2. My INSERT query, also works correctly if it's NOT inside the loop. However, it will NOT do the insert inside the loop. I've been playing with this for awhile. I'm stumped.
 

// GET THE PARTNERS
$GETPRTNR = $pdo->query('SELECT prtnrID, prtnrNAME FROM enr_partners ORDER by prtnrNAME');

// Get the RATES
$getR = $pdo->prepare("SELECT * FROM rvsd_coverages WHERE id = :thisID"); 
$getR->bindParam(':thisID', $x, PDO::PARAM_STR);

// UPDATE THE DATABASE
$sql = "INSERT INTO rvsd_ptnr_coverages ( covID ... blah blah ) VALUES ( :covID ... blah blah )";
$stmt = $pdo->prepare($sql);                                       
$stmt->bindParam(':covID', $gr1['id'], PDO::PARAM_STR);
$stmt-> ... blah blah blah 

foreach ($GETPRTNR as $row)
// while ($row = $GETPRTNR->fetch())
{
    echo "<h1>Partner: ".$row['prtnrNAME'].' - '.$row['prtnrID'] . "</h1>\n"; //THIS IS OUTPUTTING CORRECTLY
   // LOOP for each of the 5 coverages
   // 1 = Major Medical  |  2 = Plastic/Cosmetic  |  3 = Invasive  |  4 = Non-Invasive  |  5 = Bariatric
  
   for ($x = 1; $x <= 5; $x++) {
        echo "<br>The COVERAGE ID is: $x <br>"; // THIS IS DISPLAYING CORRECTLY TOO
        $getR->execute(); 
        $gr1 = $getR->fetch();
        echo $gr1['cov_type'].' - '.$gr1['parf_25']; // THIS IS ALSO DISPLAYONG CORRECTLY
        $stmt->execute(); 
        } 
   $x = 1;
}

Advise please??

[requinix]

What's the code with the INSERT inside the loop?

And something's not right here. Are you absolutely sure this code you posted is correct except that it doesn't insert data? Because you're querying enr_partners fine but you're not doing anything with the data returned. The road you're on has you inserting into rvsd_ptnr_coverages a copy of the 5 coverages for every row from enr_partners; if that query had 9 partners then you'd insert 9 identical copies of each of the 5 coverages.

How about this. What data from what table(s) needs to end up where in what other table?

[Barand]

If you didn't obfuscate with things like "SELECT *" and "blah blah" it might be easier to understand what's going on.

[LesMizzell]

requinix: That's actually correct. For each partner, I'm inserting a copy of each of the 5 rows of coverages.
Worse, there's around 50 different coverage rates for each coverage - so that's a lot of stuff for each partner.
The coverage rows are the DEFAULT rates for every partner. Once all this data is set up, then the client has to go back through each one, and edit an individual rate here and there, as most of them don't use all the default rates.

Once all the rates have been edited, it will all get used on the website as:
Select a PARTNER from the partners table ---> Select a coverage type ---> see the rates for that partner/coverage type. This part is handled by a multi-table view in the database.

rvsd_ptnr_coverages contains: prtnrID from the partner table, covID from the coverages table, plus all the rates for each coverage type.

If I run the code, it's displayinb each partner, partner ID, then then each of the first coverage rate column for each of the 5 coverages:
Partner ID - 100042
  The COVERAGE TYPE is: 1 
  Major Medical - 1071
  The COVERAGE TYPE is: 2 
  Plastic / Cosmetic - 514
  The COVERAGE TYPE is: 3 
  Invasive - 321
  The COVERAGE TYPE is: 4 
  Non-Invasive/Minimally Invasive - 433
  The COVERAGE TYPE is:: 5 
  Bariatric (WLS) - 246
…. and so on for every partner

[requinix]

Okay, yeah, so that code you have isn't right. It's not going along the lines you need it.

But to make sure I understand this: you need to do this one-time setup operation that creates a billion or so partner/coverage rows, where each partner gets the same copy of all the coverages? Each coverage of a type will be the same for all partners, but eventually someone will come in and change the data as needed.

Then you should be able to do this with a single query after all.

INSERT INTO table getting all the merged data (columns...)
SELECT partner ID, coverage ID, coverage rates...
FROM partner table, coverage table

MySQL will combine every row from the partners table with every row from the coverage table, which is normally really bad but this time is exactly what you want.

[LesMizzell]

Here's the full thing below.
If I run the insert query just inside the for ($x = 1; $x <= 5; $x++) { insert here } loop - it inserts the 5 needed records, plus whatever prtnrID I feed it just fine.
It's only when I put it back inside of the "foreach ($GETPRTNR as $row)" that is isn't inserting - and it's not giving an error either

 

// LOOP for each partner/agent in database

// GET THE PARTNERS
$GETPRTNR = $pdo->query('SELECT prtnrID, prtnrNAME FROM enr_partners ORDER by prtnrNAME');

// Get the RATES
$getR = $pdo->prepare("SELECT * FROM rvsd_coverages WHERE id = :thisID"); 
$getR->bindParam(':thisID', $x, PDO::PARAM_STR);

// UPDATE THE DATABASE
$sql = "INSERT INTO rvsd_ptnr_coverages (
          covID,
          prtnrID,
          cov_type,
          add_25,
          add_50,	
          add_75,	
          add_100,	
          rmp_25,	
          rmp_50,	
          rmp_75,	
          rmp_100,	
          amst_25,	
          amst_50,	
          amst_75,	
          amst_100,	
          failed_25,	
          failed_50,	
          failed_75,	
          failed_100,	
          amt_25,	
          amt_50,	
          amt_75,	
          amt_100,	
          aexp_25,	
          aexp_50,	
          aexp_75,	
          aexp_100,	
          dcare_25,	
          dcare_50,	
          dcare_75,	
          dcare_100,	
          rsm_25,	
          rsm_50,	
          rsm_75,	
          rsm_100,	
          pah_25,	
          pah_50,	
          pah_75,	
          pah_100,	
          emt_25,	
          emt_50,	
          emt_75,	
          emt_100,	
          emc,	
          trav_can,	
          trav_dly,	
          loss_bag,	
          parf_25,	
          parf_50,	
          parf_75,	
          parf_100,	
          tc_add_25,	
          tc_add_75,	
          tc_add_50,	
          tc_add_100,	
          tc_rmr_25,	
          tc_rmr_50,	
          tc_rmr_75,	
          tc_rmr_100,	
          tc_wce,	
          tc_emt,	
          tc_trav_can,	
          tc_trav_dly,	
          tc_loss_bag,	
          tc_fee_25,	
          tc_fee_50,	
          tc_fee_75,	
          tc_fee_100
          ) VALUES ( 
          :covID,
          :prtnrID,
          :cov_type,
          :add_25,	
          :add_50,	
          :add_75,	
          :add_100,	
          :rmp_25,	
          :rmp_50,
          :rmp_75,	
          :rmp_100,	
          :amst_25,	
          :amst_50,	
          :amst_75,	
          :amst_100,	
          :failed_25,	
          :failed_50,	
          :failed_75,	
          :failed_100,	
          :amt_25,	
          :amt_50,	
          :amt_75,	
          :amt_100,	
          :aexp_25,	
          :aexp_50,	
          :aexp_75,	
          :aexp_100,	
          :dcare_25,	
          :dcare_50,	
          :dcare_75,	
          :dcare_100,	
          :rsm_25,	
          :rsm_50,	
          :rsm_75,	
          :rsm_100,	
          :pah_25,	
          :pah_50,	
          :pah_75,	
          :pah_100,	
          :emt_25,	
          :emt_50,	
          :emt_75,	
          :emt_100,	
          :emc,	
          :trav_can,	
          :trav_dly,	
          :loss_bag,	
          :parf_25,	
          :parf_50,	
          :parf_75,	
          :parf_100,	
          :tc_add_25,	
          :tc_add_75,	
          :tc_add_50,	
          :tc_add_100,	
          :tc_rmr_25,	
          :tc_rmr_50,	
          :tc_rmr_75,	
          :tc_rmr_100,	
          :tc_wce,	
          :tc_emt,	
          :tc_trav_can,	
          :tc_trav_dly,	
          :tc_loss_bag,	
          :tc_fee_25,	
          :tc_fee_50,	
          :tc_fee_75,	
          :tc_fee_100 )";

$stmt = $pdo->prepare($sql);                                       
          $stmt->bindParam(':covID', $gr1['id'], PDO::PARAM_STR);
          $stmt->bindParam(':prtnrID', $row['prtnrID'], PDO::PARAM_STR); 
          $stmt->bindParam(':cov_type', $gr1['cov_type'], PDO::PARAM_STR);	
          $stmt->bindParam(':add_25', $gr1['add_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':add_50', $gr1['add_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':add_75', $gr1['add_75'], PDO::PARAM_STR);
          $stmt->bindParam(':add_100', $gr1['add_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':rmp_25', $gr1['rmp_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':rmp_50', $gr1['rmp_50'], PDO::PARAM_STR);
          $stmt->bindParam(':rmp_75', $gr1['rmp_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':rmp_100', $gr1['rmp_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':amst_25', $gr1['amst_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':amst_50', $gr1['amst_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':amst_75', $gr1['amst_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':amst_100', $gr1['amst_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':failed_25', $gr1['failed_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':failed_50', $gr1['failed_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':failed_75', $gr1['failed_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':failed_100', $gr1['failed_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':amt_25', $gr1['amt_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':amt_50', $gr1['amt_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':amt_75', $gr1['amt_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':amt_100', $gr1['amt_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':aexp_25', $gr1['aexp_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':aexp_50', $gr1['aexp_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':aexp_75', $gr1['aexp_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':aexp_100', $gr1['aexp_100'], PDO::PARAM_STR);
          $stmt->bindParam(':dcare_25', $gr1['dcare_25'], PDO::PARAM_STR);
          $stmt->bindParam(':dcare_50', $gr1['dcare_50'], PDO::PARAM_STR);
          $stmt->bindParam(':dcare_75', $gr1['dcare_75'], PDO::PARAM_STR);
          $stmt->bindParam(':dcare_100', $gr1['dcare_100'], PDO::PARAM_STR);
          $stmt->bindParam(':rsm_25', $gr1['rsm_25'], PDO::PARAM_STR);
          $stmt->bindParam(':rsm_50', $gr1['rsm_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':rsm_75', $gr1['rsm_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':rsm_100', $gr1['rsm_10'], PDO::PARAM_STR);	
          $stmt->bindParam(':pah_25', $gr1['pah_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':pah_50', $gr1['pah_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':pah_75', $gr1['pah_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':pah_100', $gr1['pah_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':emt_25', $gr1['emt_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':emt_50', $gr1['emt_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':emt_75', $gr1['emt_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':emt_100', $gr1['emt_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':emc', $gr1['emc'], PDO::PARAM_STR);	
          $stmt->bindParam(':trav_can', $gr1['trav_can'], PDO::PARAM_STR);	
          $stmt->bindParam(':trav_dly', $gr1['trav_dly'], PDO::PARAM_STR);	
          $stmt->bindParam(':loss_bag', $gr1['loss_bag'], PDO::PARAM_STR);	
          $stmt->bindParam(':parf_25', $gr1['parf_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':parf_50', $gr1['parf_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':parf_75', $gr1['parf_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':parf_100', $gr1['parf_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_add_25', $gr1['tc_add_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_add_75', $gr1['tc_add_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_add_50', $gr1['tc_add_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_add_100', $gr1['tc_add_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_rmr_25', $gr1['tc_rmr_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_rmr_50', $gr1['tc_rmr_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_rmr_75', $gr1['tc_rmr_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_rmr_100', $gr1['tc_rmr_100'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_wce', $gr1['tc_wce'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_emt', $gr1['tc_emt'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_trav_can', $gr1['tc_trav_can'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_trav_dly', $gr1['tc_trav_dly'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_loss_bag', $gr1['tc_loss_bag'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_fee_25', $gr1['tc_fee_25'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_fee_50', $gr1['tc_fee_50'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_fee_75', $gr1['tc_fee_75'], PDO::PARAM_STR);	
          $stmt->bindParam(':tc_fee_100', $gr1['tc_fee_100'], PDO::PARAM_STR);


foreach ($GETPRTNR as $row)
// while ($row = $GETPRTNR->fetch())
{
    echo "<h1>Partner: ".$row['prtnrNAME'].' - '.$row['prtnrID'] . "</h1>\n";

   // LOOP for each of the 5 coverages
   // 1 = Major Medical  |  2 = Plastic/Cosmetic  |  3 = Invasive  |  4 = Non-Invasive  |  5 = Bariatric
  
   for ($x = 1; $x <= 5; $x++) {
        echo "<br>The COVERAGE is: $x <br>";

        $getR->execute(); 
        $gr1 = $getR->fetch();

        echo $gr1['cov_type'].' - '.$gr1['parf_25'];   

        $stmt->execute(); 

        } 
  
   $x = 1;

}

 

[Barand]

That is a spreadsheet, NOT a relational database table. Try normalizing your data.

[benanamen]

I don't quite get whats going on here yet but something smells really bad. OP, how about focus on what the real problem is that needs to be solved rather than talking about your attempt at solving it. Sure smells like an XY problem and as the forum members here know, I have a very sensitive nose for it.

[LesMizzell]

It's about as normalized as I can get it. It's a LOT of data. I know that.

The process on the running application is:
1. You pick your PARTNER (two tables here - one with just partner name and ID, the other with the rest of the partner contact information and such)
2. You pick a MEDICAL PROCEDURE - procedure table has maybe 150 different type surgeries and such that are available.
3. Each procedure is assigned one of the 5 COVERAGE TYPES (another table)
4. It then uses the PROCEEDURE TYPE and PARTNER ID to look up the appropriate coverage rates from "rvsd_ptnr_coverages", which is displayed as a large grid on the site.
5. From the coverage grid, you select the level of coverage you want to apply for … and go from there.

Even if I pare this down to a test table with just a few fields (which I've tried), the issue is still that it happily do the insert outside of the loop for each loop, but not inside of it.

[benanamen]

Are you able to post an SQL dump of your current DB schema with a few sample records? You can also PM it. It would be very helpful to see what you are actually working with.

[LesMizzell]

Think I found it - it won't accept the binding the way I've got it:

$stmt->bindParam(':add_100', $gr1['add_100'], PDO::PARAM_STR);

It will accept this:

$add_100 = $gr1['add_100'];
$stmt->bindParam(':add_100', $add_100, PDO::PARAM_STR);

I was not aware I couldn't do it the first way ...

[Barand]

Why are you still messing around with param binding and individual inserts?

requinix has shown you exactly how to accomplish it simply with a single query.

[LesMizzell]

Finally got it to work by running the below query directly in the database per requinix. Thanks for assisting this poor idiot.

As to binding in future queries - you can't directly bind the results like?
$stmt->bindParam(':add_100', $gr1['add_100'], PDO::PARAM_STR);

 

  INSERT INTO rvsd_ptnr_coverages (
          covID,
          prtnrID,
          cov_type,
          add_25,
          add_50,	
          add_75,	
          add_100,	
          rmp_25,	
          rmp_50,	
          rmp_75,	
          rmp_100,	
          amst_25,	
          amst_50,	
          amst_75,	
          amst_100,	
          failed_25,	
          failed_50,	
          failed_75,	
          failed_100,	
          amt_25,	
          amt_50,	
          amt_75,	
          amt_100,	
          aexp_25,	
          aexp_50,	
          aexp_75,	
          aexp_100,	
          dcare_25,	
          dcare_50,	
          dcare_75,	
          dcare_100,	
          rsm_25,	
          rsm_50,	
          rsm_75,	
          rsm_100,	
          pah_25,	
          pah_50,	
          pah_75,	
          pah_100,	
          emt_25,	
          emt_50,	
          emt_75,	
          emt_100,	
          emc,	
          trav_can,	
          trav_dly,	
          loss_bag,	
          parf_25,	
          parf_50,	
          parf_75,	
          parf_100,	
          tc_add_25,	
          tc_add_75,	
          tc_add_50,	
          tc_add_100,	
          tc_rmr_25,	
          tc_rmr_50,	
          tc_rmr_75,	
          tc_rmr_100,	
          tc_wce,	
          tc_emt,	
          tc_trav_can,	
          tc_trav_dly,	
          tc_loss_bag,	
          tc_fee_25,	
          tc_fee_50,	
          tc_fee_75,	
          tc_fee_100 )
SELECT 
  rvsd_coverages.id,
  enr_partners.prtnrID,
  rvsd_coverages.cov_type,
  rvsd_coverages.add_25,
  rvsd_coverages.add_50,	
  rvsd_coverages.add_75,	
  rvsd_coverages.add_100,	
  rvsd_coverages.rmp_25,	
  rvsd_coverages.rmp_50,	
  rvsd_coverages.rmp_75,	
  rvsd_coverages.rmp_100,	
  rvsd_coverages.amst_25,	
  rvsd_coverages.amst_50,	
  rvsd_coverages.amst_75,	
  rvsd_coverages.amst_100,	
  rvsd_coverages.failed_25,	
  rvsd_coverages.failed_50,	
  rvsd_coverages.failed_75,	
  rvsd_coverages.failed_100,	
  rvsd_coverages.amt_25,	
  rvsd_coverages.amt_50,	
  rvsd_coverages.amt_75,	
  rvsd_coverages.amt_100,	
  rvsd_coverages.aexp_25,	
  rvsd_coverages.aexp_50,	
  rvsd_coverages.aexp_75,	
  rvsd_coverages.aexp_100,	
  rvsd_coverages.dcare_25,	
  rvsd_coverages.dcare_50,	
  rvsd_coverages.dcare_75,	
  rvsd_coverages.dcare_100,	
  rvsd_coverages.rsm_25,	
  rvsd_coverages.rsm_50,	
  rvsd_coverages.rsm_75,	
  rvsd_coverages.rsm_100,	
  rvsd_coverages.pah_25,	
  rvsd_coverages.pah_50,	
  rvsd_coverages.pah_75,	
  rvsd_coverages.pah_100,	
  rvsd_coverages.emt_25,	
  rvsd_coverages.emt_50,	
  rvsd_coverages.emt_75,	
  rvsd_coverages.emt_100,	
  rvsd_coverages.emc,	
  rvsd_coverages.trav_can,	
  rvsd_coverages.trav_dly,	
  rvsd_coverages.loss_bag,	
  rvsd_coverages.parf_25,	
  rvsd_coverages.parf_50,	
  rvsd_coverages.parf_75,	
  rvsd_coverages.parf_100,	
  rvsd_coverages.tc_add_25,	
  rvsd_coverages.tc_add_75,	
  rvsd_coverages.tc_add_50,	
  rvsd_coverages.tc_add_100,	
  rvsd_coverages.tc_rmr_25,	
  rvsd_coverages.tc_rmr_50,	
  rvsd_coverages.tc_rmr_75,	
  rvsd_coverages.tc_rmr_100,	
  rvsd_coverages.tc_wce,	
  rvsd_coverages.tc_emt,	
  rvsd_coverages.tc_trav_can,	
  rvsd_coverages.tc_trav_dly,	
  rvsd_coverages.tc_loss_bag,	
  rvsd_coverages.tc_fee_25,	
  rvsd_coverages.tc_fee_50,	
  rvsd_coverages.tc_fee_75,	
  rvsd_coverages.tc_fee_100 
FROM rvsd_coverages, enr_partners

 

[Barand]

1 hour ago, LesMizzell said:

As to binding in future queries - you can't directly bind the results like?
$stmt->bindParam(':add_100', $gr1['add_100'], PDO::PARAM_STR);

Strange that you should think that, because this worked perfectly for me

$arr = [ 'val1' => 25 ];

$stmt = $db->prepare("SELECT fname, lname FROM customer WHERE customer_id = :cust");
$stmt->bindParam(':cust', $arr['val1'], PDO::PARAM_INT);
$stmt->execute();

foreach ($stmt as $r) echo "{$r['fname']} {$r['lname']}<br>";

 

[Barand]

P.S.

... but only if the array is defined before binding the parameters. Thus this fails

$stmt = $db->prepare("INSERT INTO  customer (fname, lname) VALUES (:fname, :lname)");
$stmt->bindParam(':fname', $arr['val1'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $arr['val2'], PDO::PARAM_STR);

$arr = [ 'val1' => 'Basil', 'val2' => 'Rathbone' ];           // $arr defined AFTER binding

$stmt->execute();                                             // FAILS (inserts empty values)

Whe you use bindParam() the variable is passed "by reference". ie it uses the address of the variable. If the array doesn't yet exist, the address of the array element cannot be determined. When binding ordinary variables, an empty variable can be created (if it doesn't yet exist) and its address used.

[ginerjm]

For what it's worth, I never use the bind_param thing.  I simply create the array of parms and use it in the execute.

$parms = array('fname'=>$arr['val1'],  'lname'=>$arr['val2'])
$stmt->execute($parms);

 

Those two lines would be inside your loop after having done the prepare ahead of the loop.  Of course the $arr values have to be already defined at the point of the creation of the $parms array. 

Edited October 6, 2018 by ginerjm