Inserting into two tables at once 1

[sipps]

Hi all,

I am inserting a new item into an Item table, adn I also want to insert the stock quantity into a stock table on the same form.

Problem is that the stock table uses the Item_ID which is an auto-inc from the item table. How can I get the Item_ID that it inserts for the new item, and use it in the stock table all on the same page?

I'm not sure whether it is possible, I don't mind adding a new item, going to the next page and inserting the stock quantity there, but how would I retrieve the last Item_ID?

Thank you,

sipps

 

[sleipnir214]

Take a look at PHP's mysql_insert_id() (

http://www.php.net/manual/en/function.mysql-insert-id.php)

function.

You'll perform the first insert, get the auto_increment ID generated by the insert via mysql_insert_id(), then use that in your second insert. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sipps]

Thanks sleipnier214

I read through the documentation, and tried to implement it. I have now got two pages, the first inserts the item details into the item table. After it inserts, it goes to the next page where it adds the number of stock for that item. I used the LAST_INSERT_ID() funtion to get the last ID, but I get no results back, this is my statement:

SELECT *
FROM item, category, supplier
WHERE item.Item_ID = LAST_INSERT_ID() AND item.Category_ID = category.Category_ID AND item.Supplier_ID = supplier.Supplier_ID

As I say, this returns no results, is that because the connection to the database was closed between the two pages? I cannot think how to get this Item_ID back between the pages so I can insert the stock quantity.

Thanks

sipps

 

[sleipnir214]

Yes, since the database connection was closed, mysql_insert_id() will not return anything. mysql_insert_id() is specific to a particular database connection.

I recommend that your first script fetch the insert id, then pass that id on to the script which produces the next user input form.

That form-producing script can then place the insert-id on the page, perhaps in a hidden form field, so that the last data insertion script can have the id available. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sipps]

Thank you sleipnir214,

I have put the last_insert_id() on the first page, where I insert the new item, so the statement is right at the top of the page:

mysql_select_db($database_digitalhome, $digitalhome);
$query_getitemid = "SELECT LAST_INSERT_ID()";
$getitemid = mysql_query($query_getitemid, $digitalhome) or die(mysql_error());
$row_getitemid = mysql_fetch_assoc($getitemid);
$totalRows_getitemid = mysql_num_rows($getitemid);

and then is called in a hidden form variable at the bottom of the page:
<input type=&quot;hidden&quot; name=&quot;Last_Item_ID&quot; value=&quot;<?php echo $row_getitemid['LAST_INSERT_ID()']; ?>&quot;>

But the Id will not be there until the item details have been committed to the database, after the submit button is pressed, by which time the next page will be loaded.

The problem is that as soon as the submit button is pressed the details are sent back to the database, and it doesn't run the other query getting the last ID.

I am not sure how to do this. Is it possible to keep a connection open to the database across two pages?

Thank you for your help,

sipps

 

[sleipnir214]

I'm confused. Invoking mysql_insert_id() before you have inserted anything into the database is meaningless.

Here's what I imagine:[ol][li]Script 1 produces an HTML form which will collect data from a user. When the user fills in the form fields and hits &quot;submit&quot;, this form will send its data to script 2[/li][li]Script 2 inserts the user data into the database then produces and HTML form which will collect more data from the user, data which must be related to the data inserted into the database by script 2. The form produced by script 2 submits to script 3[/li][li]script 3 takes the additional data submitted by the user and inserts a record into another table, this data being related to the data inserted by script 2[/li][/ol][Note: all three of these scripts could concievably be in reality one script]

Given what I imagine is happening, it would seem to me that script 2 should insert the data, then invoke mysql_insert_id(), then produce the HTML form to collect the related data (including the hidden field for the insert id), in that order. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sipps]

Sorry sleipnir214,

The php page is just an HTML form, which calls some PHP code upon submit to commit the data in the form to the db. This code then automatically sends the user to the next page to insert the amount of stock of that new item, so I can't run the LAST_INSERT_ID() funtion on that page, because it moves off it after insert.

Because I am not a programmer, I have to rely on Dreamweaver to do most of the PHP for me apart from odd bits and pieces, so the options of what happens when new data is inserted into the db are limited.


Is it possible to keep a connection open over two pages?
Or, is there another way to do this, is there some sort of query that can retrieve the last Item_ID from the item table, I know this is crude, but this is only a protoype site.

Thank you for your help, I hope one day I will be able to answer all my own silly questions! Practice.....

sipps

 

[sleipnir214]

The nice thing about Dreamweaver and products of its ilk is that they allow you to get starting doing basic things quickly. The bad thing about Dreamweaver and its ilk is that if you try to do something out of the ordinary, you are going to have to fight the software.

I'm sorry, but I can't help you solve your problem within the constraints of having to do everything withing Dreamweaver. I wish you luck. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sipps]

Thank you sleipnir214, I will just keep at it!!

It's nice that you have such good patience!

Thank you for you advice,

sipps

 

[tekee]

Hi Sipps;
I have always found it easier to keep like processes together. I realize that all I know of you procedure is what I read here, but would it be possible (I don't use dreamweaver either) for you to do all data collection in one process (i.e. script) and all database processing in another script. That is, script one creates a HTML form for collection of all data (item info and quantity etc..). Script two would then be able to open the database once and do all updates i.e. add item, retrieve last_item_id, add quantity using item_id, etc... then close the database and empty the connection and results variables (housekeeping is always a good practice). I would also advise that you make it a practice to init all relevant variables at the beginning of every script, function, class, etc... Just my two cents, good luck.

 

[GiffordS]

Going back to sipps.......

Even if you are using DreamWeaver you should still be able to pull down the insert id before moving off the page. One word of caution before you do anything else though.... using Last_Insert_ID() is NOT the same as using MySql_Insert_ID(). Two entirely different functions which can return different results. You need to be using Mysql_Insert_ID().

Now, having said that... I think you're just a little confused about where any of this stuff should be. If I understand you correctly you have an HTML form, and when that form is submitted you want to insert the data from that form into one table, and then insert another value into a different table. Well, NONE of the sql should be on the page that contains the form. The submit button does indeed move on to the action page specified in the form tag. In fact, it should be moving along with doing ANY of the sql processing, DreamWeaver or no DreamWeaver. You need to move both insert steps to the target page. The first step inserts the data into the first table. From there you simply call mysql_insert_id(). That returns the auto-incremented id of the record you just put in. From there you do your second insert. It's that simple.

For the record, last_insert_id() returns the auto increment value of the most recent update. This will not always be the one you just ran. It's very possible that two or more people could be running this script simultaneously. The value of mysql_insert_id() will always contain the value for the record just inserted by that exact user. If the site has any kind of volume at all you could run into real problems confusing the two.