CakePHP: Add user input to record alongside data read from an infile

Question

I have a CakePHP application that stores "mission" data. Every time a new mission is created, a static input file is read and 592 new records are inserted into the 'missions' table. Basically, a single column is populated with permanent data. I've got that working just fine. The Model code looks like this:

public function importCSV($csv_name) { 
    $sql = "LOAD DATA INFILE '{$csv_name}' INTO TABLE missions
            LINES TERMINATED BY '\r'
            (or_identity)";

    $this->query($sql);
}

And here is the controller code:

public function add() {
    if($this->request->is('post')) {
        $this->Requirement->create();
        $csv_name = /path/to/csv/file
        if($this->Requirement->save($this->request->data)) {
            $this->Requirement->importCSV($csv_name);
            $this->Session->setFlash(__('The new mission has been created.'));
            return $this->redirect(array('action' => 'index'));
        } else {
            $this->Session->SetFlash(__('Unable to create mission.'));
        }
    }
}

...and user input from the View:

echo $this->Form->input('mission_id', array('type' => 'textbox', 'label' => 'Mission ID (Ex: OA-5)'));

When a user creates a new mission, they are required to enter a Mission ID, which will be something like "OA-5." This Mission ID is in its own column in the 'missions' table. What I can't figure out how to do is make sure that Mission ID is inserted along with the 592 new records. It needs to be there to form a concatenated key. How can I modify my methods to allow this?

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

Just insert it in the same SQL call. You can add additional fields to the query. See Add extra column of data when using LOAD DATA LOCAL INFILE for how to do it. For additional info check the manual.

Taken from the manual:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

edited May 23 '17 at 12:23

Community

1
1

answered Jan 26 '16 at 15:55

floriank

25,546
9
42
66

Awesome solution. The only problem I have now is that it looks like Cake is doing an initial insert of the mission_id, which means I end up with 593 rows instead of 592. The first row only contains the mission_id, and all subsequent rows have the mission_id and the data from the infile. How can I prevent the first insertion? – Chris Jan 26 '16 at 17:21
Remove that first line from the file or please *read the documentation* of the SQL function. By more than just a quick look I can see that it supports `[IGNORE number {LINES | ROWS}]`. So just alter the query to ignore that line. – floriank Jan 26 '16 at 17:46
The IGNORE clause refers to ignoring the first line of the infile. I don't want to do that. I want to keep CakePHP from first INSERTING the mission_id on its own and THEN reading the infile and setting mission_id to what the user provided. – Chris Jan 26 '16 at 17:48
I'm pretty sure this happens because the first line contains not all the data but just a CSV "header". – floriank Jan 26 '16 at 18:49
Adding the IGNORE clause just makes it ignore the first line of data in the infile. Even with it there, cake still does an insert of the mission_id BEFORE it starts reading the infile. I just need to suppress that first insert somehow. – Chris Jan 26 '16 at 18:58
Well, you're obviously doing a save() before, so you get that "empty" record from this `if($this->Requirement->save($this->request->data)) {`. – floriank Jan 26 '16 at 19:26

CakePHP: Add user input to record alongside data read from an infile

1 Answers1