Search code examples
mysqlcakephpformhelper

Adding data to multiple tables in CakePHP

I'm working on an invoicing system, and I'm trying to add invoices in a single form. Right now I'm only getting the ability to add to my invoices table, not my invoices_works table. Here is what I'm working with. Sorry in advance for adding so much code:

My invoices table:

CREATE TABLE `invoices` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`commission` double NOT NULL DEFAULT '30',
`location_id` int(11) unsigned NOT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `location_id` (`location_id`),
CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

My invoices_works table:

CREATE TABLE `invoices_works` (
`invoice_id` int(11) unsigned NOT NULL,
`work_id` int(11) unsigned NOT NULL,
`count` int(11) NOT NULL DEFAULT '1',
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
KEY `invoice_id` (`invoice_id`),
KEY `work_id` (`work_id`),
CONSTRAINT `invoices_works_ibfk_2` FOREIGN KEY (`work_id`) REFERENCES `works` (`id`),
CONSTRAINT `invoices_works_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

My works table:

CREATE TABLE `works` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`cost` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

My Invoice Model (the HABTM, at least):

public $hasAndBelongsToMany = array(
        'Work' => array(
                    'className' => 'Work',
                    'joinTable' => 'invoices_works',
                    'foreignKey' => 'invoice_id',
                    'associationForeignKey' => 'work_id'
                )
        );

The method for adding in the Controller:

public function add() {
    if($this->request->is('post')) {
        $this->Invoice->create();
        if ($this->Invoice->saveAssociated($this->request->data)) {
            $this->Session->setFlash(_('The invoice has been saved.'), true);
            echo var_dump($this->data);
            $this->redirect(array('action' => 'index'));
        }
        else {
            $this->Session->setFlash(_('The invoice could not be saved.', true));
        }
    }
    $work = $this->Work->find('list');
    $location = $this->Location->find('list', array('conditions' => array('Location.id !=' => 0)));
    $this->set('works', $work);
    $this->set('locations', $location);
}

And finally, the View itself:

<?php

 echo $this->Form->create('Invoice');
echo "<fieldset>";
    echo $this->Form->input('location_id');
    echo $this->Form->input('commission', array('default' => 30));
echo "</fieldset>";
    //echo $this->Form->hidden('InvoiceWork.invoices_id', array('default' => 1));
echo "<fieldset>";
    echo $this->Form->input('InvoicesWork.work_id');
    echo $this->Form->input('InvoicesWork.count', array('default' => 1));
echo "</fieldset>";

 echo $this->Form->end('Save Invoice');

?>

It seems like I'm missing something, and I know it's going to be obvious once I find it.

Solution

  • After several hours of smashing my face against my keyboard, I've solved my issue. I ended up using separate add functions for the two tables.

    public function add() {
    if($this->request->is('post')) {
        $this->Invoice->create();
        if ($invoice = $this->Invoice->saveAll($this->request->data)) {
            $this->Session->setFlash(_('The invoice has been saved.'), true);
            if(!empty($invoice)) {
                $this->request->data['InvoicesWork']['invoice_id'] = $this->Invoice->id;

                $this->Invoice->InvoicesWork->save($this->request->data);
                echo "InvoicesWork save completed?<br/>";
                echo var_dump($this->request->data);
            }
            $this->redirect(array('action' => 'index'));
        }
        else {
            $this->Session->setFlash(_('The invoice could not be saved.', true));
        }
    }
    $work = $this->Work->find('list');
    $location = $this->Location->find('list', array('conditions' => array('Location.id !=' => 0)));
    $this->set('works', $work);
    $this->set('locations', $location);
}

    It's not the most elegant solution, but after I insert data into my invoices table, I grab the new id from it and store it in my array. Then, I just call the add function from the InvoicesWork Model.