Rows in a view displaying twice

[dmajwool]

Hi Eugenio,

Something else is puzzling me as I continue the migration of my forums and DaDaBik to a new host. All was working OK at the old host.

I have a few DaDaBik pages based on views and the views weren't migrated with the MySQL database so I have recreated them in the new database.

Each view is displaying as expected in phpMyAdmin

However, in DaDaBik, for all the views I am seeing nearly every row displayed twice but in the 2nd instance of each row one field (the same field in every record) is empty.

Above the DaDaBik display grid I see...

[pre]
468 items found (Total items:237)
[/pre]

...which is accurate because the phpMyAdmin view shows 237 rows and the DaDaBik page shows 468 rows.

Can you suggest where I might start looking to fix this. I'd rather not start the page from scratch because it's quite laborious.

Here is the view's select statement.

[pre]
select `dbs397594`.`phpbb_users`.`user_id` AS `id`,`dbs397594`.`phpbb_profile_fields_data`.`pf_title` AS `title`,`dbs397594`.`phpbb_profile_fields_data`.`pf_lastname` AS `lastname`,`dbs397594`.`phpbb_profile_fields_data`.`pf_firstname` AS `firstname`,`dbs397594`.`phpbb_profile_fields_data`.`pf_emailsalutation` AS `emailsalutation`,`dbs397594`.`phpbb_profile_fields_data`.`pf_postalsalutation` AS `postalenvelope`,`dbs397594`.`phpbb_profile_fields_data`.`pf_address_one` AS `address1`,`dbs397594`.`phpbb_profile_fields_data`.`pf_address_two` AS `address2`,`dbs397594`.`phpbb_profile_fields_data`.`pf_address_three` AS `address3`,`dbs397594`.`phpbb_profile_fields_data`.`pf_city` AS `city`,`dbs397594`.`phpbb_profile_fields_data`.`pf_county` AS `county`,`dbs397594`.`phpbb_profile_fields_data`.`pf_postcode` AS `postcode`,`dbs397594`.`phpbb_profile_fields_data`.`pf_telephone` AS `telephone`,`dbs397594`.`phpbb_profile_fields_data`.`pf_emailactive` AS `emailworks`,`dbs397594`.`phpbb_users`.`fld_currentMember` AS `membership`,`dbs397594`.`phpbb_users`.`user_email` AS `email`,`dbs397594`.`phpbb_users`.`user_type` AS `member_type`,`dbs397594`.`phpbb_users`.`username` AS `username`,`dbs397594`.`phpbb_profile_fields_data`.`pf_phpbb_interests` AS `interests`,`dbs397594`.`phpbb_profile_fields_data`.`pf_commsbypost` AS `commsbypost`,`dbs397594`.`phpbb_profile_fields_data`.`pf_newsletterbypost` AS `newsletterbypost`,`dbs397594`.`phpbb_profile_fields_data`.`pf_yearjoined` AS `yearjoined`,`dbs397594`.`phpbb_profile_fields_data`.`pf_paiduntil` AS `paiduntil`,`dbs397594`.`phpbb_profile_fields_data`.`pf_paymentmethod` AS `paymentmethod`,`dbs397594`.`phpbb_profile_fields_data`.`pf_showemail` AS `showemail`,`dbs397594`.`phpbb_profile_fields_data`.`pf_showphone` AS `showphone`,`dbs397594`.`phpbb_profile_fields_data`.`pf_showaddress` AS `showaddress`,`dbs397594`.`phpbb_profile_fields_data`.`pf_partnerto` AS `partnerto`,`dbs397594`.`phpbb_profile_fields_data`.`pf_gdpr_consent` AS `pf_gdpr_consent`,`dbs397594`.`phpbb_profile_fields_data`.`pf_show_interests` AS `pf_show_interests` from (`dbs397594`.`phpbb_users` join `dbs397594`.`phpbb_profile_fields_data` on((`dbs397594`.`phpbb_users`.`user_id` = `dbs397594`.`phpbb_profile_fields_data`.`user_id`))) order by `dbs397594`.`phpbb_profile_fields_data`.`pf_lastname`
[/pre]

Very many thanks, David.

You are using DaDaBIK version 9.2-Monterosso pro, installed on 12-14-2018

PHP Version: 7.2.30
mysql version: 5.7.30-log
Web server: Apache
Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36 Edge/18.18363

 

[dmajwool]

Some more info:

If I do use DaDaBIK to create another view with the same select query as the original, install it and create a page to go with it, this new replacement displays each record once, as expected.

So now I have 2 similar views in the MySQL database, the old one displays records twice, the new one displays records once.

I had a look around in the dadabik tables in the database and didn't see any obvious duplications, so my question is to ask where can I look to maintain the original view and page in order to see each record only once - and hence retain the layout work I've done on its permissions table.

Many thanks, David

 

[dmajwool]

To recap:

A newly created view (with the same SELECT statement) produces no duplicated rows in its new page in DaDaBIK.

However,

In an attempt to recover my previous page formatting work and apply it to "new_view"; in the database table dadabik_forms I copied the 30 rows where table_name = "old_view" to replace the 30 rows where table_name = "new_view".

At this point, the row duplication started in "new_view", same as in "old_view"

So does this imply the duplication is being controlled by entries in the dadabik_forms table?

Here are my 30 rows for "new_view" in the dadabik_forms table

