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() { 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() { 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. |
Welcome Guest, Not a member yet? Register Sign In |