champeaub

Hey Benjamin,

Well I’ll be darned. I had done the plugin troubleshooting, but forgot theme. I switched themes and it seems to be working. Well, at least I am pointed in the right direction.

Thank you for the help.

Alright,

The first thing I will say the Participant Database plugin has a specific purpose, and is most likely not your solution. The Participant Database plugin is pretty awesome, but if you are looking for something that can fit a wider set of solutions look into PODS Framework plugin.

With that said here is how I setup this up.

I am using this plugin to simply display an employee schedule. The software that is used here to schedule employees is beyond limited, but we wanted a way to display the schedule online. Luckily the software was capable of exporting the schedule to CSV every time the schedule was saved.

Make sure the CSV you have is setup the way you want it setup. The CSV file that I got from the scheduling software had a lot of extra data that was not needed. So, I created a PHP script that read in the CSV, stripped out the data not needed, and then wrote the data to a new CSV.

Once your CSV is set go to the “Manage Database Fields” under the Participant Database plugin. You need to delete whatever fields are under Participant Info Fields, and put in the same fields that are in your CSV.

The website I maintain is hosted on-site, and runs on Ubuntu 12.04. On the server I setup a chron job that looks like this:

cp -f /path/to/file /place/to/copy/to && php5 /path/to/script && mysql -uusername -ppassword database name < path/to/import.sql

The first command is copying the csv off the network onto the server.

The second command is running the php script I created to create the stripped version of the csv.

The third command is logging into sql and into the wordpress database and telling it to run the commands in the import.sql file.

There is probably a much more secure way to do this than putting your sql password in a scheduled task . . . I just haven’t taken the time to improve the process.

Here is a copy of my import.sql

truncate table wp_participants_database;
LOAD DATA LOCAL INFILE ‘/path/to/csv’ INTO TABLE wp_participants_database FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (field_1, field_2, field_3, field_4);

Explanation of import.sql:

truncate table – this deletes all data in table

LOAD DATA LOCAL INFILE – This tells sql to import the csv

INTO TABLE – this tells it what table to import the csv to

FIELDS TERMINATED BY ‘,’ and ENCLOSED BY ‘”‘ and LINES TERMINATED BY ‘\n’ – just keep these in here to be safe

IGNORE 1 LINES – if your csv has a header (first row is the name of your fields) then include this.

(field_1, field_2, field_3, field_4) – the name of these fields are the same as the field names you listed in Manage Database Fields and your csv.

I don’t know if I provided enough detail. Let me know if this helps or not. If you have a specific question let me know.

Here are some of the things I read up on to help me understand what I was trying to do.

mySQL
mysql 5.5 LOAD DATA INFILE syntax

PHP – if you plan on making a script
fgetcsv
fputcsv

I can share my script if anyone is interested. It probably doesn’t follow best practice but it gets the job done.

Hey there,

Sorry I missed your message. It is a bit late for me now. I will get back to you guys tomorrow morning!

Goodnight!

Hey xnau,

Thanks for the response. I was afraid this was going to be the answer, ha! I started to look into the WP chron, but I thought I would ask you before I dove in. I will let you know how it goes!

Thanks again!