If you have a database already and want to adapt it for use in the Participants Database plugin, you’ll have to set up the plugin to interface with that data and then import the data itself into the plugin’s main database table.
Here’s how you get that done.
Start with the User Interface
Participants Database is essentially a user interface for a database. When you want to port an existing database into the plugin, your first task will be deciding how to best represent each field of data in your database.
Go through each field in your database and decide how it would best be managed: for instance: is it a single line of arbitrary text (like a name)? Then it should be defined in the plugin as a text-line field.
Several examples of how to translate your database field to a plugin field:
- a large set of single terms or phrases (like a state): dropdown
- a small set of single terms: radio button
- a set of terms drawn from several possibilities (like a set of qualifications): multiselect
- HTML: rich text
- image file: image upload field
- an email address: text-line (linked emails are automatic)
- a series of yes/no fields that are related? Try combining them into a multiselect checkbox.
And so forth, the idea being to find the best way to give the user the ability to add or edit the data that will preserve the integrity of the database. Don’t worry about the names of the fields as you enter them, they can be different from the column names in your existing database. Just make sure they are unambiguous so you know what goes where.
Matching the Existing Values
When you are defining dropdowns, radios, multiselects, etc. where the possible values are defined in the field, be careful to match the values to what is in the existing data. The labels for the values can be anything, but the values must match exactly.
For example, if you have this in your database:
| state |
| OR |
| OK |
| NY |
You would have this as your values for your dropdown:
Oregon::OR,Oklahoma::OK,New York::NY
It’s important you define all the fields you want to use from the original database. Adding and/or changing this configuration later will be a headache, best to avoid that if possible. It’s easy to delete a field later, so be generous in what you decide to import to the plugin.
Groom Your Data for the Import
Now that the plugin fields have been defined, you’ll need to prepare the data you’re going to import into the plugin database. The easiest way to do that is to get your existing database into a spreadsheet application. Typically, this would be done using a CSV file. It is very important this be done using “UTF-8” encoding at every step.
Once the data is in your spreadsheet app, edit the column heads to match the field names you established in the plugin.
Most data types will be imported into the plugin as a string, but there are a few exceptions you may have to deal with.
Multiselects
If it is a multiselect-type field in the plugin, the data in that field in the spreadsheet should be a comma-separated list of terms. Make sure those terms match what you defined in the plugin for that field.
URL’s with Linktext
“Link” fields in the plugin expect special formatting on the imported data. If it is a bare URL, it should be represented as <URL>. (replacing “URL” with the actual URL) If it has a link text, it’s represented as a “markdown” string like this: [Link Text](URL). Email addresses should be imported as a plan text-line. The plugin handles creating links on these.
Images and Files
Images and files are normally stored as filenames without paths. These are expected to be found in the uploads directory defined in the plugin settings. It is also possible to use a full absolute URI (i.e. a URL that includes the domain name) if you need to. Be careful about using relative paths for images and files, it will work, but may cause problems later.
Saving Your CSV
Once you have the headers set up and the data groomed for importing into the plugin, save the spreadsheet file (in case you run into trouble and need to try again) and then export the sheet as a CSV. Be sure to take the extra step of configuring the CSV export because the default settings are often not the right ones to use here. Use these settings for your CSV export:
Delimiter: comma (,)
Enclosure: double-quote (“)
Separator: line-feed
Encoding: UTF-8
Save it on your local computer in a place you can easily find. Be sure to give it a name that differentiates it from other similar files. Remember, this file is a temporary file only used to transport the data from the spreadsheet to the plugin. The spreadsheet file is the one to keep, and the one to go back to if changes are needed.
Importing the CSV
For very large uploads, you may need to break the upload up into smaller chunks.
Using the “Import CSV File” page in the plugin, find and import your CSV file. There is no limit to the number of records you can import here, but your PHP configuration (this is in your hosting setup) will limit the file size. If you need to upload a very large CSV, you may need to change the “file_upload_size” setting on your PHP configuration, and possibly the “max_execution_time” setting as well. If you don’t know how to do this, ask your web host.
After the file is uploaded, the plugin will tell you how many records it got. If it gets hung up on one, it will let you know where that happened. In a case like that, you should look at the records around that number for something amiss. The things that can cause problems are quotes, commas, line breaks…in other words, characters that the CSV uses to organize it’s data. These characters can be imported in the data (that’s what “enclosures” are for) but not all spreadsheet apps can do this correctly, or can be confused in some circumstances.
Most of the time, just following the CSV configuration guidelines will solve the problem. If you can’t configure the CSV in the spreadsheet app, try another app or use a plain-text editor to format your CSV. A CSV file is nothing more than plain text, and so pretty much all problems can be solved in your plain-text editor. The encoding is one thing you can’t change, the exporting app must use UTF-8 encoding or you will have trouble. A good plain text editor will tell you the encoding of the file. It will allow you to change it there, but in most cases the damage will have already been done by the exporting application.
Do-Overs Allowed
With a complex data set, things won’t always come in to the plugin and behave the way you want. After uploading your data, play around with the listing and editing tools in the plugin to make sure it’s interacting with the data the way you want. If it’s not right, you can start over.
For small datasets, just go to the List Participants page in the plugin admin and delete the records. For large datasets, you’ll want to use a database management tool (such as PHPMyAdmin) to delete the records. If you want to reset the index, you can do this using the management tool…but only do that if you have deleted all records. To delete the contents of a table and reset the index, use the TRUNCATE command.
The database management tool is also a great place to check your imported data. You will want to be sure it’s all correct and all your fields are defined the way you want them before you actually start using the database. Going backwards once the plugin is in use and the database has been altered is very much more complicated and should be avoided.
Muhammad Razzaq 
Roland Barker 
Leslie Wood 
Matt K 
social 
John C Williams 
JC Williams 
Hello Ronald,
First of all i would like to say Thank you for your quick response. I really appreciate your work and skills.
Today I am working on placeholder in search box. I have tried everywhere and write placeholder code in pdb_list_detailed.php but never getting any response. Let me know if you know how can I write placeholder in search box.
Thanks
Make sure you understand how to set up the custom template. The template file must have the correct name and it must be placed in the correct location on your server. That is the usual reason it doesn’t work.
Using Participants Database Custom Templates
For you information, placeholders are set as an attribute of the text input…for example:
<input id="participant_search_term" type="text" name="value" class="search-item" value="<?php echo $this->list_query->current_filter( 'search_term' ) ?>" placeholder="your search here..." >I have certificate numbers for every student and every student Certificate number starts from “AUS…”. So when i put only AUS then all the record appears in the list. Give me suggestion how can I filtered it.
Thanks
What you’re seeing is the expected result. What exactly are you trying to find?
We have list of people who have completed their certificates. Every person certificate number start from AUS e.g AUS000, AUS001 etc. When I entered certificate number in the search box and only put AUS so it shows me all the record. I want to use an validation where if a person put AUS so it show an error that “Please enter correct certificate number”.
Thanks
You can use the “Whole Word Match Only” setting under the Multi Search Settings tab.
Dear Sir,
I can’t reach any multi search tab in my plugin. Kindly guide me from where I can get this tab.
Did you purchase the Combo Multisearch plugin?
Yes, to answer your earlier question, every time I open List Participants, no records show and this error is displayed:
“Fatal error: Out of memory (allocated 616034304) (tried to allocate 12288 bytes) in /homepages/17/d361500940/htdocs/mpl/wptest/wp-includes/wp-db.php on line 1989
There has been a critical error on your website. Please check your site admin email inbox for instructions.
Learn more about debugging in WordPress.”
The website’s admin says he has not received any emails about this error.
I believe I set List Participants to display 300 records at a time. The setting is on that page (right?), so I’m not sure how to reduce it now.
300 should not be too much, but then I don’t really know why this is giving you an error. Changing the number of listings was only a guess. Another possibility is you can reduce the number of columns that are showing on the Manage List Columns page. Set the admin list columns to a small number of fields and try to load the page.
I tried changing the number of columns, but that did not clear the error message, either. The error below has been displayed on the List Participants page for over three weeks now, and I’d really appreciate your help in clearing it so that I can use your plugin.
Fatal error: Out of memory (allocated 615919616) (tried to allocate 12288 bytes) in /homepages/17/d361500940/htdocs/mpl/wptest/wp-includes/wp-db.php on line 1989
There has been a critical error on your website. Please check your site admin email inbox for instructions.
This kind of error is very difficult to diagnose because you can’t tell what caused it. It may be the plugin, I don’t know because there is nothing specific about the error to indicate what really caused it. There never is with out of memory errors.
You can try reducing the number of records that the admin page is configured to show, but that setting is also kept as a preference for a particular user, so changing the global setting may not help. The admin user settings are kept in an option with “pdb-admin-user-settings” the name. Delete these options from the options table to reset the preferences.
There is no guarantee this will help, but it could.
Another possibility is reducing the number of columns that the admin list is configured to show.
These are all things that will reduce the amount of data that that page is attempting to show, which may solve the problem.
Hello. I started uploading records today from an existing database. I will ultimately upload over 800,000 records to PDB, but I’m starting with small chunks of about 5,000 records each. After uploading approximately 35,000 records, I got the following error message:
Fatal error: Out of memory (allocated 616034304) (tried to allocate 12288 bytes) in /homepages/17/d361500940/htdocs/mpl/wptest/wp-includes/wp-db.php on line 1989
Fatal error: Out of memory (allocated 616034304) (tried to allocate 20480 bytes) in /homepages/17/d361500940/htdocs/mpl/wptest/wp-includes/wp-db.php on line 184
Is this an issue with my organization’s WP site, or with the database? (Sorry for the dumb questions; I’m not the website administrator and I don’t build databases every day!)
You’re probably running too close to the limit with your 5000 record imports. Either give php more memory (you can do this temporarily if you don’t want to keep a high memory limit for php) or try smaller imports.
You may want to look into a direct import to the table using phpMyAdmin. You will be able to do a lot more at once because it’s a lot faster. Just make sure the imported data is in the correct form because you will be bypassing the plugin’s data conditioning on import.
Thanks, I’ll try adding memory and reducing the import file size.
How do I clear the error message? When I click “List Participants” in the plugin, all I see is the error message. I can’t get to the list of records on the back end (though I can search for and retrieve them on the user interface).
OK, so every time you open the List Participants page in the admin, no records show, and it shows the out of memory error message?
Not sure what would cause that, since you’re not importing at that time. Maybe make sure you’re not trying to show all the records? There is a setting for how man reocrds to show.
I went import 19 entry’s. However, only 7 are getting imported. I deleted them all and tried again. The same thing is happening. Why are only 7 entries getting imported? Also, when I imported the list over again is duplicated entries when it should have overwritten them. What’s going on?
What message was given after you imported the CSV? That will be an important clue to what happened. Most of the time, what is going on is a syntax issue in the CSV, so you will want to look at the row where the import stopped for problems with the syntax. Most often, that will be something like a comma, apostrophe, or unenclosed line break. Field data with meta characters or line breaks must be enclosed, usually with double quotes.
When importing check the import settings for how duplicate records are handled. There must be a unique ID field of some kind in order for the plugin to know which records to update, otherwise it will import it as a new record.
I am attempting to upload 1 .3Mb file containing 30,000 rows in CSV format. Having checked the the uploads directory I can see that the file uploaded correctly but the data import into the database never completes. Several attempts have been tried involving truncating the existing table and starting afresh. I generally reach 7 or 8000 , then reload the data again and it may go to 11,000 and so on but never seem to reach the full import of rows.
At first I believed it to be a server timeout limit but I have tried on another server with a different hosting company and still no joy. Is there is a limit on data import rows ? or a method of getting the data into the database other than using the built in import CSV utility ( no sure if this would be a problem as regards PrivateID generation)
Thanks
The plugin is not set up to handle very large data imports. You need to break the CSV up into smaller chunks, how large depends on the server. The limitation in most cases is the “max_execution_time” php setting, lengthening that should increase the amount you can upload at once.
This is what I discovered: If the value of the column is less than 5 characters in length, if the column is used as the key to look up the record, it is then generated as a random 5 character length. I have gone back through my database and updated those unique values to be 5 characters long so they are not changed when uploaded into the database.
Yes, it is a 5-character code by default. It is possible to change the length of the private id using the filter ‘pdb-private_id_length’
I am importing a csv file that contains a unique alphanumeric value called PrivateId. PrivateId is used as the lookup value for the client to update their record and therefore appears under the Record Info area and I am unable to change any setting which may be causing the issue. I have one record that generates a random value upon upload of the initial upload for testing. I have examined the information in the csv file using Hexplorer and the information appears valid. Regardless of how many times I upload the data or what data surrounds it, it will generate a unique and random value. Suggestions? I can upload the csv for you to work with.
There is an internal field named ‘private_id’ that is used as a unique identifier, so your field must use a different name. If you named it ‘privateid’ that would work, although it might be a little confusing.
I have uploaded my spreadsheet after dividing my database fields into 2 groups (to simulate multi-db) but when I upload the file that has only the fields from one group in it, random data gets populated into the fields from the other group for that record – how can I ensure these fields remain blank?
It’s hard to say what is happening without seeing the file you are uploading. If a column is not present in the CSV, the import leaves the value for that column alone. What you may want to try is to include all the fields, but with blank values for the columns you want empty. However, if the upload is UPDATING existing records, blank fields will be ignored.
Dear Roland, thank you for your time and for responding so quickly, I believe I have figured it out. I researched your wonderful documentation and finally found a page about the “persistence” checkbox – when I unchecked these the data uploaded beautifully. Thanks again!
Hello Roland. Thanks for the answers to another question elsewhere.
I’m just wondering, and haven’t seen it posted anywhere yet, but when I will upload a csv file from another database, will the database plugin send an email to the participant giving them a code to get back into their information for addition or correction. As if they were signing up the first time?
Thanks,
No, it won’t do this unless you configure an email using the Email Expansion Kit add-on. The way this works is once the records are imported, you can select them in the admin Participant List page and send out the “signup” email. It’s not a good idea to do it automatically because it can happen by accident, or you just won’t be certain the email went out correctly.