Records suddenly unavailable to [pdb_list] become available if submitted again

Now a variant on this issue has raised its head. The behaviour I described in my initial report all occurred on a local MAMP instance. After posting my initial report above, I:

• Extracted my local MAMP record data to a spreadsheet using the PDb function on the List Participants page
• Copied my edited template and associated functions file from MAMP onto my ‘real’ site
• Truncated the PDb records table on my ‘real’ site
• Imported into my ‘real’ site the CSV extracted from my MAMP site

After performing these steps all appears to be fine in my ‘real’ site when I go to the PDb List Participants page (that is, all the right data appears to be present). When I display the page where my [pdb_list] shortcode is present, however, no records are displayed. And once again, the only way to get a record to display in my template is to ‘edit’ it via the List Participants page, make no changes whatsoever, but ‘Submit’ the (non)edit. Once I do this, the quasi-edited record appears on the page where my [pdb_list] shortcode is present, but all other records fail to appear.

To investigate this further, I tried extracting a single participant record from the database both BEFORE and AFTER I ‘edited’ it on the List Participants page (in reality, merely submitted the unedited record). I used phpMyAdmin to do this, and saved the result in each case to a plain (non-Excel) CSV file. I discovered two things through this process:

• At least four fields in the AFTER submission version had their values changed from NULL to blank
• My ‘participants database’ table contains many columns that represent database fields I created during development but subsequently deleted. These fields certainly don’t show up on the PDb Manage Database Fields page – but they’re still right there in the ‘participants database’ table.

It appears, then, that saving a record in Participants Database may cause at least some fields which contain a value of NULL after a CSV import to be changed to empty, and that if these fields contain NULL they are not made available to the [pdb_list] shortcode. I’m afraid I’m getting a little out of my depth here!

I am wondering if I may have screwed with the integrity of the PDb database setup. In the early stages of my playing around (before I knew any better – sorry) I recall I did delete some columns directly from the participants_database table instead of deleting fields properly through the PDb front end. Questions arising:
1) Could this have resulted in there now being old fields represented in this table which are not now visible through the front end?
2) Could the presence of these fields (rather than the NULL/blank issue) be responsible for the behaviour I’m seeing?
3) Is it ever acceptable practice to delete unwanted fields directly from the participants_database table (eg, using phpMyAdmin)?

As my next step in investigating this issue, I opened each record in the PDb ‘Edit Existing Participate Record Page’, made no change to the record, and submitted it. I did this for every record. Once all records had been through this process, they all displayed correctly on the page where my [pdb_list] shortcode is located.

Next, I downloaded a CSV file using the Export CSV function within PDb (ie, at the bottom of the ‘List Participants’ page. I then truncated my participants_database table in phpMyAdmin and imported the CSV file I had just downloaded using the PDb import CSV file function. Once again, none of my participant records would display on the page where my [pdb_list] shortcode is located.

I used phpMyAdmin to select all columns for one record (id=49) and downloaded a CSV file for this record from within phpMyAdmin selecting CSV as my download format, and accepting all custom defaults (including ‘Replace NULL with: NULL’). I opened record 49 in ‘Edit Existing Participate Record Page’, clicked the Submit button, then repeated the process I had used in phpMyAdmin to download to a CSV file the record for id=49. Comparing the versions of this record I collected before and after saving this file in the PDb record editor, I could see, once again, that at least one field that I am currently using (f2f_start_time) had changed from containing NULL to containing a blank. Most NULL fields in the ‘before’ record had not changed to blank in the ‘after’ record, but these appeared to be most old fields which I have deleted via the PDb front end, but which still remain in the ‘participants_database’ table).

At face value, it appears that PDb is writing out at least one field that is not flagged as NULL in the underlying database as if it is NULL when the user downloads a CSV file of participant records. And it further appears that if a record containing this NULL value is subsequently imported in PDb via the Import CSV file function, the presence of this NULL value is enough to make this record unavailable to any template associated with the [pdb_list] shortcode.

While I can’t say I understand the problem you’re seeing, it sounds a bit like there is a disconnect between the database tables you’re looking at in phpMyAdmin and the plugin tables.

Records that are deleted in the PDb backend are indeed deleted from the main database table. If you don’t see that, then my first thought is make sure you’re looking at the correct table.

Also, importing data directly using phpMyAdmin is not recommended, it can cause problems, you should use the plugin’s built-in importer.

Truncating the table then re-importing could cause problems if the export did not include all the columns, depending on which columns were missing…again, this is not a recommended procedure because you’re mixing pure MySql operations with plugin operations, and they are not the same.

