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 >> >> >> This whole thing is a contact form. >> > > Well, in that case, you might be able to do something along the lines of this. > > I tested this on my server: > Server version: 5.0.51a-log > MySQL client version: 5.0.51a > using phpMyAdmin - 2.11.1.2 > > I have modified an example from this page: > http://dev.mysql.com/doc/refman/5.0/en/user-variables.html > > <?php > # > # Setup database stuff, process input, get everything ready to do the insert. > # > > # 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 > ) The above should be this instead @confirm_number ); > SELECT @confirm_number AS confirm_number; > "; > $confirm_number = NULL; > # Run it and get confirm_number to work with now. > if ( ($result = @mysql_query($SQL)) !== FALSE ) { > list($confirm_number) = mysql_fetch_row($result); > } > > if ( is_null($confirm_number) ) { > echo 'Failed to get number'; > } > > ?> > > Obviously, I can't test this without your schema. So, I hope it works. > > In the end, you should have a result set that gets returned that contains the > 'confirm_number' of the newly created entry. > > This should also, pretty much, eliminate any chance of a race condition. Since everything is > happening within mysql, it should be very hard to end up > with a condition that you start stomping on records. > > Let the list know if it works for you. > -- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare