0

I'm trying to write a controller class from a model class for a closure table.

Trying to get my head into it, but it seems difficult. I want to insert entries into a closure table.

Here's the model:

public $table;
public $closure_table = 'closures';

public function __construct($table_name = NULL, $closure_table = NULL){
    parent::__construct();
    $this->table = $table_name;
    if ($closure_table !== NULL) {
        $this->closure_table = $closure_table;
    }
}

public function add($node_id, $target_id = 0) {
    $sql = 'SELECT ancestor, '.$node_id.', lvl+1
        FROM '.$this->closure_table.'
        WHERE descendant = '.$target_id.'
        UNION ALL SELECT '.$node_id.','.$node_id.',0';
    $query = 'INSERT INTO '.$this->closure_table.'(ancestor, descendant, lvl) ('.$sql.')';
    $result = $this->db->query($query);
    return $result;
}

Error message

"Syntax error at UNION"

N. francis
  • 75
  • 1
  • 12
  • **'but it seems difficult'** What seems difficult? Where exactly is the problem? What part of code is not working right? https://stackoverflow.com/help/how-to-ask – Xpleria Nov 03 '17 at 12:16
  • Hi @NeilPatrao, I just updated with an error message on the model – N. francis Nov 03 '17 at 12:31
  • Look up how to use union all. It appears you are missing a FROM. Print out your $sql and test it (after reading up on union all ). The same is advisable for your $query, print it out to sanity check it. – TimBrownlaw Nov 03 '17 at 13:30
  • @TimBrownlaw, After adding FROM, it still shows syntax error – N. francis Nov 03 '17 at 13:50
  • ok, so what did you end up with. Care to share it... If you just added a FROM it needs a FROM Sometable as well. – TimBrownlaw Nov 03 '17 at 21:05
  • there was still syntax error, I added FROM '.$this->closure_table.' – N. francis Nov 04 '17 at 12:13

0 Answers0