+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Can I get the last inserted IDs from an INSERT SELECT? (/showthread.php?tid=57798)
Can I get the last inserted IDs from an INSERT SELECT? - El Forum - 04-11-2013
[eluser]Unknown[/eluser]
I'm new to CI and so far it's been great. I apologize if I give too much information, I just want to include everything on the first try.
Brief:
Purpose: Project Management software.
1) There are templates for each project type.
These consist of three tables: templates, templates_tasks, templates_tasks_jobs
2) When a new project is added, the existing template will be copied to the actual project tables:
projects, projects_tasks, projects_tasks_jobs
3) Each template has multiple tasks, and each task has multiple jobs. If it helps to give an example:
Project: Website
Task1: Design
Task1Job1: Research
Task1Job2: Wireframe
etc.
Task2: Build
Task2Job1: Build session 1
Task2Job2: Build session 2
etc.
I add the project from a form. I know the template_id and project_id so to add the tasks:
Code:
$query = $this->db->query("INSERT INTO projects_tasks (`project_id`, `name`, `description`, `notes`)
(SELECT $project_id, `name`, `description`, `notes`
FROM templates_tasks
WHERE `templates_id` = $templates_id)");Here's where my question comes in. Can I get all the IDs I just inserted so when I insert the jobs for those tasks I can associate them, or do I need to rewrite how I'm inserting the tasks and do them one at a time so I can use
Code:
$this->db->insert_id()to grab them?
I've been staring at this for a while so if I'm missing another option altogether I wouldn't be surprised.
Thank you in advance.
Can I get the last inserted IDs from an INSERT SELECT? - El Forum - 04-11-2013
[eluser]Jan_1[/eluser]
maybe show some lines more of your code. so it would be more easy to help you.
and I think it's also a question how to store the data in the database.
Can I get the last inserted IDs from an INSERT SELECT? - El Forum - 04-12-2013
[eluser]TheFuzzy0ne[/eluser]
I think the best solution is to use $this->db->insert_id() after each insert. That would be the safest way to do it.
Can I get the last inserted IDs from an INSERT SELECT? - El Forum - 04-12-2013
[eluser]Unknown[/eluser]
Thank you Fuzzy that's what I thought.
After looking at this for another several hours I can't associate the jobs with their parent tasks unless I do them as you suggest and add the task then all it's associated jobs, then the next task and it's jobs etc.
I was going to create a unique key for each project, then use the key and the template_task row id to associate the jobs which leaves my inserts really clean but the logic. Oh the logic.
Again, thank you for the sets of eyes.