0

I need help with some methodology here.

I have 3 text inputs in a form 'add_task':

<form action="page.php" method="POST">
    <input type="text" name="task_string" value="" />
    <input type="text" name="hours" value="" />
    <input type="text" name="minutes" value="" />
    <input type="submit" value="Add Task" />
</form>

The form 'creates' a task with a timeline using the inputs above.

Now, I have two tables, tasks and timelines

I can INSERT INTO the tasks table with the task_string value, the entry then holds a unique id that auto increments.

// tasks
+----+------------------+
| id | string           |
+----+------------------+
| 1  | task string here |
+----+------------------+

I now need to enter the minutes and hours values into the timelines table but I need to reference the above tasks.id

//timelines
+----+-------+---------+-----------------------------+
| id | hours | minutes | task_id                     |
+----+-------+---------+-----------------------------+
| 1  | 3     | 29      | // task_id from table above |
+----+-------+---------+-----------------------------+

How can I accomplish this using SQL Server and PHP in a multi-user environment?

proPhet
  • 1,188
  • 4
  • 16
  • 39

1 Answers1

1

You can use @@IDENTITY for this purpose; An example below. But for a multi user environment you must be using transaction block to make sure both the insertion happens successfully.

create procedure doDML(@task_string varchar(100),@hours int, @minutes int)
as
begin
declare @lastID int;
insert into tasks values(@task_string);
set @lastID = @@IDENTITY;

insert into timelines values(@hours, @minutes, @lastID);
end
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 1
    You should use SCOPE_IDENTITY instead of @@IDENTITY. [Stack Overflow Questions Tags Users Badges Unanswered Ask Question What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current?](http://stackoverflow.com/a/1920640/924077) – Bogdan Sahlean Oct 19 '14 at 05:55
  • @BogdanSahlean, Thanks for pointing that link but in this situation, there is no harm in using @@IDENTITY since you are using it in the same procedure. You can try it and it's guaranteed to work. – Rahul Oct 19 '14 at 06:43
  • @BogdanSahlean, I read [here](http://msdn.microsoft.com/en-us/library/ms190315.aspx) that `scope_identity()` is broken and that one should use `output clause` instead. – proPhet Oct 19 '14 at 10:19
  • 1
    @proPhet: SCOPE_IDENTITY may produce wrong results when [parallel plans](http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/) are used. But this is not the case with `INSERT ... VALUES (...).` The bug affects SCOPE_IDENTITY() and, also, @@IDENTITY (according to Microsoft Support link). – Bogdan Sahlean Oct 19 '14 at 10:59