The problem for me in supporting this question is I have no way of knowing exactly what happened because you are circumventing the plugin’s handling of the database. I can certainly support using the plugin as intended because then I can verify the issues…with what you’re describing here, I have no way of verifying your results, nor can I use it to check for bugs in the code.

Thank you for your considerate response, which is completely reasonable. I dumped a lot of information there. And I agree that given I did, at one point, directly delete several columns from the database directly, which is not a process you advise, I certainly should bear responsibility for whatever consequences follow from that. To be clear, though: at no point did I ever upload data into PDb via phpMyAdmin. I only ever uploaded data via the PDb CSV import function.
Rather than be forever annoyed by the issue I have been experiencing (or at least by a lingering doubt that I may have caused the issue I was experiencing by meddling directly with the database) I decided this morning to screenshot info about all my fields and groups and then start again with a clean slate.
Accordingly, I uninstalled PDb via the Plugins Delete function, and then performed a fresh install of PDb from scratch.
Following the fresh install, I did the following:

1. Created a new field group called ‘public_common’
2. Added a text-line field to this group called ‘xxx’
3. Deleted the ‘xxx’ field inside PDb by clicking the red cross adjacent to the field inside PDb, on the Manage Database Fields page (ie, using this PDb front end)

After this, I viewed the structure of the ‘participants_database’ table inside phyMyAdmin and found that it contained the original 18 columns that come ‘built in’ with a fresh installation, plus the ‘xxx’ field which I had previously deleted via the PDb front end.

To be clear, the ‘xxx’ field is no longer visible within PDb itself. However, it continues to be present in the underlying database.

This would appear to suggest that database fields created in PDb then deleted via the PDb front end are NOT removed from the ‘participants_database’ table. It should be a fairly simple exercise to demonstrate whether or not this behaviour can be replicated on another fresh installation of PDb.

You are correct about the column in the main database, it is not deleted when the field definition is deleted. (this is to prevent unintentional data loss) There is no performance penalty for leaving it there, it is simply ignored. You can remove it if you want.

I did not understand that that was what you were asking about, I thought you were saying you’d deleted a record, so I’m sorry for the confusion.

In my various postings under this (now somewhat misleading) topic heading, I have been raising two probably quite distinct and unrelated matters.
The first concerns whether or not PDb deletes database fields at an underlying database level when the user ‘deletes’ a PDb database field using the PDb interface in WordPress. I’m clearer about that now. Thank you!
The second concerns the treatment of apparently empty fields when PDb:

• Exports and exports participant records via its respective import CSV and export CSV functions
• Makes those records to any custom template associated with the [pdb_list] shortcode

Today I have performed a completely fresh installation of the PDb plugin. Since doing so, the only direct manipulation I have done of the underlying database has been to truncate the ‘participants_database’ table using phpMyAdmin for purposes described in the PDb documentation.
After reinstalling PDb and creating a set of database fields via the PDb ‘Manage Database Fields’ function, I imported some data from a spreadsheet. The data did not initially display on the custom template I have associated with a [pdb_list] shortcode.
After I opened the ‘Edit Existing Participant Record’ screen for one of these records and clicked the Submit button, that one record DID display on the page using my custom template. No other of my records did.
Using myPhpAdmin, I ran a query that selected all data for that one record, and saved the result to a spreadsheet. I DID NOT EVER LOAD THIS SPREADSHEET INTO PDb. I GATHERED THE INFORMATION ONLY FOR INSPECTION PURPOSES.
Next, I deleted the record which was showing on the screen associated with my custom template. I deleted this record inside PDb, from the ‘List Participants’ page.
Next, I used the PDb ‘Import CSV file’ function to import into PDb the one record I previously exported as a CSV file using PDb. Following the import, I could see that this record was now present, but it did NOT display on the page using my custom template (and neither did any other of my records).
Using myPhpAdmin, I again ran a query that selected all data for that one record, and saved the result to a spreadsheet. NOTE AGAIN THAT I DID NOT EVER LOAD THIS SPREADSHEET INTO PDb. I GATHERED THE INFORMATION ONLY FOR INSPECTION PURPOSES.
By this time, it seemed clear that the ‘secret’ to making any of my records available to the [pdb_list] template was to open them in the ‘Edit Existing Participant Record’ screen and click the Submit button.
Directly inspecting the contents of the ‘participants_database’ table reveals that at the time when a record is not available to the [pdb_list] template, it contains NULL in some of the fields I have defined. When the table entry contains NULL in these fields, it never becomes available to my custom template. The effect of displaying a record on the ‘Edit Existing Participant Record’ screen and clicking Submit, however, is to remove those NULLs and turn them into empty strings. Once this happens, the record becomes available for use in my custom template.
I am satisfied that the effect I am observing inside my custom template is not the result of a coding error on my own part within the template. I can verify this by dumping the contents of $this->records in the first couple of lines of my template. Records which contain NULLs in any of the fields I have configured as listable in PDb do not appear in the resulting dump. As soon as I ‘edit’ a record in PDb by clicking the Submit button, however, that record does appear in the dump.
It seems to me that either there is an underlying bug in the PDb CSV import process which is causing it to write empty fields to the underlying database as NULLs, or else there is a bug in the way in which records are excluded from listability if any field in a record contains a NULL at the database level.
I am sorry this is longwinded. The ‘experiment’ I have conducted to establish my point is necessarily a bit hard to describe any more succinctly.

Damn! Even now I left out a step. Before I deleted the ‘one record’ I have referred to, I exported a CSV file for just that one record using the PDb CSV export function. It’s that file which I subsequently reimported into PDb.

This record was ‘working fine’ before I exported it to CSV because it contained no NULLS (because I had submitted it). When I reimported the very same record, it no longer ‘worked’, because at the database level it now contained NULLs.

To help see what is going on, turn plugin debugging on (plugin settings under the advanced tab), clear the debugging log, then load the page with the list shortcode. Got to the debugging log, you will see the database query that is used to generate the list. This query is partly based on the “filter” attribute of the list shortcode.

What is the exact shortcode you are using for the pdb_list shortcode?

Shortcode is: [pdb_list template=bootstrap-pcmga]

My custom template is called ‘pdb-list-bootstrap-pcmga’. I’m using your add-on for keeping custom templates in their own folder outside the PDb main plugin. The template is writing tons of messages to the php log, so I’m totally sure it’s being invoked.

The query reads:

PDb_List::_setup_iteration list query: SELECT p.id, p.address_1, p.address_2, p.admin_short_note_1, p.admin_short_note_2, p.admin_short_note_3, p.admin_long_note_1, p.age_group, p.approved, p.date_last_verified, p.email, p.f2f_day, p.f2f_end_time, p.f2f_start_time, p.facilitator_1, p.facilitator_2, p.how_is_this_program_delivered, p.organisation_1, p.org_1_url, p.organisation_2, p.org_2_url, p.organisation_3, p.org_3_url, p.person_who_handles_program_enquiries, p.phone_number_for_enquiries, p.private_id, p.note_to_webmaster, p.program_type, p.brief_note_to_accompany_listing, p.publication_status, p.record_is_for_testing_only, p.role_of_person_who_handles_enquiries, p.rop_day, p.rop_end_time, p.rop_start_time, p.state, p.suppress_contact_phone, p.suppress_facilitator_2, p.suppress_org_1, p.suppress_org_1_url, p.town FROM 89E_participants_database p WHERE p.approved = "X" AND p.record_is_for_testing_only <> "X" AND p.publication_status = "LIVE" ORDER BY p.date_updated DESC

As you can see, I’m filtering the query with several calls to: add_action(‘pdb-list_query_object’, ‘my_filter_function’).

I don’t know about values changing from an empty string to a NULL in the database when importing a record, I’m not seeing this in my tests here, so I can’t account for why that is happening. You can use the debugging log to get a detailed picture of the interactions with the db.

I will dig further into this to see if there is an issue with empty string values getting getting changed to NULL values in the db.

What is happening to your list display is the p.record_is_for_testing_only <> "X" clause of your query will not include records with a NULL value in that column. This is the way that mysql works: NULLS are a special case, so the clause would need to be p.record_is_for_testing_only <> "X" OR p.record_is_for_testing_only IS NULL so that records with a NULL value in that column will be included.

A NULL value in the db signifies that the value has never been changed or set. This gets changed to an empty value when the record is updated if the field is left blank in the form. This can be useful in tracking whether the value has been updated or not.

Thanks for this great insight into what’s going on with respect to the non-appearance of my records on the [pdb_list] screen. It is my code responsible after all.

Unfortunately, it looks to me like pdb-list_query_object isn’t currently set up to handle this rather unusual situation (unless maybe I’ve coded this wrongly, which is always possible).

Here’s my whole query filtering routine:

add_action('pdb-list_query_object', 'pcmga_filter_displayed_list');

