CakePHP: Saving HABTM data MySQL error

Question

I'm trying to build a form in a view that saves data to 3 different tables that all have a many to many relations in my MySQL database, and hasAndBelongsToMany relations in the CakePHP models. However I try to do this though, it doesn't work, it either only saves the data from the controller I'm working in, or gives me the following MySQL error:

Error: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`agenda`.`timetables_weekschedules`, CONSTRAINT `fk_weekschedules_has_timetables_timetables1` FOREIGN KEY (`timetable_id`) REFERENCES `timetables` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTIO)

This is the form in my View/Weekschedules/jeffrey.ctp (will be properly renamed when it's working):

<div class="form">
<?php echo $this->Form->create('Weekschedule'); ?>
    <fieldset>
        <legend><?php echo __('Create workschedule'); ?></legend>
    <?php
        echo "<h1>Period</h1>";
        echo $this->Form->input('Period.name');
        echo $this->Form->input('Period.description');
        echo $this->Form->input('Period.startdatetime');
        echo $this->Form->input('Period.enddatetime');
        echo "<h1>Weekschedule</h1>";
        echo $this->Form->input('Weekschedule.name');
        echo $this->Form->input('Weekschedule.oddeven');
        echo "<h1>Timetable</h1>";
        echo $this->Form->input('Timetable.weekday');
        echo $this->Form->input('Timetable.starttime');
        echo $this->Form->input('Timetable.endtime');
    ?>
    </fieldset>
<?php echo $this->Form->end(__('Submit')); ?>
</div>

When the form inputs are like this, it throws the MySQL error, and when I format the inputs that are not in this controller like Period.0.name it just only saves the Weekschedule data.

This is how the jeffrey action in Controller/WeekschedulesController.php currently looks like, I only set the Weekschedule id as a test because I want to create a new Weekschedule, but that also doesn't work. I also tried saveAll() and saveAssociated() instead of the save() function and tried 'deep'=>true, nothing works.

public function jeffrey() {
    $this->Weekschedule->recursive = 2;

    $this->request->data['Weekschedule']['id'] = 95;
    debug($this->request->data);

    if (!empty($this->request->data)) {
        $this->Weekschedule->save($this->request->data);
    }
}

My Weekschedule model is default CakePHP bake code:

<?php
App::uses('AppModel', 'Model');
/**
 * Weekschedule Model
 *
 * @property Period $Period
 * @property Timetable $Timetable
 */
class Weekschedule extends AppModel {

/**
 * Display field
 *
 * @var string
 */
    public $displayField = 'name';


    //The Associations below have been created with all possible keys, those that are not needed can be removed

/**
 * hasAndBelongsToMany associations
 *
 * @var array
 */
    public $hasAndBelongsToMany = array(
        'Period' => array(
            'className' => 'Period',
            'joinTable' => 'periods_weekschedules',
            'foreignKey' => 'weekschedule_id',
            'associationForeignKey' => 'period_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
        ),
        'Timetable' => array(
            'className' => 'Timetable',
            'joinTable' => 'timetables_weekschedules',
            'foreignKey' => 'weekschedule_id',
            'associationForeignKey' => 'timetable_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
        )
    );

}

And this is the line in my routes.php:

// Jeffrey's test page
Router::connect('/jeffrey', array('controller' => 'weekschedules', 'action' => 'jeffrey'));

I wanted to post a screenshot of my database diagram, but since I'm new here I can't post images, so I'll try to describe it:

There's a periods table with the following columns:

• id (INT)
• name (VARCHAR(45))
• description (TEXT)
• startdatetime (DATETIME)
• enddatetime (DATETIME)
• created (DATETIME)
• modified (DATETIME)

Then there's a periods_weekschedules join table with the following columns:

• period_id (INT)
• weekschedule_id (INT)

And the following foreign keys:

• fk_periods_has_weekschedules_periods1: periods_weekschedules.period_id -> periods.id
• fk_periods_has_weekschedules_weekschedules1: periods_weekschedules.weekschedule_id -> weekschedules.id

Then there's a weekschedules table with the following columns:

• id (INT)
• name (VARCHAR(45))
• orderNr (INT)
• oddeven (VARCHAR(45))
• created (DATETIME)
• modified (DATETIME)

Then there's a timetables_weekschedules join table with the following columns:

• weekschedule_id (INT)
• timetable_id (INT)

And the following foreign keys:

• fk_weekschedules_has_timetables_weekschedules1: timetables_weekschedules.weekschedule_id -> weekschedules.id
• fk_weekschedules_has_timetables_timetables1: timetables_weekschedules.timetable_id -> timetables.id

And finally a timetables table with the following columns:

• id (INT)
• weekday (INT)
• starttime (TIME)
• endtime (TIME)
• created (DATETIME)
• modified (DATETIME)

Since I'm a beginner with CakePHP, I hope this is enough information to help you help me, thanks in advance! Jeffrey

Answer 1

Its hard to debug such big relations without having the full code and testing it. But if everything fails you can manually sort the data from your

$this->request->data

and manually save the results for each model.

Also take a look at the very detailed guide: How to save HABTM data in CakePHP