Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP How-To
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
PHP How-To Post your "How do I do this with PHP?" questions here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 13th, 2005, 02:31 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default update table with an ID Num from different table

Hi,

i was wondering if anyboby can help me. i have two tables in my database:
1) AccTable - which has an AccID primary key
2) TicketTable - which has a ticketNo and AccID Column which is a foreign key to AccID in the AccTable.

The process is as follows: a user enters their Name in a form which is inserted into AccTable then they click a button on a different form which enter a ticket num in the Ticket table

The problem is i would like the AccID number from AccTable to be inserted into the TicketTable when they click the button.

Can anybody help?

Scoobie


Reply With Quote
  #2 (permalink)  
Old January 13th, 2005, 03:08 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

What DB are you using?

If MySQL all you have to do is call on mysql_insert_id() after inserting the first record. Presuming that the ID is an auto-increment field. The mysql_insert_id() function will get the value of the auto-increment field created, whereas you can pass it on to the next query requiring that id.

Regards,
Rich

--
[http://www.smilingsouls.net]
Mail_IMAP: A PHP/C-Client/PEAR solution for webmail
Author: Beginning CSS: Cascading Style Sheets For Web Design
Reply With Quote
  #3 (permalink)  
Old January 14th, 2005, 12:59 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

The method suggested by richard is sufficient for systems involving low number of transactions. But when the number of transactions is very high (of the order of hundreds of transactions per second), this method will fail.

Another method (in places where high amount of transactions occur) is to use uniqid function to generate a unique random number. Then append a random number (of 6 digits) to it. accid column and corresponding foreign key column should be defined as strings of length 120.

Then use this to insert a record to acc table. Then use the same string to insert a record to ticket table. This will make it thread safe in the midst of thousands of transactions.
Reply With Quote
  #4 (permalink)  
Old January 14th, 2005, 09:33 AM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

I really don't think that is true. How would it fail? Be more specific.

If you are implying that it is possible for mysql_insert_id() to retrieve the id of a different client, that is not possible.

Quote:
quote:Quoted from: http://dev.mysql.com/doc/mysql/en/In...functions.html

"The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions."
Then it is just a matter of how large of an integer said field may contain.

I think the method that you suggest is more cumbersome.

Offer some proof of your claim.

Regards,
Rich

--
[http://www.smilingsouls.net]
Mail_IMAP: A PHP/C-Client/PEAR solution for webmail
Author: Beginning CSS: Cascading Style Sheets For Web Design
Reply With Quote
  #5 (permalink)  
Old January 14th, 2005, 12:03 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I tried the mysql_insert_id() and i can get it to print the AccID on the screen after i execute the first form (Register) but i have no idea how to pass it in the second insert query on the second form (Buy Ticket).

Any suggestions / sample code

Thanks,
Scoobie

Reply With Quote
  #6 (permalink)  
Old January 15th, 2005, 01:22 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Hello Richard,

For all normal atomic operations where new connections are created per page basis (non-persistent connections), last_insert_id is enough.

If you are using pconnect (persistent connection), which is recommended by PHP, this function is not thread safe.

But those pages where batch updates, delayed insertions etc. are run, it is not possible to depend upon last_insert_id because operations on a single page itself are running on different threads. This again fails when the there is any problem for the insert statement. It will not return 0, but gives the id of the last successful insert operation.

Many persons have pointed out the vulnerabilities of this function in the manual.

-----------------------------------------------------------

Hello Scoobie,

sample code from PHP manual is given below.

INSERT INTO table1(auto,text)
   VALUES(NULL,'text'); # generate ID by inserting NULL
INSERT INTO table2 (id,text)
   VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
Reply With Quote
  #7 (permalink)  
Old January 16th, 2005, 03:17 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
Thanks for your help. i tried the following code and it works.
$insert = "insert into tbl (AccID, Num, RecDate)
values (LAST_INSERT_ID(), '$Random', '$TodaysDate')";

The problem I have now is that this only works if the person is joing for the first time.

How do you get it to insert the persons ID if they log in instead of registering using their email address and password (which is stored in another table). i have looked in the php manual but can't find how this would be done.

Do you have any suggestions?

Scoobie

Reply With Quote
  #8 (permalink)  
Old January 17th, 2005, 01:37 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Hello Scoobie,
I have not understood your problem fully. Do you want to add a record into ticket table after a person logs in ? If yes, this won't require last_insert_id function. From the login details (email / username) you can locate the record from acc table and get the primary key value for that record. You can then use this value to add a new record to tickets table.
Reply With Quote
  #9 (permalink)  
Old January 23rd, 2005, 06:38 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

do you have any sample code, i new and don't have a clue how to do this. i been at this for over a week

Scoobie

Reply With Quote
  #10 (permalink)  
Old January 24th, 2005, 01:48 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

If you need to first insert a record to acc table and then with the new id generated insert a new record to tickets table, the above codes will do it perfectly. If you have a different requirement, please explain it . Then we will be able to help you.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
update table from another db table timbal25 SQL Server 2005 3 January 19th, 2008 06:47 AM
Update one table to another table using DTS in SQL Printmaker SQL Language 0 July 24th, 2007 07:17 AM
DTS return table ID forkhead SQL Server DTS 2 November 9th, 2006 12:15 PM
How to Update one table with other table data? ramk_1978 SQL Language 2 May 26th, 2006 12:51 AM
Update parent table with the sum of child table gbrown SQL Language 2 November 9th, 2004 07:53 AM



All times are GMT -4. The time now is 09:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.