Re: mySQL query question

Jim Lucas wrote:

mikesd1@verizon.net wrote:

Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were,

Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1

Seems like a perfect candidate for an auto-inc field, though mysql doesn't let you have multiple in the same table (afaik).

# Now prepare your statement $SQL = " SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM contacts); INSERT INTO contacts (

                      first_name,
                      last_name,
                      email,
                      phn_number,
                      address,
                      city,
                      state,
                      zip,
                      dates,
                      comments,
                      confirm_number
                      ) VALUES (
                      '{$FirstName}',
                      '{$LastName}',
                      '{$Email}',
                      '{$Phone}',
                      '{$Address}',
                      '{$City}',
                      '{$selected_state}',
                      '{$Zip}',
                      '{$newdate}',
                      '{$Comments}',
                      @confirm_number
                      )

SELECT @confirm_number AS confirm_number; ";

You do have a race condition, you can end up with 2 of the same confirm_numbers (you'd have to be unlucky, but it can happen). 2 hits at the same time = 2 selects getting the same max(confirm_number), which results in 2 inserts with the same number. -- Postgresql & php tutorials http://www.designmagick.com/