Trying to do a multiple insert into 2 tables

12/22/2020 4:27 am | #1 Sweens

Hi Ray

I'm trying to insert new users into two tables, users and site_members.

I want the newly created users.UserID from the first insert behaviour to be inserted into the site_members.sm_user_id field - but it doesn't appear in the list of available fields.

(The insert single record behaviour creates a session variable for the field, so I can do it for adding records individually).

Any directions much appreciated.

Cheers
Chris

12/22/2020 7:40 am | #2 Ray BorduinWebAssist

If I'm understanding you correctly, this will have to be hand coded.

I see your queryobj and rsobj includes are too low on the page... move those up.

Then you can manually add code to the first multiple insert code to make it do two inserts like:

php:

<?php

if (isset($_POST["btnAdduser"]) || isset($_POST["btnAdduser_x"])) {  // updated manually

  $MultipleInsertConnection = $connEGM_i;

  $MultipleInsertTable = "users";

  $MultipleInsertRedirect = "2-users_MultiInsert.php?ins=success";

  $MultipleInsertCheck = "fmEmail";

  $MultipleInsertIdentifier = 'wamultiinsert_1';

  for ($mi=0; $mi<sizeof($_POST[$MultipleInsertIdentifier]); $mi++) {

    $MultipleInsertCount = $_POST[$MultipleInsertIdentifier][$mi];

    if (($MultipleInsertCheck == "" || (isset($_POST[$MultipleInsertCheck.'_'.$MultipleInsertCount]) && $_POST[$MultipleInsertCheck.'_'.$MultipleInsertCount]!=""))) {

      $InsertQuery = new WA_MySQLi_Query($MultipleInsertConnection);

      $InsertQuery->Action = "insert";

      $InsertQuery->Table = $MultipleInsertTable;

      $InsertQuery->bindColumn("UserEmail", "s", "".((isset($_POST["fmEmail"."_".$MultipleInsertCount]))?$_POST["fmEmail"."_".$MultipleInsertCount]:"")  ."", "WA_IGNORE");

      $InsertQuery->bindColumn("UserPassword", "s", "".((isset($_POST["pwd"."_".$MultipleInsertCount]))?$_POST["pwd"."_".$MultipleInsertCount]:"")  ."", "WA_IGNORE");

      $InsertQuery->bindColumn("UserFirstName", "s", "".((isset($_POST["firstname"."_".$MultipleInsertCount]))?$_POST["firstname"."_".$MultipleInsertCount]:"")  ."", "WA_IGNORE");

      $InsertQuery->bindColumn("UserLastName", "s", "".((isset($_POST["surname"."_".$MultipleInsertCount]))?$_POST["surname"."_".$MultipleInsertCount]:"")  ."", "WA_IGNORE");

      $InsertQuery->bindColumn("UserEmailVerified", "i", "1", "WA_IGNORE");

      $InsertQuery->execute();

        

        

      $InsertQuery2 = new WA_MySQLi_Query($MultipleInsertConnection);

      $InsertQuery2->Action = "insert";

      $InsertQuery2->Table = "site_members";

      $InsertQuery2->bindColumn("sm_site_id", "i", "".$InsertQuery->InsertID  ."", "WA_DEFAULT");

      $InsertQuery2->bindColumn("sm_site_user", "i", "".$_SESSION['SiteID']  ."", "WA_DEFAULT");

      $InsertQuery2->bindColumn("sm_userlevel", "i", "2", "WA_DEFAULT");

      $InsertQuery2->execute();

        

    }

  }

  if ($MultipleInsertRedirect) {

    if (function_exists("rel2abs")) $MultipleInsertRedirect = rel2abs($MultipleInsertRedirect,dirname(__FILE__));

    header("location: " . $MultipleInsertRedirect);

  }

}

?>

Then delete the second multiple insert entirely.

Did this help? Tips are appreciated...

12/22/2020 8:27 am | #3 Sweens

Hi Ray

Thank you so much - modified a field name and it works a treat!

Just need to add validation to the email address now.

Many thanks, once more, and best wishes for Christmas and beyond.

Cheers

Chris

3/15/2021 2:07 am | #4 Sweens

Hi Ray

I don't know if I should have done this first, but I'm having difficulty in not allowing duplicate values on the email address. When I go to add the behaviour, the two fields listed are wamultiinsert_1[] csv and wamultiinsert_1[] (array).

If I add either field as the one to check, records are inserted even if the email address already exists.

Cheers
Chris

3/15/2021 9:58 am | #5 Ray BorduinWebAssist

The multiple insert doesn't really have unique value validation options.

This is something that would have to be hand coded to do properly. I could help in a premier support ticket.

Did this help? Tips are appreciated...

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

Trying to do a multiple insert into 2 tables

12/22/2020 4:27 am | #1 Sweens