[pre]
-- phpMyAdmin SQL Dump
-- version 4.9.5
-- https://www.phpmyadmin.net/
--
-- Host: db5000415913.hosting-data.io
-- Generation Time: Jun 05, 2020 at 08:33 AM
-- Server version: 5.7.30-log
-- PHP Version: 7.0.33-0+deb9u7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `<mydatabase>`
--

--
-- Dumping data for table `dadabik_forms`
--

INSERT INTO `dadabik_forms` ( `name_field`, `label_field`, `type_field`, `calculated_function_field`, `custom_validation_function_field`, `custom_formatting_function_field`, `custom_csv_formatting_function_field`, `custom_required_function_field`, `js_event_functions_field`, `content_field`, `present_search_form_field`, `present_results_search_field`, `present_details_form_field`, `present_insert_form_field`, `present_edit_form_field`, `present_csv_field`, `present_filter_form_field`, `present_ext_update_form_field`, `required_field`, `check_duplicated_insert_field`, `other_choices_field`, `show_lookup_link_field`, `chosen_field`, `chosen_ajax_field`, `select_options_field`, `primary_key_field_field`, `primary_key_table_field`, `primary_key_db_field`, `linked_fields_field`, `linked_fields_order_by_field`, `linked_fields_order_type_field`, `where_clause_field`, `select_type_field`, `items_table_names_field`, `items_table_fk_field_names_field`, `cascade_filter_field`, `cascade_parent_field`, `prefix_field`, `default_value_field`, `width_field`, `height_field`, `maxlength_field`, `hint_insert_field`, `tooltip_field`, `order_form_field`, `separator_field`, `details_new_line_after_field`, `search_new_line_after_field`, `insert_new_line_after_field`, `edit_new_line_after_field`, `insert_separator_before_field`, `edit_separator_before_field`, `search_separator_before_field`, `details_separator_before_field`, `min_width_results_grid_column_field`, `table_name`) VALUES
( 'id', 'id', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 1, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'title', 'Title', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 5, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'lastname', 'Lastname', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 7, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'firstname', 'Firstname', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 6, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'emailsalutation', 'Emailsalutation', 'text', '', '', '', '', '', '', 'email', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 15, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'postalenvelope', 'PostalEnvelope', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 16, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'address1', 'Address1', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 8, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'address2', 'Address2', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 9, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'address3', 'Address3', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 10, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'city', 'City', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 11, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'county', 'County', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 12, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'postcode', 'Postcode', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 13, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'telephone', 'Telephone', 'text', '', '', '', '', '', '', 'phone', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 14, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'emailworks', 'EmailWorks?', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 24, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'membership', 'Paid-up', 'select_single', '', '', '', '', '', '', 'alphabetic', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', 'fld_userCode', 'user_DropDownListTextValues', '', 'fld_userStatus', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 3, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'email', 'Email', 'text', '', '', '', '', '', '', 'email', '1', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '100', '', '', 17, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'member_type', 'Forum type', 'select_single', '', '', '', '', '', '', 'alphabetic', '1', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '', 'fld_userCode', 'user_DropDownListTextValues', '', 'fld_userType', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 4, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'username', 'Username', 'text', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/contains/doesnt_contain/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '', '', '255', '', '', 2, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'interests', 'Interests', 'textarea', '', '', '', '', '', '', 'alphanumeric', '1', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'contains/doesnt_contain/is_equal/is_different/starts_with/ends_with/greater_than/less_then/is_null/is_not_null/is_empty/is_not_empty', '', '', '', '', '', '', '150', '', '100', '', '', 18, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'commsbypost', 'Commsbypost', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 22, '~', '0', '1', '1', '0', '', 'In the following fields 1=No, 2=Yes. (other values are indeterminate)', '', 'In the following fields 1=No, 2=Yes. (other values are indeterminate)', '', 'view_all_members_2'),
( 'newsletterbypost', 'NewsletterByPost', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 23, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'yearjoined', 'YearJoined', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 19, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'paiduntil', 'PaidUntil', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 20, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'paymentmethod', 'PaymentMethod', 'select_single', '', '', '', '', '', '', 'alphabetic', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', 'fld_PrimaryKey', 'user_DropDownListTextValues', '', 'fld_paymentMethod', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 21, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'showemail', 'ShowEmail', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 25, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'showphone', 'ShowPhone', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 26, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'showaddress', 'ShowAddress', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 27, '~', '0', '1', '1', '0', '', '', '', '', '', 'view_all_members_2'),
( 'partnerto', 'PartnerTo', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 30, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'pf_gdpr_consent', 'GDPR consent', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 29, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2'),
( 'pf_show_interests', 'ShowInterests', 'text', '', '', '', '', '', '', 'numeric', '1', '1', '1', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', 'is_equal/is_different/greater_than/less_then/is_null/is_not_null', '', '', '', '', '', '', '', '', '100', '', '', 28, '~', '1', '1', '1', '1', '', '', '', '', '', 'view_all_members_2');
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[/pre]

 

[eugenio]

Hello,
if the two views has same definition, same form configuration and same permissions they cannot produce two different results.

If you see "duplications" the reason might be that you set a lookup field linking a field that is not a primary key in the linked table, so you get several rows matching.

Best,

 

[dmajwool]

Hi Eugenio,

Thank you for that pointer.

I have found the culprit, it was as you suggested a duplicate value in a lookup field. Two rows in the linked table had the value zero and when I reset the rogue duplicate to NULL, my problem went away.

So, not a DaDaBIK issue at all - it was a data hygiene issue in my database.

Thanks again,
David.