Advice on whether to use self referencing join

michaelosolinski

Hi Allan,

As discussed in my original post, I have the table tblTariffs which contains the following fields:

tariffID
tariffName
rentalPeriodProfile
tarifftoCopy

When creating a new record I want the front end form to contain a select box which contains the name of each existing tariff as the label and the tariffID as the value and when the form is submitted, the tarifftoCopy field is populated with the appropriate id. I hope this make sense?

The overall aim here is that I have another table that contains tariff details and when the record above is created a trigger will be fired to copy a set of records that correspond to the value in tarifftoCopy.

Anyway, I think I may need to use a self referencing join here but am not quite sure and am a bit confused by the syntax in the example and how it relates to my requirements.

Below is my php in it's current state. Would it be possible to provide an example of how to implement what I need to do, ideally commenting what each part does so I understand how it works for the future. If you can also explain how I implement this in the Javascript that would be great.

I hope this is clear enough but let me know if I need to provide any more information

Kind Regards

Mike

[code]
<?php

/*
* Editor server script for DB table seasonalTariffs
* Automatically generated by http://editor.datatables.net/generator
*/

// DataTables PHP library
include( "lib/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\Validate;

$pkey = 'tariffID';

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'tblTariffs', $pkey )
->field(
Field::inst( 'tariffID' ),
Field::inst( 'tariffName' ),
Field::inst( 'rentalPeriodProfileID' ),
Field::inst( 'tarifftoCopy' )
)

->join(
Join::inst( 'tblRentalPeriodProfiles', 'object' )
// the first value is the parent id, the second is the child id
->join( 'rentalPeriodProfileID', 'profileID' )
->set ( false )
->field(
Field::inst( 'profileName' ),
Field::inst( 'profileID' )
->set ( false )
)

);



// The "process" method will handle data get, create, edit and delete
// requests from the client
$out = $editor
->process($_POST)
->data();


// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'department' select list and 'access' radio boxes
if ( !isset($_POST['action']) ) {
// Get Rental Period Profile Name details
$out['tblRentalPeriodProfiles'] = $db
->select( 'tblRentalPeriodProfiles', 'profileID as value, profileName as label' )
->fetchAll();





}

// Send it back to the client

echo json_encode( $out );





[/code]

allan

Hi,

I don't think you need a self referencing join at all here actually since you just want to be able to create a new record with a single field on the table - presumably an integer value. Where that value comes from doesn't really matter - so I'd start by removing your join().

The next step, it looks like you have already done - you've got the list of profiles being sent back to the client which you can use in your fnInitComplete callback to update the options for the `tarifftoCopy` field.

Unless I'm missing something, I think the simple approach here should work :-)

Allan

michaelosolinski

Hi Allan,

It may be me that is misunderstanding but it's not the rental period profiles that I want to populate the select box for tarifftoCopy with, it is the existing data in the tariffID and tariffName fields which are in the same table that I am trying to add a new row to with the label being the tariffName value and the value being tariffID

allan

No sorry, it was me not fully understanding.

I think you want to update the extra data you are getting from the database to something like:

[code]
$out['tariffs'] = $db
->select( 'tblTariffs', 'tariffID as value, tariffName as label' )
->fetchAll();
[/code]

Then you have the existing tariff information, and can populate the select list with that. Or have I get the wrong end of the stick again (its a little difficult to visualise :-) )?

Allan

michaelosolinski

W00t! That's working perfectly now, thanks! As you say above, it was much more simple than I was trying to make it but think I am starting to get the hang of this now :)

Cheers!

Mike

* one other quick question, I already know I am going to have questions on other points not related to this specific issue, is it preferable for me to create new threads for each individual issue or continue this one?

allan

Excellent to hear that works now!

To be honest, its entirely up to you! If you have multiple different questions, then it might be best to track them individually in different threads. But if you have overlapping questions, it might be best to track them in a single thread. Its really down to how you would prefer to organise the questions and answers yourself - I'll fit in!

Regards,
Allan