function pcmga_filter_displayed_list($query) {

if($GLOBALS['PCMGA_LIST_FILTER_INCLUDE_ONLY_APPROVED_PROGRAMS']) {
	$query->add_filter('approved', '=', 'X');
	pcmga_write_to_PDb_debug_log('We are including only approved = X programs in display listing',3);
}

if ($GLOBALS['PCMGA_LIST_FILTER_EXCLUDE_TEST_PROGRAMS']) {
	$query->add_filter('record_is_for_testing_only', '!=', 'X', 'OR');
	pcmga_write_to_PDb_debug_log('We are excluding all test_only = X records from display listing',3);
}

if ($GLOBALS['PCMGA_LIST_FILTER_EXCLUDE_TEST_PROGRAMS']) {
	$query->add_filter('record_is_for_testing_only', 'IS', 'NULL');
	pcmga_write_to_PDb_debug_log('We are including records where test_only is NULL',3);
}

if ($GLOBALS['PCMGA_LIST_FILTER_INCLUDE_ONLY_LIVE_STATUS_PROGRAMS']){
	$query->add_filter('publication_status', '=', 'LIVE');
	pcmga_write_to_PDb_debug_log('We are including only publication_status = LIVE programs in display listing',3);
}

if ($GLOBALS['PCMGA_LIST_FILTER_EXCLUDE_LIVE_PROGRAMS']) {
	$query->add_filter('publication_status', '!=', 'LIVE');
	pcmga_write_to_PDb_debug_log('We are excluding publication_status = LIVE programs in display listing',3);
}

}

Even though I’ve attempted to set up an IS NULL test, the resulting SQL looks like this:

PDb_List::_setup_iteration list query: SELECT p.id, p.address_1, p.address_2, p.admin_short_note_1, p.admin_short_note_2, p.admin_short_note_3, p.admin_long_note_1, p.age_group, p.approved, p.date_last_verified, p.email, p.f2f_day, p.f2f_end_time, p.f2f_start_time, p.facilitator_1, p.facilitator_2, p.how_is_this_program_delivered, p.organisation_1, p.org_1_url, p.organisation_2, p.org_2_url, p.organisation_3, p.org_3_url, p.person_who_handles_program_enquiries, p.phone_number_for_enquiries, p.private_id, p.note_to_webmaster, p.program_type, p.brief_note_to_accompany_listing, p.publication_status, p.record_is_for_testing_only, p.role_of_person_who_handles_enquiries, p.rop_day, p.rop_end_time, p.rop_start_time, p.state, p.suppress_contact_phone, p.suppress_facilitator_2, p.suppress_org_1, p.suppress_org_1_url, p.town FROM 89E_participants_database p WHERE p.approved = "X" AND (p.record_is_for_testing_only <> "X" OR p.record_is_for_testing_only = "NULL") AND p.publication_status = "LIVE" ORDER BY p.first_name DESC

I have to say, like so many others who have used this forum before me, that I am just immensely impressed by the level of support you are offering here. It’s truly outstanding, and I’m very grateful. I’m developing this use of Participants Database myself as a volunteer working for a small charity, and in this capacity it’s just fantastic, to me, to have stumbled upon you and your plugins.

To be clear, the ‘IS NULL’ intention of the filter has become ‘= NULL’ in the resulting SQL – not the same thing at all.

On the issue of some PDb fields for a new record that has been imported into PDb via CSV import, I can see by examining the SQL generated for each individual record import that if a field in my spreadsheet does not contain an explicit value for a given field, that field is not referenced at all in the SQL INSERT… SET instruction. I’m not sure if the following documentation is relevant for the version of MySQL running on my host, but the doco here on the INSERT statement suggests that “If strict SQL mode is not enabled, any column not explicitly given a value is set to its default (explicit or implicit) value” (https://dev.mysql.com/doc/refman/8.0/en/insert.html). When I examine the default value of fields I have defined in PDb such a text-line fields using phpMyAdmin, the default value shows up as NULL.
If my reasoning about this is correct, it follows that a user could do the following steps, and end up with different data inside PDb than what they started with.
1. Export all their records to a .csv file.
2. Delete all their records inside PDb using the PDb delete function.
3. Reimport the records exported in step 1.
The difference between ‘before step 1’ and ‘after step 3’ would be that before, provided a user had ever submitted an edit to a record using PDb, no empty space inside a record would be represented at database level by a NULL, while after step 3, all empty spaces in all records would be represented by a NULL. (This is because when a user submits an edit, some value is written into every database field, including empty fields, so that no field ends up holding NULL.)