Is it possible to insert into two separate databases?

Is it possible to insert into two separate databases?

mfitportalmfitportal Posts: 15Questions: 6Answers: 0

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 49,733Questions: 1Answers: 7,345 Site admin

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

    Thanks,
    Allan

  • mfitportalmfitportal Posts: 15Questions: 6Answers: 0
    edited November 2018

    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.

  • allanallan Posts: 49,733Questions: 1Answers: 7,345 Site admin
    Answer ✓

    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

Sign In or Register to comment.