Multiple users inserting into multiple tables! Help!

Lurk

I'm converting an Access database over to PHP/MSSQL and have struck a problem when inserting data into two tables.

Basically, what the database does is create an 'order' record in one table(Order table), which is linked to another table which contains order items(Item table), which is linked by the OrderID field.

My problem is that in Access, using a subform, the individual items are entered into the Item table, and automatically get assigned the Order number(which is an Autonumber), which is great! Really easy. When one user starts filling out the record, the current OrderID is reserved for them, so when the next person goes to add a record, they get the next number after that. This is what I want!

The hard bit is I am trying to do exactly the same thing in PHP/MSSQL, and have to take into consideration that multiple users will be accessing the database, so how do I ensure that the Items are being assigned the correct OrderID? And how do I get the OrderID in the first place(to use in the Item table INSERT statement), seeing as it is an autoincrementing field!

Thanks

jerdo

Use the autoincrementing ID in the table where things are inserted first. Then use an sql statement to retrieve that id number using something like mysql_insert_id, then insert that into the second table with the rest of the information. Make sure to make that unique in the second table as well so you lessen the chance of a flub up screwing up both databases.

Lurk

Thanks.

I found this method, but wasn't sure if it was the only/best way of doing it.

There doesn't seem to be an equivalent function to mysql_insert_id() for MSSQL, so what is the best way of achieving the same thing?

jerdo

You might just have to run a query like "select id from db where column='something that you just inserted'" for mssql, this is not exactly efficient though. I'm not sure though, I have only recently been forced into dealing with mssql (yuck!) at work. Sorry I didn't notice that you posted you were needing mssql, guess I shouldn't try reading these so late on a work night 🙂 If I run across a better way to get that id I'll post back here.

Lars_Berg

To get the newly inserted identity value

$q = " set nocount on; insert into t.... ; select @@identity ";

It would be preferable to use a stored procedure though.

Lurk

Originally posted by Lars Berg
To get the newly inserted identity value
$q = " set nocount on; insert into t.... ; select @@identity ";
It would be preferable to use a stored procedure though. [/B]

Thanks alot. My next question is: what is the best way to take the 'IDENTITY' value and put it in a variable?

It seems overkill doing a loop to get one value... ie.

         while ($line = mssql_fetch_array($result)) {

           $lastrow = $line["IDENTITY"];

     }

Lars_Berg

select @@identity will only return one row so you don't need a loop. The result column does not have a name so you have to use an ordinal when referencing (starts at 0).

Lurk

Thanks again. Only one problem left I think!

It claims "not a valid MS SQL-result resource", but when I run the query in Enterprise Manager it returns the @@IDENTITY row with no problems.

$sql = "SET nocount ON; INSERT INTO $table (Date,OrderbyID,FaxNumber,orderresult,Cred_Id,Attention,IsSent) VALUES ('$reqdate','$orderedbyid','$faxnumber','$orderresult','$crednumber','$attention','1'); SELECT @@IDENTITY";

$result= mssql_query($sql,$db);

$lastrow = mssql_result($result,0,0);

Am I on the right track with this?

EDIT:

This code works perfectly, but not when I try to combine the queries. It just won't recognise the result as a valid resource. Any ideas? Because even though these lines of code are right next to each other, I really don't want to have problems when multiple people are using the form! Thanks.

         mssql_query($sqlinsert,$db);

     $lastrow = mssql_result(mssql_query("SELECT @@IDENTITY FROM tblname",$db),0,0);