How to use a look up table twice

ShaneBrennan

I have a risk assessment screen using editor that works fine - a hazard description then 2 pull downs to select the severity and likelihood of something happening, and a description of the "control Measures" that can be used to reduce risk - that works fine.

However, I now need to add the expected severity and likelihood after the control measures have been carried out to show what the new Likelihood and Severity will be. Obviously I need to use the same two tables I have currently being using.... but I get an error saying:

"DataTables warning: table id=example4 - SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The objects "tblHandSRAInjurySeverityLabels" and "tblHandSRAInjurySeverityLabels" in the FROM clause have the same exposed names. Use correlation names to distinguish them "

Here is my code, I'm sure the solution is simple, but brain not working at the moment - thank you in advance for any help given:

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../../php/DataTables.php" );

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

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'tblHealthAndSafetyRiskAssessmentHazards' )
    ->field(
      Field::inst( 'tblHealthAndSafetyRiskAssessmentHazards.id' ),
       Field::inst( 'tblHealthAndSafetyRiskAssessmentHazards.hazard' )
         ->validator( 'Validate::notEmpty', array(
            'message' => 'This is a required field.') )
         ->validator( 'Validate::unique', array(
            'message' => 'This Hazard already exists, please try again.')),
      Field::inst( 'tblHandSRAInjurySeverityLabels.label' ),
      Field::inst( 'tblHandSRALikelihoodLabels.label' ),
      Field::inst( 'tblHealthAndSafetyRiskAssessmentHazards.injurySeverity' )
         ->options(Options::inst()
            ->table( 'tblHandSRAInjurySeverityLabels')
            ->value( 'value' )
            ->label( 'label' )
            ->order( 'value asc')
         )
         ->validator( 'Validate::dbValues' ),
      Field::inst( 'tblHealthAndSafetyRiskAssessmentHazards.likelihood' )
         ->options(Options::inst()
            ->table( 'tblHandSRALikelihoodLabels')
            ->value( 'value' )
            ->label( 'label' )
            ->order( 'value asc')
         )
         ->validator( 'Validate::dbValues' ),
      Field::inst( 'tblHealthAndSafetyRiskAssessmentHazards.controlMeasures' ),
      Field::inst( 'tblHealthAndSafetyRiskAssessmentHazards.cmInjurySeverity' )
         ->options(Options::inst()
            ->table( 'tblHandSRAInjurySeverityLabels')
            ->value( 'value' )
            ->label( 'label' )
            ->order( 'value asc')
         )
         ->validator( 'Validate::dbValues' ),
      Field::inst( 'tblHealthAndSafetyRiskAssessmentHazards.cmLikelihood' )
         ->options(Options::inst()
            ->table( 'tblHandSRALikelihoodLabels')
            ->value( 'value' )
            ->label( 'label' )
            ->order( 'value asc')
         )
         ->validator( 'Validate::dbValues' )
                  
    )
   ->leftjoin( 'tblHandSRAInjurySeverityLabels', 'tblHandSRAInjurySeverityLabels.value', '=', 'tblHealthAndSafetyRiskAssessmentHazards.injurySeverity' )
   ->leftjoin( 'tblHandSRALikelihoodLabels', 'tblHandSRALikelihoodLabels.value', '=', 'tblHealthAndSafetyRiskAssessmentHazards.likelihood' )
   ->leftjoin( 'tblHandSRAInjurySeverityLabels', 'tblHandSRAInjurySeverityLabels.value', '=', 'tblHealthAndSafetyRiskAssessmentHazards.cmInjurySeverity' )
   ->leftjoin( 'tblHandSRALikelihoodLabels', 'tblHandSRALikelihoodLabels.value', '=', 'tblHealthAndSafetyRiskAssessmentHazards.cmLikelihood' )
   
    ->process( $_POST )
    ->json();

allan

You need to use an alias:

->leftjoin( 'tblHandSRAInjurySeverityLabels as tblH1', 'tblH1.value', '=', 'tblHealthAndSafetyRiskAssessmentHazards.injurySeverity' )

and also refer to the join in the Field::inst for it as tblH1 (or whatever you choose to call it). Likewise alias the second call to that table.

Allan

ShaneBrennan

Great got it retrieving the data now - works great... just need to get the datatable and edit table working with the 3 extra fields now. Thanks Allan