Bulk insert of data into a custom table

If I understand you correctly, then I’d first caution you that any solution you come up with should check to ensure the table doesn’t already exist, and if it does, either truncate it before inserting the records or don’t insert them at all. That will prevent errors and/or duplicate records from being created if the user deactivates and then reactivates the plugin.

That said, I think you have three options (someone correct me if I’m wrong):

1) keep the data in a centralized database and have each client/plugin feed off of this centralized data source (i.e., through an AJAX call).

2) Populate the table on your development computer with all the records and then use a tool like phpMyAdmin’s export utility to generate a SQL dump (INSERT statements) of all the records. Then, save the results as a file in your plugin folder. When the plugin activates, simply read the file, extract the SQL statement(s) and execute them.

3) Store the data in some other format, like a CSV file. This option makes sense if your master list is stored in Excel instead of MySQL. When the plugin loads, loop through each line and build/execute the appropriate INSERT statement. Or, you can try the suggestion in the link below regarding “LOAD DATA INFILE SQL”. However, I have never tried that, so I can’t vouch for it… but it sounds interesting.

Of all the solutions above, solution 1 is the optimal choice because it’s the least hassle to maintain. There’s no need to do anything when the plugin activates, and if the data changes or need updates, you only have to do it in one place — on the master server. If your plugin is going to be used on more than one WordPress install, maintaining separate repositories is going to quickly become a headache.

That said, if this is only ever going to be installed on one WordPress site, then you might be ok. Have a look at the following discussion:

http://stackoverflow.com/questions/9734486/importing-a-csv-into-phpmyadmin

or try googling “phpmyadmin import csv file” or “mysql import csv file”. But I’d avoid doing this on the plugin’s activation event, if at all possible. Better to add the records through phpMyAdmin if you only have one install site.

I think you misunderstood me regarding phpMyAdmin. I was suggesting you use it to create an export file and then your plugin would handle the import, not have the end user import it via phpMyAdmin.

As for option 3, you’d have to parse the CSV file in some way using PHP. For that there are many different options. The easiest way appears to be using LOAD DATA INFILE SQL, as suggested above:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

If that doesn’t work, just read through the CSV file line-by-line and explode() that line/string using ‘,’ comma as your delimiter. The resulting array will contain your column data. So, just do an INSERT …. VALUES $array[0], $array[1],… $array[4] etc. for each row. You can use the PHP file functions to accomplish this.

This might work, as well:
http://php.net/manual/en/function.fgetcsv.php

I don’t do a lot of work with CSV files, so I don’t know the best way to parse one. I’d probably do it line-by-line with fopen() and fgets(), myself, but the following thread might give you the code samples you need to get started:

http://stackoverflow.com/questions/7422292/output-csv-to-array