UPDATE on mysql joined view

darren

Member
Hello,

I have a problem, I have a joined view that I am using as a table. I would like to update some data on the view, however, upon changing any data and clicking save, dadabik simply returns to the results page and no change is made.

This is a problem i notice when the sql code for an UPDATE includes a LIMIT (some number)

i.e.

[pre]
UPDATE x SET y WHERE z = a LIMIT 1
[/pre]


now upon scanning the dadabik code I do not see any limit used in the sql statements, so I do not believe this is the problem, but this leaves me with no idea why it is failing.

any help would be appreciated

You are using DaDaBIK version 8.2-Lerici pro, installed on Dec 19, 2017 (installation code: 130575a022f599edc0), the latest version of DaDaBIK is 8.2-Lerici released on Dec 19, 2017

You are runnning the last release of DaDaBIK

PHP Version: 7.1.12-1+ubuntu16.04.1+deb.sury.org+1

mysql version: 5.7.20-0ubuntu0.16.04.1

Web server: Apache/2.4.18 (Ubuntu)

Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36
 

eugenio

Administrator
Staff member
Hello,
is the view actually updatable?

Not all mysql views are updatable:
https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

probably in your case the problem is that yo have in the edit form fields from both tables, so the "SET" in the update statement is executed on fields coming from different tables, which makes the view not updatable:

From https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

"it is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION ALL are not permitted even though they might be theoretically updatable."

Best,
 

darren

Member
The view is update-able I tested by doing manual queries using code like

[pre]
UPDATE table.foo
SET field.bar = bash
WHERE field.biz = baz
[/pre]

Its hard to diagnose where the problem is coming from, like I said I have noticed this problem when there is a LIMIT set in the query, but this is not the case here as the dadabik code does not have this or so it seems.

I have an idea, it is difficult to go through all the code for me to find out exactly which SQL query is used in the update of the table, if I could find out which query is being used I could then test and get errors back from MySQL telling me whats going wrong. Do you know where I could look to find out which query is being used?
 

eugenio

Administrator
Staff member
Please try to leave in the edit form ONLY the field field.bar to replicate the query you did manually. If it works, it means the problem is that you have in the form fields coming from two different tables.

Best,
 

darren

Member
No good unfortunately,

to give an idea of what I am doing

the view I am changing has a query like

[pre]
SELECT bar, biz, bash
FROM foo
where bar = '1'
[/pre]

and I want to change bar from '1' to '3'

Does this change anything for you since the results of the view change upon the UPDATE

I forgot to mention that the view also has an inner join query, but writing that in the example would only complicate things
 

eugenio

Administrator
Staff member
I don't think I've understood what you mean. Did you try to leave just one field in the form? Is the behaviour the same? Can I see a screenshot of the edit form to check if it's what I meant?

Best,
 

darren

Member
Here is the different possible fields

different_fields.PNG


Here is the edit form choice

one_field.PNG


It seems the first image is too large to show up correctly, I will make it smaller then update
 

eugenio

Administrator
Staff member
Ok, with dadabik pro the easiest way to see the update query executed is the following one:

- set the parameter $display_sql to 1 in config.php

- in include/business_logic.php look for the function update_record and at the end, just before this line:

return $files_to_delete_ar;

add this line:

exit();

Try to update the view and see what is displayed.
 

darren

Member
I am unable to edit the post anymore it seems.

Anyway the choices are
[pre]
First
Last
Address
AGE
Vol oreintation date
Vol notes
Current volunteer
[/pre]
 

darren

Member
ok upon using your code to determine what is being used, everything seems to be fine as far as what the query is

[pre]
UPDATE `volunteers_for_reception_applicant` SET `current_volunteer` = '1' where `ID` = '70'
[/pre]

which is exactly what my manual code would be
 

darren

Member
oh and to clarify, the problem remained the same when I only had the one field editable. still brought me back to the results page with no changes made
 

darren

Member
Upon further testing, i discovered if I changed the view to not filter based on the "current_volunteer" field then updating the view does not have a problem. To give full clarification as to what is happening so that there is no misunderstanding here is my SQL statement for the VIEW

[pre]
select `volunteers`.`current_volunteer`,`volunteers`.`Timestamp`,`volunteers`.`ID`,`volunteers`.`fk_mailID` ,`volunteers`.`Vol_oreintation_date`,`volunteers`.`Notes`,truncate(abs(((to_days(`volunteers`.`DOB`) - to_days(curdate())) / 365.25)),0) AS `AGE`,replace(substring_index(`mailing_list`.`First`,' ',1),',','') AS `First`,`mailing_list`.`Last`,`mailing_list`.`Address`
from (`volunteers`
join `mailing_list` on(((`volunteers`.`fk_mailID` = `mailing_list`.`ID`) and (`volunteers`.`current_volunteer` = '2'))))
[/pre]

Now what I have is a couple different views based on the result of "current_volunteer"
1 = active
2 = inactice
3 = applicant

My goal is that the person managing the volunteers would go to the inactive volunteer page, for example, and change from inactive to active and then the field would disappear from the inactive and appear in the active page.

Now if the person changes the field from inactive to active for example, it returns back to the results page and no change is made, the only way currently is to have all the results in a list and have the person filter using a search based on the 3 choices of "current_volunteer"
 

eugenio

Administrator
Staff member
Hello,
the thing is that if the query generated:

UPDATE `volunteers_for_reception_applicant` SET `current_volunteer` = '1' where `ID` = '70'

works when you execute it manually, it is very strange (almost impossibile, I would say) that it doesn't work in DaDaBIK.

Another test you could do, to verify that we haven't any error message that for some reason you don't see in the during the normal update process, is to add:

exit();

before this line in index.php

if (isset($_GET['go_back_after_saving']) && $_GET['go_back_after_saving'] == 1){

You need to remove the exit() you added in update_record() and to set both $display_sql and $debug_mode to 1.

Let me know.

Best,
 

darren

Member
I implemented what you proposed for testing and i still do not see anything that indicates an error, I checked all the different error logs, apache, php, ufw, mysql and there is no error.

I am not sure what is causing this as it doesn't make sense to me either, I imagine that what happens is the SQL update is executed first and then everything else happens afterwards.

This may simply be a bug, where if you setup a update-able view the depends on the result of of one of the fields (call it field X) and you edit one of those fields (field X) it just may not know how to handle this.

I will test different methods to see what may be causing this, and I will post back
 

darren

Member
Just to update back on this topic, I changed the behavior of the view to not filter based on the result of the field 'current_volunteer' and the view worked fine after that, it seems when the view depends on a certain criteria and you change the value the view is depending on, it does not know how to handle it.
 

eugenio

Administrator
Staff member
Hello, I still don't understand how that can be possible.

Question: were you trying to change the primary key (unique field) value of the view? There is a related known limitation (see documentation):

"If, while editing a record, you change the value of the primary key, DaDaBIK is not able to show you the updated record after saving."

I am pretty sure you weren't, but I just wanted to double check.
 

darren

Member
I understand, I know of this limitation and it should not apply to this case. The column 'current_volunteer' is not primary or set as the unique ID, the column I set as my unique id is an auto-number field named 'ID'
 

eugenio

Administrator
Staff member
If you want you can send a mysql dump of your db to suppport@dadabik.org (you can delete most of the data and leave only some test data that shows the problem) and I'll have a look into it.

Best,
 
Top