MySQL error - duplicate entry

[paul foraker]

Hi,

I'm getting a MySQL error in a handler that is based on the newRecord
handler from the Working with Databases sample stack.

This handler is updating a table named 'groups'. The columns are id, grp_id,
member_id
There are currently 60 rows, with id = 1 to 60

Whereas in the newRecord handler in the sample stack, the incoming parameter
is just the pRecordID, I'm also passing to my handler a comma-delimited list
of groups the pRecordID person is assigned to.

As in the original newRecord script, the first INSERT command is supposed to
create a new record in the table. Then, a LAST_INSERT_ID command is issued
to get the ID of the new record. Then an UPDATE command is issued to post
the values to the new row.

In this example, there are two groups in the pGrpList parameter. As I
iterate through them, the first one appears to get posted OK (but not
really), but the second time through I'm getting the error:

  Duplicate entry '0' for key 1

When I check the database in phpMyAdmin, a new row with id = 0 has been
created -- presumably by the first iteration.

On that first iteration, the LAST_INSERT_ID is consistently returning 3084.
Since there are only 60 rows, that seems a weird number. It is, however, a
valid member_id for someone who appears in the table.

So, maybe there's more than one problem?

The failure on the second iteration makes me think there's a database cursor
problem, but I don't know how to resolve that, if that's what it is.

Here's the script...

global gRTdbID
# following assumes MySQL queries
on updateGroups pRecordID,pGrpList

   # looking in the debugger,
   # pRecordID is valid; it's "1009"
   # pGrpList is valid; it's "8,12"

   startLoading
   set the cursor to busy

   # first, delete the existing rows if any
   revExecuteSQL gRTdbID, "DELETE FROM groups WHERE member_id = :1",
"pRecordID"
   get the result -- just checking in the debugger: it's 0

   # now add the new list (a comma-delimited list of groups to which this
person is assigned)
   if pGrpList is empty then put 10 into pGrpList -- default group

      repeat for each item tGroup in pGrpList
         revExecuteSQL gRTdbID, "INSERT INTO groups() VALUES()"
         put the result into tRes
         if tRes is not a number or tRes < 0 then
            error tRes
         end if

         put revDataFromQuery(comma, return, gRTdbID, "SELECT
LAST_INSERT_ID()") into tLastID

         # at this point, tLastID is 3084. But, there are only 60 rows in
the table... ?

         if item 1 of tLastID is "revdberr" then
            error tLastID
         else
            revExecuteSQL gRTdbID, "UPDATE groups SET grp_ID = :1, member_ID
= :2 WHERE id = :3", \
                   "tGroup", "pRecordID", "tLastID"

         end if

      end repeat

   stopLoading
end updateGroups

Any ideas?

Thanks,

-- Paul