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:

https://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.

>> prevent errors and/or duplicate records

Well you need an uninstall function as part of the plugin’s class for that.

Concering the options you outlined:

1> is unworkable because it creates a cost center (think free plugin = unsustainable nightmare)

2> Ya can’t dump phpMyAdmin on noobs who may not have Cpanel access

3> would be much preferred. The data is in Access, Excell, CSV or any other format if need be. How would you create an array of all those records in order to pass them through a for each loop? Each record is five fields of character data. 50 bytes of data each record, max.

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:

https://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:
https://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:

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

My apologies! I misread Option 2 (long day).

Let me play with that

Thanks, I’ll let you know how it works out.

Ed

Many thanks! I didn’t think of editing a filename.sql file.

For whatever reason (probably protection against timeouts) the .sql file batches the records to be INSERTed in groups of just under 1,000. I edited it into one big INSERT SQL command.

The resulting character string was about two megs, so I put it in a separate php file as a function to return the string ( to keep the plugin.php file lean and trim) and used include() to bring in the data during the activation process.

One little call $wpdb->query($sql); pulls it all in.

Creates the table and fills it. GREAT STUFF!

Thanks again,

Ed

Awesome. Glad I was able to help. ??

Your help is appreciated. Thanks, again.