Is it possible to insert into two separate databases?

mfitportal

Hello,
So I am working with an application in which a user enters a cohort name, along with a group name. Each cohort can hold multiple groups, which is linked in the database by a foreign key in the group table. I wan't to insert both the cohort name and group name in their respective tables. At the moment I only can get the cohort to insert and not the group. Is it possible to do this without making a separate file for it?

allan

Is this a one-to-one relationship? Can you show me your SQL schema?

Thanks,
Allan

mfitportal

Hi Allan,
sorry for the delayed response. Bellow is some of the stuff that could help you. As for the one-to-one relationship, I think no as a Cohort can be associated with multiple groups, but a group can only be associated with one cohort. Hopefully what I provided helps and if you need anything else let me know.

SQL Tables for Group and Cohort, note some items are test data

--
-- Table structure for table `study_cohort`
--

CREATE TABLE `study_cohort` (
  `ID` int(11) NOT NULL,
  `CohortName` varchar(255) NOT NULL,
  `IsQCed` int(11) NOT NULL DEFAULT '0',
  `IsDeleted` int(11) NOT NULL DEFAULT '0',
  `LastModifiedBy` int(11) NOT NULL,
  `LastModifiedOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `study_cohort`
--

INSERT INTO `study_cohort` (`ID`, `CohortName`, `IsQCed`, `IsDeleted`, `LastModifiedBy`, `LastModifiedOn`) VALUES
(1, 'TRY', 1, 1, 1, '2018-11-08 16:51:32'),
(2, 'dasf', 1, 0, 0, '2018-10-23 11:26:37'),
(6, 'wow', 1, 0, 1, '2018-11-08 15:00:43'),
(8, 'asdffre', 1, 1, 0, '2018-11-08 16:51:41'),
(13, 'dude', 1, 0, 1, '2018-11-13 13:27:22');

-- --------------------------------------------------------

--
-- Table structure for table `study_group`
--

CREATE TABLE `study_group` (
  `ID` int(11) NOT NULL,
  `Group_CohortID` int(11) NOT NULL,
  `GroupName` varchar(255) NOT NULL,
  `IsQCed` int(11) NOT NULL DEFAULT '0',
  `IsDeleted` int(11) NOT NULL DEFAULT '0',
  `LastModifiedBy` int(11) NOT NULL,
  `LastModifiedOn` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `study_group`
--

INSERT INTO `study_group` (`ID`, `Group_CohortID`, `GroupName`, `IsQCed`, `IsDeleted`, `LastModifiedBy`, `LastModifiedOn`) VALUES
(1, 1, 'EFT', 1, 0, 0, '2018-10-16 10:48:22'),
(2, 7, 'asomeperson', 1, 0, 0, '2018-11-06 13:13:19'),
(3, 1, 'asdfasd', 1, 0, 0, '2018-11-06 13:18:04'),
(4, 6, 'blobs', 1, 0, 0, '2018-11-08 14:55:48'),
(6, 13, 'werewerw', 1, 0, 0, '2018-11-13 09:22:48');

The Model that would insert into the two tabels

<?php
// DataTables PHP library
include('../config/DataTables.php');

// Alias Editor classes so they are easy to use
use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Join,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst($db, 'study_cohort')
  ->fields(
    //Field::inst('study_cohort.Cohort_GroupID')
    //  ->options('study_group', 'ID', 'GroupName'),
    Field::inst('study_cohort.ID')
      ->options('study_group g1', 'Group_CohortID', 'GroupName'),
      Field::inst('g1.GroupName'),

    Field::inst('g1.Group_CohortID'),
    Field::inst('g1.IsQCed'),
    Field::inst('g1.IsDeleted'),
    Field::inst('g1.LastModifiedBy'),

    Field::inst('study_cohort.CohortName')
      ->validator('Validate::notEmpty')
      ->validator('Validate::unique'),

    Field::inst('study_cohort.LastModifiedBy')
      ->options('users u1', 'ID', 'Username'),
      Field::inst('u1.Username'),

    Field::inst('study_cohort.IsQCed'),
    Field::inst('study_cohort.IsDeleted'),
    Field::inst('study_cohort.LastModifiedOn')
    )
  ->where('study_cohort.IsDeleted', 0)
  ->where('study_cohort.IsQCed', 0, '!=')
  ->where('g1.IsDeleted', 0)
  ->where('g1.IsQCed', 0, '!=')
  ->leftJoin('users as u1', 'u1.ID', '=', 'study_cohort.LastModifiedBy')
  ->leftJoin('study_group as g1', 'g1.Group_CohortID', '=', 'study_cohort.ID')
  ->process($_POST)
  ->json();

// $editor = Editor::inst($db, 'study_group')
//   ->fields(
//       Field::inst('study_group.GroupName'),
//       Field::inst('study_group.Group_CohortID'),
//       Field::inst('study_group.LastModifiedBy'),
//       Field::inst('study_group.IsDeleted'),
//       Field::inst('study_group.IsQCed')
//
//     )
 ?>`

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

If I've understood correctly (apologies if not!) this example I think will be of interest.

My understanding is that you want to show a table of groups, a table of study cohorts and have the ability to assign groups into each cohort. Is that correct?

If so, I would have a separate DataTable for maintaining the groups. I'd also have a separate "group" database table, since at the moment you might have duplicate information for the groups. Then study_group can be a trivial link table like in the example.

Regards,
Allan