On Fri, 4 Jul 2003 19:46:53 -0500 Thomas Castonzo
<[EMAIL PROTECTED]> wrote:
> I am trying to check to see if a certain date is already booked
> before I enter a new record with that same date into my DB. When I
> test this no matter what the args are, I get this error message:
>
> DBD::mysql::st execute failed: Duplicate entry '3' for key 1 at
> dateDiff2.pl line 201.
>
> Key 1 is a unique reservation Id that gets generated every time the
> script is called.
> I tried changing this number manually but still get the same error.
I don't see 'key 1' explicitly mentioned in the INSERT statement.
Could it be a index identifier instead?
If it is an autoincremented column, it should get incremented every INSERT,
not every script invocation. Try inserting rows from the mysql command line
tool and see if it is really getting incremented.
I hope you have set $dbh->{RaiseError} to 1.
> <snip>
>
> my ($count) = 0;
>
> # Make sure we are not double-booking a reservation
> $sth2 = $dbh->prepare(" SELECT house_num,start_date,end_date
> FROM reservations
> WHERE house_num = ?
> AND start_date = ?
> AND end_date = ?
> ");
>
> $sth2->bind_param(1,$rental);
> $sth2->bind_param(2,$$sql_date_1);
> $sth2->bind_param(3,$$sql_date_2);
Are $sql_date_1 and $sql_date_2 really references to values?
If they aren't you are probably using undef for placeholders 2 and 3
which converts to NULL.
What happens if someone reserves the house with overlapping dates?
> # See if $count gets incremented from 0 to > 0
> $count = $sth2->execute();
>
> #If so, we have am existing reservation with the same start and end date
> if($count > 0) {
Taking the rowcount from execute() may work with mysql, but it is not
portable.
You are also risking a race condition here. If there is any possiblity
of another user inserting or updating the record while you are
preparing your own insert, you would be better off just attempting the
INSERT and trapping the duplicate key errors when they happen. That
assumes you have a unique index on house_num + start_date + end_date.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
