Re: mySQL query question
| From: | Jim Lucas | Date: | Fri, 14 Nov 2008 20:46:38 +0000 |
| Subject: | Re: mySQL query question | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-283307@lists.php.net to get a copy of this message | ||
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
)
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