Re: mySQL query question

[Jim Lucas <lists@xxxxxxxxx>]

mikesd1@xxxxxxxxxxx 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
                      )
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


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php