Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Transaction Patterns » Delete2 deletes the wrong records... where's my error?
icon5.gif  Delete2 deletes the wrong records... where's my error? [message #1202] Fri, 28 December 2007 18:05 Go to next message
ikatz is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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.


icon9.gif  Re: Delete2 deletes the wrong records... where's my error? [message #1204 is a reply to message #1203] Mon, 31 December 2007 12:50 Go to previous messageGo to next message
ikatz is currently offline  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 #1205 is a reply to message #1204] Mon, 31 December 2007 13:31 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
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.

There is a MULTI2 transaction in the MENU system which you can test (see http://www.tonymarston.net/php-mysql/menuguide/mnu_nav_butto n(multi2).html), and if this works correctly then there is nothing wrong with the logic within the Radicore code.


Re: Delete2 deletes the wrong records... where's my error? [message #1207 is a reply to message #1205] Wed, 02 January 2008 14:51 Go to previous messageGo to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
i have no doubt that the radicore stuff is working properly; it works just fine for the existing multi2 that i was trying to copy.

what php debugger(s) do you recommend?
Re: Delete2 deletes the wrong records... where's my error? [message #1208 is a reply to message #1207] Wed, 02 January 2008 15:29 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
I personally use the one which comes with Zend Studio, but that is not free. If you google for 'php debugger' you will see what is available.

Re: Delete2 deletes the wrong records... where's my error? [message #1210 is a reply to message #1208] Fri, 04 January 2008 17:33 Go to previous messageGo to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
thanks for your help and your patience on this.

there is another error that i'm seeing in some of my multi2 transactions, when i use the submit button:
Quote:

Could not locate original X record for updating ()


would that have anything in common with my original problem, and if so, what would it point to as a culprit? (if it's not related, i'll worry about it later)
Re: Delete2 deletes the wrong records... where's my error? [message #1212 is a reply to message #1210] Fri, 04 January 2008 18:01 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
The code which is included in the Radicore download which demonstrates the Multi2 pattern does not have this error, so there must be something wrong with your code. You need to step through it with a debugger to see what is going wrong where.

Re: Delete2 deletes the wrong records... where's my error? [message #1213 is a reply to message #1205] Wed, 09 January 2008 15:51 Go to previous messageGo to next message
ikatz is currently offline  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 #1214 is a reply to message #1213] Wed, 09 January 2008 16:49 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
Even though order_id is being shown as non-blank in the OUTER area, when you use a navigation button the values for order_id and product_id are taken from the records in the INNER area as these form the primary key of the INNER entity. Have you checked to ensure that the data retrieved from the INNER entity actually includes a value for the order_id field?

Re: Delete2 deletes the wrong records... where's my error? [message #1215 is a reply to message #1214] Wed, 09 January 2008 20:05 Go to previous messageGo to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
aha! this is exactly what my error was. i had assumed that the page was providing the order_id, and did not bother to include it in my inner select.

thanks! i can hardly thank you enough... i spent so much time scratching my head on this.

...just for curiosity's sake, why did this error in my code only delete one record (and not all records with the given product_id)? also, why hadn't it produced an SQL error?
Re: Delete2 deletes the wrong records... where's my error? [message #1216 is a reply to message #1215] Thu, 10 January 2008 05:07 Go to previous message
AJM is currently offline  AJM
Messages: 2246
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.


Previous Topic: POPUP transaction probelm
Next Topic: can't get _cm_popupReturn to execute
Goto Forum:
  


Current Time: Sat Jun 12 08:02:03 EDT 2021

Total time taken to generate the page: 0.01568 seconds