storing record in tables, that relate to multiple id's

[eluser]Brad K Morse[/eluser]
I have a form the user will enter a comment, that can relate to multiple steps, which the user may select.

Each step is assigned an id.

What I need to do is, is store the comment, in the comments table: (comments.id, comments.message) and then take the array of steps the user selected (steps.id) and insert that into the transactions table, which will consist of transactions.comment_id and transactions.step_id

So the user submitted a comment, and selected 4 steps, the id's of those steps are: 2, 5, 6, 7

the comment will be stored into the comments table, have an id auto-incremented, in this example, the comment id will be 12

So now I need to insert into the transactions table, this is what I want it insert (into transactions table) as:

comment_id: 12 | step_id: 2
comment_id: 12 | step_id: 5
comment_id: 12 | step_id: 6
comment_id: 12 | step_id: 7

I know I will have to store all the step_id's into an array, and then run a query for each value, inserting the comment_id that was generated when the comment was inserted into the comments table.

I am unsure on how to accomplish getting that id automatically after it was generated.

I am using an oracle database (oci8)

To sum it up, I need to grab the newly generated primary key from the comments table (comments.id), then store that into the transactions table (transactions.comment_id) 'x' amount of times, based on how many values are stored within the step_id array, so if the user assigned it to the step_id's 2, 5, 6, 7 then there would be 4 inserts into the transactions table, like you see above.

Any help is appreciated.

[eluser]LuckyFella73[/eluser]
You get the id from your last DB INSERT by:

Code:

$this->db->insert_id()

[eluser]Brad K Morse[/eluser]
I tried using that. I have the database autoloading within the libraries array of autoload.php

This is the function I wrote that inserts into two tables.

Code:

function insertTwo() {
  $data = array(
                  'SP_COMMENTS_MESSAGE'       =>  'I am trying to insert this in' ,
                  'SP_COMMENTS_STATUS'        =>  'Completed' ,
                  'SP_COMMENTS_DEPARTMENT_ID' => 1
                );
  $this->db->insert('SP_COMMENTS', $data);
  
  $foreign_key = $this->db->insert_id();
  
  $data2 = array(
                  'SP_TRANSACTIONS_COMMENT_ID'  =>  $foreign_key ,
                  'SP_TRANSACTIONS_STEP_ID'     =>  3
                );
                
  $this->db->insert('SP_TRANSACTIONS', $data2);
  
}

It prints this error out: This feature is not available for the database you are using.

I am using a oracle db, which does not support the $this->db->insert();

[eluser]LuckyFella73[/eluser]
ah ok, I didn't know that is not working with oracle db.
I hope someone else around this forums has a clean solution
for that.

As a workaround you could do a select query, order by id DESC,
LIMIT 1 get the highest id value which should be the last
inserted id. But that is definitely not a clean solution.
On a site with heavy activity you could get something messed up ...
Sorry, but I have no experience with oracle db ...

Maybe this page has useful informations for you:
http://osdir.com/ml/python.db.cx-oracle/...00006.html

[eluser]Brad K Morse[/eluser]
the name of the sequence for the SP_COMMENTS table, is for the SP_COMMENTS_ID field, the name of the sequence is SP_COMMENTS_SEQUENCE

It increments it by 1.

I am stuck with this

Code:

function getNextId() {
  $this->db->select("SP_COMMENTS_SEQUENCE.NEXTVAL AS NEXTID", FALSE);
  $this->db->from("dual");
  $query = $this->db->get();
  $row = $query->row();
  print $row->NEXTID;
}

I have seen in other examples getting it from "dual", I tried getting it from("SP_COMMENTS") as well, but still unsure

Displays: A Database Error Occurred
Error Number:

SELECT SP_COMMENTS_SEQUENCE.NEXTVAL AS NEXTID FROM dual

Screenshot of error: http://cl.ly/3R363Y1K2z1J3P2Z1I03

Unfortunately there is no error number.