 Delete2 deletes the wrong records... where's my error? [message #1202] |
Fri, 28 December 2007 18:05  |
ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
|
Member |
|
|
(first time poster, radicore newb on an existing system)
i am working on a link table transaction between tables A and B. the link table (call it X) has a_id, b_id, and a date field. X's primary key is (a_id, b_id).
there was already a working multi2 transaction set up that used X as the inner table and A as the outer, and i used that as a guide to create my own multi2 with X as the inner and B as the outer.
so i created my own multi2, add3, and popup screens, but used the existing delete2 (because it contains just the table_id and the 'require std.delete2').
the multi2 and add3 work fine... but the delete2 displays strange behavior. there are about 4700 existing records in X, and i have used my add3 to enter 2 more. 4702 records, let's say.
i when i select the 2 records i added and press the button for my delete2 script, i get a message in green that 2 records were deleted. the record count has decreased by 2... but my 2 records still appear in the multi2. i can do this many times, and it continues to successfully delete records -- the wrong records.
if i delete all the records in X and add my 2 records, it will be able to delete them.
i have logged the SQL for this, and it seems to be getting the primary key wrong on the delete. this is what i would have typed manually on the mysql command line:
delete from x where a_id=37 and b_id=105
delete from x where a_id=37 and b_id=205
this is what i see instead:
delete from x where a_id=20 and b_id=303
delete from x where a_id=21 and b_id=303
in other words, radicore is creating delete statements where b_id is staying constant and a_id is changing, while the reverse should be true... and the ids for both are completely wrong. as there is already existing data in this table, i'm having trouble figuring out whether the constant/changing IDs are caused by my code or just coincidence within the pre-existing records.
where would i start troubleshooting something like this?
|
|
|
|
| Re: Delete2 deletes the wrong records... where's my error? [message #1203 is a reply to message #1202] |
Sat, 29 December 2007 04:48  |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
If you have created a new version of this MULTI2 transaction with table B as the outer entity and X as the inner entity (where X performs an internal JOIN to table A) then by definition you are selecting a single occurrence of table B and showing those occurrences on table X which link to table A. This is why b_id remains constant and a_id keeps changing.
If you want a_id to remain constant then table A must be defined as the outer entity.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
 Re: Delete2 deletes the wrong records... where's my error? [message #1204 is a reply to message #1203] |
Mon, 31 December 2007 12:50 |
ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
|
Member |
|
|
ok. i'm pretty sure that i have the inner/outer table stuff done correctly, because my add3, popup, and multi2 work as expected. please disregard my previous statements about the a_id changing instead of b_id. after some more exploration, it turns out that both ids were filled with junk values; getting 2 of the same b_id was sheer coincidence.
so, the best description i can give of the problem (as i now understand it) is that my delete2 is deleting the correct number of records, but not necessarily the records that i have selected. its sort of like delete2 is picking the records to be deleted out of a different (larger) recordset than the one being displayed by multi2.
what would make that happen? (also, if there are other questions i should be asking myself, or other information i should be posting to be more helpful then please let me know)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Delete2 deletes the wrong records... where's my error? [message #1213 is a reply to message #1205] |
Wed, 09 January 2008 15:51 |
ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
|
Member |
|
|
| AJM wrote on Mon, 31 December 2007 13:31 |
You will need to step through with your debugger to see what records are being selected when the MULTI2 screen is populated, then check that when you select a record for deletion the primary key of that record is being extracted correctly before being passed to the DELETE2 transaction.
|
ok, i am set up with the zend debugger. i may not be looking in the right place in the code, but it does indeed seem like the primary key is not being extracted correctly.
to put things in terms of your documentation, figure 7, i have MULTI2 page where the order is listed in the "parent details" and the "child data" shows the products associated with it. when stepping through the code, i saw the primary key data in one of the variables, looking something like this:
order_id='' and product_id='7'
this seems strange to me; the product_id=7 is definitely the box i selected, but why is the order_id blank when it is already displayed in the "parent details"!
i'm not sure where the key extraction process starts -- on which file/function should i set the first breakpoint, to follow the key extraction sequence for a delete operation?
|
|
|
|
|
|
|
|
| Re: Delete2 deletes the wrong records... where's my error? [message #1216 is a reply to message #1215] |
Thu, 10 January 2008 05:07 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
Even though the order_id is passed down in the WHERE clause it has to be included in the list of selected column names so that it appears in $fieldarray. It is the contents of this array which is used to construct the list of primary keys for the selected records.
An SQL error will only be generated if there is a syntax error in the SQL statement, such as an unknown column or table name, or an unknown or missing keyword. Issuing a DELETE for a record which does not exist will not produce an error - it will simply report that zero rows were affected.
Whenever an SQL statement contains a WHERE clause it will only work on those records which actually match that clause, which may not be the same as those records which you think ought to match.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
